Build-On Tutorial:

Invoicing

Invoicing: Lesson 1

Create Invoice

Lesson Overview

In this lesson you will make table, field, layout, and scripting changes so your team can create an invoice from the Job Detail page and automatically add all billable time entries to it. You will also add Rate fields to both time entries and staff members, so you can account for different rates during billing.

Here is a sneak peek of what you will build:

Step 1 - Create Invoice table and fields

Create a new table and fields for creating invoices.

  1. Open the Manage Database dialog using the File > Manage > Database menu item.

    Note: You can also open the Manage Database dialog while in Layout mode by selecting the Manage drop-down icon in the top right of the tool bar, and then selecting Database, or you can use the keyboard shortcut Command-Shift-D (macOS) / Control-Shift-D (Windows).
  2. Navigate to the Tables tab, and select the Template table. Click Copy and then click Paste.
  1. Rename your new table from “Template 2” to “Invoice" and click Change to save the new name.
  1. Double-click the Invoice table to see the list of fields it contains.
  2. To help users by automatically entering the invoice date, create a new field named Date, of Type: Date
    1. Click Options to view more settings for the field.
    2. In the Auto-enter tab, activate Calculated value.
    3. Set the auto-enter calculation to Get ( CurrentDate )
  1. In order to track the status of an invoice, create a new field named Status, of Type: Text.
    1. Click Options to view more settings for the field.
    2. In the Auto-enter tab, activate Calculated value.
    3. Set the auto-enter calculation to: "Not Sent"
  2. To give each invoice a clearer name, you will prefix the invoice’s unique ID with the text “Invoice #”:
    1. Create a calculation field named “Invoice_Name”.
    2. Set the calculation to “Invoice #” & ID.
    3. Set the Calculation result to Text.
  3. Create a new field named ID_Job, of Type: Number to store the related Job’s ID.
  4. Click on the Options for the ID field that was aleady present in the table. For Auto-Enter set the next value of the Serial number section to 1000.
  1. Since time entries will be added to an invoice, you need to create a foreign key in the TimeEntry table that knows the invoice ID related to each time entry. Navigate to the fields in the TimeEntry table and create a number field named ID_Invoice.

Step 2 - Create relationships

Create relationships between Invoice and other tables to view related data, like related time entries and the job name.

  1. While you’re still in the Manage Database window, go to the Relationships tab.
  2. Find the table occurrence named Invoice and rename it to “INVOICE” by double clicking on it.
  3. Move this table occurrence to the left side of the Relationships window.
  4. To create a relationship between the Invoice and TimeEntry table occurrences, you will first create a new table occurrence based on the TimeEntry table.

    Click on the Add a table button at the bottom of the Relationships tab.
  1. Name the new table occurrence “invoice_TIMEENTRY”.
  1. Create a relationship between INVOICE and invoice_TIMEENTRY by clicking on the ID field in INVOICE and dragging over to the ID_Invoice field in invoice_TIMEENTRY.
  2. To view job details from the invoice, create a table occurrence based on the Job table and name it “invoice_JOB”.
  3. Create a relationship between the INVOICE and invoice_JOB table occurrences. Match ID_Job from INVOICE to the ID from invoice_JOB.
  4. To also see client data based on the related job, create a table occurrence based on the Client table and name it “invoice_job_CLIENT”.
  5. Create a relationship between the invoice_JOB and invoice_job_CLIENT table occurrences. Match ID_Client from invoice_JOB to the ID from invoice_job_CLIENT.
  1. To see a list of invoices from the Job Detail layout, create a relationship between JOB and a new INVOICE table occurrence. Start by creating the new table occurrence for INVOICE and name it “job_INVOICE”.
  2. Create a relationship between the JOB and job_INVOICE table occurrences. Match the ID from JOB to the ID_Job from job_INVOICE.
  3. To see a list of invoices from the Client Detail layout, you will create a relationship between the CLIENT and INVOICE tables. Create a table occurrence based on the INVOICE table and name it “client_job_INVOICE”.
  4. Create a relationship between the client_JOB and client_job_INVOICE table occurrences. Match ID from client_JOB to ID_Job from client_job_INVOICE. Your relationships graph will look similar to this:
  1. When you created the Invoice table, a developer layout with the name Invoice was created as well. To clean this up, close out of the Manage Database dialog, then rename this new layout to dev_INVOICE by going to File > Manage > Layouts…, finding the INVOICE layout, then double-clicking on it, and renaming it. Move it to the Developer folder.

Step 3 - Modify Job Detail layout

You will create an invoice tab on the Job Detail layout so your team can see a list of invoices for the current job. Also, you will create an Add Invoice button that will run a script to create an invoice. You will create the script later in this lesson.

  1. Enter Layout mode using the Edit Layout button in the status toolbar. Note: You can also enter Layout mode using the View > Layout Mode menu item, or the keyboard shortcut Command-L (macOS) / Control-L (Windows).
  2. Use the Layout pulldown menu in the status toolbar to switch to the Desktop > Job > Job Detail layout.
  3. Double-click on the Tab Control, which will open the Tab Control Setup window.
  4. To create a new tab for invoices, type “Invoices” in the Tab Name field and click the Create button. Then close the Tab Control Setup window.
  1. Activate the new Invoices tab. Copy the blue line located on the right side of the screen, outside of the layout boundary. Paste the blue line and place it in under the Invoices tab name, so it matches how the other tab names are underlined.
  2. The objects and formatting for this tab will be similar to those on the Time Entries tab. To start, copy all objects from the Time Entries tab and paste them on the new Invoices tab.
  3. To modify the portal from referencing time entries to invoices, double-click the duplicated job_TIMEENTRY portal.
    1. In the Show records from: dropdown, select the job_INVOICE table occurrence.
    2. For Sort portal records click Specify.
    3. Select Clear All to remove the existing fields from the Sort Order column. Add the Date field to the Sort Order column and choose Ascending order.
  1. Back on the layout, select and delete the fields that were copied over with the portal.
  2. Open the Fields tab, then at the bottom of the Fields tab set Field Placement to horizontal and Labels to none. (If the Fields tab is not open, click the Objects pane icon in the status toolbar, then click Fields).

    Select the job_INVOICE table occurrence from the drop-down and drag the following fields into the portal:
    1. job_INVOICE::ID
    2. job_INVOICE::Date
    3. job_INVOICE::Status
  3. Select the fields added above and in the Styles tab of the Inspector, set the style for the fields to txt Minimal.Black.Left. You do not want users to edit these fields from the portal, so in the Data tab of the Inspector (with the fields still selected), under Field Entry, uncheck Browse Mode. Your layout will look similar to this:
  1. Next you will update the background button occupying the first portal row. This button enables the user to click anywhere on the row to see details about the record in another layout. Double-click the button to open the Button Setup dialog and make the following changes:
    1. Change the Optional Script Parameter: to “Job to Invoice”. The button calls an existing script that will navigate to the layout named by the script parameter. You will update the script in a future lesson.
  1. Back on the layout, select the job_INVOICE::Date field. Activate the Data tab of the Inspector. In the Data formatting section, make sure the Format: is set to 12/25/14 with Leading character set to Zero for day numbers and month numbers.
  1. This tab should display the total number of invoices above the portal, similar to the total number of time entries on the Time Entries tab.

    To achieve this, open the Manage Database dialog and create a new field in the Job table named z_Invoice_Count. Set the Type: to Calculation, turn off Do not evaluate if all referenced fields are empty, set Calculation result is: as Number and set the calculation as follows:

Let(
[
c = Count ( job_INVOICE::ID )
] ;
If ( c > 0 ; c ; 0 )
)

  1. Back on the layout, change the text box “Total Hours: <<z_Time_Entry_Hours>>” to “Total Invoices: <<z_Invoice_Count>>”. On the Data of the Inspector under Data Formatting, change the format to General.
  2. Change the name of the button Add Time Entry to Add Invoice. You will create a script to create an invoice later in this lesson.

Step 4 - Add Rate and Total fields

It would be useful to see the rate per hour and total amount for each invoice. To achieve that, you will create a Rate field in both the Staff and TimeEntry tables. A staff member might have a specific rate you charge for their work; that rate will be set on the staff member’s record, and will also populate on any of their time entries.

You will also update the related layouts.

Note: Any existing Staff and TimeEntry records will not have any rate data, so if you have already created records in these tables you will need to populate that data manually.

  1. In the Staff table, create a number field called Rate.
    1. On the Validation tab in the Options window, select Strict data type: of Numeric Only.
    2. For Validate data in this field: select Always.
    3. Deselect Allow user to override during data entry.
  1. In the TimeEntry table, create another number field named Rate.
    1. On the Validation tab in the Options window, select Strict data type: of Numeric Only.
    2. Additionally, set the auto-enter calculation to timeentry_STAFF::Rate. Once you have selected a staff member for a time entry, the rate from the related staff record will be entered into the TimeEntry table’s Rate field.
  2. Also in the TimeEntry table, add a calculation field named Total. Set the calculation to Hours * Rate with a result of Number. This is a total for the time entry.
  3. An invoice will collect multiple time entries, and should be able to display the sum of all the time entries’ Total fields. Create a field for this summary. In the Invoice table, create a new field of Type: Calculation, and name it z_Invoice_Total.
    1. Set the calculation to: Sum ( invoice_TIMEENTRY::Total )
    2. Ensure the Calculation result is Number.
  4. Back in Layout mode, navigate to the Staff Detail layout and add the Staff::Rate field, setting the style to txt Black.Left via the Styles tab of the Inspector. Also add a label named “Hourly Rate” and style it as label Left.
  1. Select the Rate field. Follow these steps to display the field in a currency format:
    1. Open the Data tab of the Inspector, and in the Data Formatting section, set the Format to Currency
    2. Select Fixed number of decimals: and set it to “2"
    3. For Currency select -$1234
    4. Set Symbol: to $.
  1. Go to the Time Entry | Card layout and add the TIMEENTRY::Rate field along with a label, formatted just like the rate field on the Staff Detail layout. This way you can see the staff member’s rate right from the time entry.
  1. Open the Job Detail layout and add the job_INVOICE::z_Invoice_Total field to the portal on the Invoices tab.
    1. Place it to the right of the rest of the portal fields.
    2. Using the Inspector, style it as txt Minimal.Black.Right.
    3. Update the data formatting for currency as you did for the Rate field.
    4. Deselect Browse Mode under Field Entry.

Step 5 - Create Invoice Script

In this step, you will create a script that will create an invoice. It will automatically add all billable time entries of the job record you are currently on to the invoice. These time entries should not belong to any other invoice, so you don’t charge for them twice.

In this step, the script will create the invoice and you will see a new invoice appear in the portal. In a future lesson, you will create an Invoice Detail layout, allowing a user to click on an invoice and see its detailed information.

Note: If you have not worked with scripts before, you will find it helpful to review the Creating and Editing Scripts section of the FileMaker Pro Advanced Help system before starting this task.

  1. Open the Script Workspace (Scripts > Script Workspace…). Duplicate the Template script.
  2. Name the new script Create Invoice. Any time you copy the template script, be sure to amend the comments at the top to fit your current script, and move the script to the "Invoice" folder.
  3. Add the following steps to your new script: (For additional details on the settings of the Go to Related Records, New WindowReplace Field Contents and Show Custom Dialog steps, see Step 4 - 7, respectively)

Set Variable [ $id_job ; Value: JOB::ID]

Go to Related Record [ Show only related records ; From table: “job_TIMEENTRY”; Using layout: “dev_TIMEENTRY” (TIMEENTRY) ; New Window]

If [ Get ( LastError ) = 0 ]
   #find all billable time entries that are not included into any invoice
   Set Error Capture [ On ]
   Enter Find Mode [ Pause: Off ]
   Set Field [ TIMEENTRY::Type_Billable ; “==Billable” ]
   Set Field [ TIMEENTRY::ID_Invoice ; “=” ]
   Constrain Found Set []

   If [ Get ( FoundCount ) > 0 ]
      #create new invoice
      New Window [ Style: Document; Using layout: “dev_INVOICE” (INVOICE)]
      New Record/Request
      Set Variable [ $id_invoice; Value: INVOICE::ID]
      Set Field [ INVOICE::ID_Job ; $id_job]
      Close Window [Current Window]

      #link found time entries to the invoice
      Replace Field Contents [ With dialog: Off; TIMEENTRY::ID_Invoice; $id_invoice]
      Commit Record/Request [With dialog: Off]
      Close Window [Current Window]

   Else
      Close Window [Current Window]
      Show Custom Dialog [“Message”; “Unable to create invoice. There are no eligible time entries.”]
   End If

Else
   Show Custom Dialog [“Message”; “Unable to create invoice. There are no eligible time entries.”]

End If

  1. For the Go To Related Record step, open the options dialog:
    1. Set Get related recored from: to job_TIMEENTRY
    2. For Show record using layout, select the dev_TIMEENTRY layout
    3. Select Show only related records and Match current record only
    4. For Result Options: select Show in new window.
    5. In the “New Window” Options dialog, set the options as follows:
  1. For the New Window script step set the Window Style to Document and select dev_INVOICE for Layout. You want each new window to appear offscreen, so that the user isn't impacted by these background processes. Set the location of the windows as follows:
    1. Width to 100 pt
    2. Height to 100 pt
    3. From Top to -5000 pt
    4. From Left to -5000 pt
  1. For the Replace Field Contents step, select the gear icon to the right and select Specify target field, then select the TIMEENTRY table and the ID_Invoice field.
    1. Click the icon again and next to Replace with click Specify. In the dialog, select Replace with calculated result and enter $id_invoice.
  2. For both Show Custom Dialog script steps, leave “OK" as the text on the first button. Clear out the text on the second button, so that the user can only click “OK" when the dialog appears.
  1. Go back to the Job Detail layout and modify the Add Invoice button. Have it perform the Create Invoice script, with no script parameter.

Step 6 - Review your work

You’ve completed this lesson! Back in Browse mode, test your new functionality.

  1. You should be able to create an invoice from the Job Detail layout. You may need to create new, billable time entries in order to test your script. In a future lesson, you will create an Invoice Detail layout so you can see the time entries on an invoice.
  2. The invoice should include all billable time entries (be sure that the time entry has a rate specified) that have not appeared on a previous invoice. 
  3. You should be able to see a list of invoices per job on the Job Detail layout.
  4. You should be able to add a rate for staff on a Staff Detail layout.
  5. When you create a new time entry record and link it to a staff member, that staff member’s rate should appear on the time entry record.
  6. You should be able edit the rate on the time entry, in case you need to override it, without changing the staff member’s rate.