Converting A Job With Line Items To An Invoice In FileMaker

From Dwayne Wright PMP, PMI-ACP, CSM
Certified FileMaker Developer

TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

A client hired me for 1 hour of support time to create the script to take a job record from his file and convert it to an invoice ... and ... include the line items from the job. Not too bad ... here is the script I used ... the # is the comment that was in the script.

#Grab The ID Of The Job
Set Variable [ $JobID; Value:Jobsheets::Jobsheet Number ]
Freeze Window

#New linked invoice
Go to Layout [ “Invoice” (Invoice) ]
New Record/Request

#Bring Over The Job Data Via A Relationship
Set Field [ Invoice::fk_JobNumber; $JobID ]
Commit Records/Requests
Set Field [ Invoice::Client; INVOICE_Jobsheets::Client ]
Set Variable [ $InvoiceID; Value:Invoice::Invoice Number ]

#Create Invoices Line Items
Go to Layout [ “Jobsheets” (Jobsheets) ]

#Go to the line items for the job
Go to Related Record [ From table: “Jobsheets Items”; Using layout: “Jobsheets Items” (Jobsheets Items) ]
[ Show only related records ]
Go to Record/Request/Page
[ First ]
Go to Object [ Object Name: "INVOICELINE" ]

#Set Each Job Line Invoice Field Equal To The Invoice ID you set to a variable earlier and then create new invoice line items using a relationship that creates related records on the fly.
Set Field [ Jobsheets Items::fk_InvoiceNumber; $InvoiceID ]
Commit Records/Requests
Go to Portal Row
[ Select; Last ]
Set Field [ JOBLINE_Invoice Items::Qty; Jobsheets Items::Qty ]
Set Field [ JOBLINE_Invoice Items::Unit; Jobsheets Items::Unit ]
Set Field [ JOBLINE_Invoice Items::Description; Jobsheets Items::Description ]
Go to Record/Request/Page
[ Next; Exit after last ]
End Loop

# Go To The New Invoice Record
Go to Layout [ “Invoice” (Invoice) ]

More info about the author and FileMaker in general, contact me at

© 2008 - Dwayne Wright -

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.