Build-On Tutorial:


Invoicing: Lesson 2

View Invoice

Lesson Overview

In this lesson you will create an Invoice Detail layout and modify the Client Detail so users can see a list of invoices for a specific client.

Here is a sneak peek of what you will build:

Step 1 - Create new layout

You will create an Invoice Detail layout by duplicating Job Detail layout.

  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. Duplicate the layout using the Layouts > Duplicate Layout menu item.

    Note: You can also use the Manage Layouts dialog (File > Manage > Layouts...) to duplicate a layout. This dialog is also useful for organizing and reordering your layouts.
  4. Using the Layout Setup dialog (Layouts > Layout Setup…) rename your layout to Invoice Detail.
  5. While still in the Layout Setup window, choose to Show records from: the INVOICE table.

Step 2 - Update Invoice Detail layout

You will update the layout to show information about an invoice, including related time entries.

  1. Change the title on the Invoice Detail layout in the Header part from “Jobs” to “Invoices”.

  2. Update the master detail portal located on the left of the layout:
    1. Open the Portal Setup windows by double clicking on the portal.
    2. In the Show records from: dropdown select Current table “INVOICE”.
    3. Change the Status field to show Job_Name from the invoice_JOB table.
    4. Change the z_Name_display field to show Invoice_Name from the Current table “INVOICE”.
  3. Change the subtitle in the Body part from “JOB DETAIL” to “INVOICE DETAIL”.
  4. Modify the merged field from the <<z_Name_Display>> to <<Invoice_Name>>.
  5. Delete the <<job_CLIENT::Name>> button below <<Invoice_Name>>.
  6. Change the Status field on the top right side of the layout show the Status field from the INVOICE table occurrence. You will create a new value list with invoice statuses and have the Status field show values from it.
  7. To do that go to File > Manage > Value lists… Click on the button New… Name your value list Invoice | Status and type in the following custom values and then click OK:

       Not Sent
  1. In Layout mode while the Status field is selected, open the Data tab of the Inspector.
    1. Choose Values from: Invoice | Status
    2. Check Include arrow to show and hide list
  1. Go to the Time Entries tab, select the portal and the Add Time Entry button. Copy those objects to your clipboard.
  2. Delete the whole Tab Control and the gray line and paste the objects from the clipboard onto the free space on the layout, similar to the image below
  1. Now, you will update the portal to show related time entries. Open the Portal Setup windows by double clicking on the portal.
  2. In the Show records from: dropdown, select the invoice_TIMEENTRY table.
  3. Choose to Sort portal records by the Date_TimeEntry field, descending.
  4. Enter “5" for the number of rows to show and close the Portal Setup window.
  5. Change the job_TIMEENTRY::Date_TimeEntry field to invoice_TIMEENTRY::Date_TimeEntry. Delete all of the other fields from the portal.
  6. Add the following fields to the portal, from the invoice_TIMEENTRY table occurrence:
    1. Rate
    2. Hours
    3. Total
  7. It would be useful to see the name of the task for every time entry. To achieve that, open the Manage Database dialog and select the Relationships tab.
    1. Create a new table occurrence based on the Task table and name it “invoice_timeentry_TASK”.
    2. Create a relationship between invoice_TIMEENTRY and invoice_timeentry_TASK by matching the invoice_TIMEENTRY::ID_Task field to the Invoice_timeentry_TASK::ID field.
    3. Once your relationship is created, add the invoice_timeentry_TASK::Task_Name field to the portal between the Date_TimeEntry and Rate field.
    4. The first row in the portal has a button. Double-click the button and modify the Optional script parameter to “Invoice to Time Entry”. You’ll update the Go to Related record (context) script later in this lesson.
  8. Create labels for each of the portal fields (if they were not automatically created in Step 16) and place them above the portal.
  9. Using the Inspector update the portal fields and labels as follows:
    1. Using the Styles tab, style the following labels as label List.Left: Date, Task.
    2. Style the rest of the labels as label List.Right: Rate, Hours, Total.
    3. Style the following fields in the portal as txt Minimal.Black.Left: Date_TimeEntry, Task_Name.
    4. Style the remaining fields in the portal as txt Minimal.Black.Right and align the labels to the right side of their respective fields: Rate, Hours, Total.
    5. Select the Rate field and the Total field and in the Data tab of the Inspector, update the Data Formatting: Select Currency, Fixed number of decimals: 2, and select Use thousands separator.
    6. Also, set the Hours field as Decimal, showing 2 decimal places.
    7. Select all of the fields in the portal and in the Data tab of the Inspector, deselect Browse Mode under Field Entry.
    8. Select the Task_Name field and in the Position tab of the Inspector, set the tooltip to Self.
    9. In the Position tab update the Autosizing of the fields below to anchor as follows:

      Date and label: left, top
      Task_Name and label: left, top, right
      Hours, Rate, Total, and corresponding labels: top, right
  10. Using the Fields tab, drag and drop the invoice_JOB::Job_Name, invoice_job_CLIENT::z_Name_Display and INVOICE::Date fields with labels to the body of the layout above the portal. (If the Fields tab is not open, click the objects pane icon in the status toolbar, then click Fields.)
  1. Style the Job_Name and client z_Name_Display as txt disabled.Grey.Left.
  2. Open the Position tab of the Inspector and set the tooltip to Self for these fields.
  3. While on the Data tab, select the Date field. Set the Control style to Drop-down Calendar. Check Include icon to show and hide calendar.
  4. Set the Data Formatting for the Date field as 12/25/14 with Zero as the leading day numbers and month numbers.
  5. Rename the labels to “Job Name”, “Client”, and “Date”. Style the labels of the three fields as label Left.
  6. Using the text tool, add the following text below the portal “Invoice Total: <<z_Invoice_Total>>”.

    Style it as Default Right, and adjust the Autosizing to anchor bottom and right. In the Data Formatting section of the Data tab, choose the number icon. Set the Format to Currency with a Fixed number of decimals of 2 and the thousands separator turned on.
  1. You don’t want a user to be able to add time entries to an invoice once it is marked as sent or paid. To achieve that, select the Add Time Entry button. Navigate to the Data tab of the Inspector and set the Hide condition to: INVOICE::Status = "Sent" or INVOICE::Status = “Paid”

    You will complete the setup of this button later in this lesson.

Step 3 - Update Client Detail Layout

Update the Client Detail layout to show a list of invoices for a client. Similarly to what you did on the Job Detail layout, you will create an Invoice tab with a portal to show invoices.

  1. Enter Layout mode.
  2. Navigate to the Desktop > Client > Client Detail layout.
  3. Double-click on the Tab Control and open the Tab Control Setup window.
  4. Type “Invoices” in the Tab Name and click the Create button. Each tab panel has a blue line under the tab name to indicate the active tab. To add this to the new Invoices tab:
    1. Click the Invoices tab to make sure it is the active tab.
    2. Scroll to the right and notice the object off the visible right edge of the layout.
    3. Copy the blue horizontal line and drag it under the Invoices tab name.
  1. Copy the portal and the total label from the Jobs tab and paste them onto the new Invoices tab.
  2. To have a total number of invoices above the portal, similar to the total number of jobs on the Jobs tab, you will create a field in the Client table. Name the calculation field “z_Invoice_Count” with the following formula, turning off Do not evaluate if all referenced fields are empty and selecting Number as the Calculation result:

    NOTE: Remove this field from the layout if it was automatically added to it.

c = Count ( client_job_INVOICE::ID )
] ;
If ( c > 0 ; c ; 0 )

  1. Back on the layout, change the merge field “Total Jobs: <<z_Job_Count>>” to “Total Invoices: <<z_Invoice_Count>>”
  2. In the Portal Setup window have the portal show records from the client_job_INVOICE table.
  3. Sort records by Date in descending order.
  4. Delete all of the fields, and the Start Date label, from the portal.
  5. Update the background button on the portal to perform the same script but set the Optional script parameter to “Client to Invoice”.
  6. Place the following new fields from the client_job_INVOICE and arrange them similar to the image below and style the fields as follows via the Styles tab of the Inspector:
    1. ID, style: txt Minimal.Black.Left
    2. ID_Job, style: txt Minimal.Black.Left
    3. Status, style it as txt Minimal.Black.Left
    4. Date, style: txt Minimal.Black.Left
    5. z_Invoice_Total, style: txt Minimal.Black.Right
  1. To resize the fields properly when a user increases the size of the window, use the Position tab of the Inspector to set Autosizing for the following client_job_INVOICE fields.
    1. The ID field should already be anchored to the top and left.
    2. Choose the ID_Job field and set it to anchor top, left, and right, so that it will extend with the window resize.
    3. Choose the Status, Date, and z_Invoice_Total fields. Set the anchor to top and right, turning off the left anchor.
  2. Select all of your newly added fields. Using the Data tab of the Inspector, turn off Browse Mode in Field entry, so users can’t modify the data in the portal rows from this layout.
  3. Select the Date field and, with the same Inspector tab, apply Data Formatting:
    1. Select the following Format: 12/25/14
    2. Set the Leading character to Zero for both day numbers and month numbers
  4. Select the z_Invoice_Total field and apply Data Formatting:
    1. Set the Format to Currency
    2. Select Fixed number of decimals: and set it to 2
    3. For Currency select -$1234, and set Symbol: to $.
  5. To have the ID_Job field show the job’s name and not the ID, you will add a value list to the field.
    1. Select the ID_Job field and open the Data tab of the Inspector.
    2. Set the Control style for the ID_Job field to Pop-up menu.
    3. Set Values from: to the Job ID and Name value list.

      • Turn off Include arrow to show and hide list. Turn on Override data formatting with value list.

Step 4 - Update navigation script

You have modified buttons on multiple layouts to run an existing script, but you have created new script parameters. In order to make these buttons functional, you will update the Go to Related record (context) script to be able to do the following: Go to Invoice Detail from the Job Detail, go to Invoice Detail from the Client Detail, and open Time Entry | Card when clicking on the portal on the Invoice Detail.

  1. Add these steps close to the end of the Go to Related record (context) script:

Else If [ $context = “Client to Invoice” ]
   #Client to Invoice
   Go to Related Record [ Show only related records ; From table: “client_job_INVOICE” ; Using layout: “Invoice Detail” (INVOICE) ]

Else If [ $context = “Job to Invoice” ]
   #Job to Invoice
   Go to Related Record [ Show only related records ; From table: “job_INVOICE” ; Using layout: “Invoice Detail” (INVOICE) ]

Else If [ $context = “Invoice to Time Entry” ]
   #Invoice to Time Entry
   Go to Related Record [ Show only related records ; From table: “invoice_TIMEENTRY” ; Using layout: “Time Entry | Card” (Time Entry) ; New Window]
   Set Variable [ $$CONTEXT ; Value: “Time Entry” ]
   Set Variable [ $$CARD_ACTION ; Valu: “Edit” ]

  1. For the Go to Related Record step for Invoice to Time Entry section, you want the Time Entry card to display in a new card window. For this step in the “Go to Related Records” Options dialog, select Show in new window for Results Options. In the subsequent dialog set the Window Style to Card, select the Time Entry | Card layout and uncheck Close under Window Options.
  2. Save the script and close the Script Workspace.

Step 5 - Review your work

You have completed this lesson. Now go to Browse mode and test your updates.

  1. You should be able to see a list of invoices per client on the Client Detail layout.
  2. You should be able to get to Invoice Detail by clicking on the invoice portal on the Client Detail.
  3. You should be able to see Invoice Detail layout with all invoice information in it.
  4. You should be able to see Time Entry details by clicking on the time entries portal on Invoice Detail.