Build-On Tutorial:

Invoicing

Invoicing: Lesson 3

Adjust Time Entries on an Invoice

Lesson Overview

In this lesson you will add functionality that will allow a user to delete time entries from an invoice and add selected time entries to an invoice. You will also create a new privilege set, along with a test account, to limit when a user can edit sent or paid invoices.

Here’s a sneak peek of what you will build:

Step 1 - Create "Remove Time Entry from Invoice" script

You will create a script that will let user delete time entries from an invoice.

  1. Open the Script Workspace (Scripts > Script Workspace…). Duplicate the Template script.
  2. Name the new script “Remove Time Entry from 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 an appropriate folder.
  3. Add the following steps to your script:

    NOTE: See step 4 for details on the Show Custom Dialog script step.

Show Custom Dialog [ “Are you sure you want to remove this time entry from this invoice?”]

If [ Get ( LastMessageChoice ) = 2 ]

   # User clicked Button 2: Yes
   Set Field [ invoice_TIMEENTRY::ID_Invoice ; “” ]
   Commit Records/Requests [ With dialog: On ]

End If

  1. In the Show Custom Dialog script step, set the Default Button to “No” and Button 2 to “Yes”.
  1. Save the script and close the script workspace.

Step 2 - Add delete button

You will add a button to delete time entries from the invoice.

  1. Open the Invoice Detail layout and go to Layout mode.
  2. Using the Button tool in the Status toolbar add a button next to the Total field on the portal. In the Button Setup window select the Display only icon option, then choose the small x icon.
  3. Have the button perform the script Remove Time Entry from Invoice.
  1. With the button still selected, open the Position tab of the Inspector and set anchoring to the top and right side of the layout. Set the style to btn Minimal.Gray.
  1. You wouldn’t want a user to be able to delete time entries from an invoice once an invoice has already been sent or paid. To achieve that, go to the Data tab of the Inspector and set the Hide condition to: INVOICE::Status = "Sent" or INVOICE::Status = “Paid”

    The Invoice Detail layout should look like the image below.

Step 3 - Create Time Entry List (Card) layout

You will create a new card window with a list of time entries. A user will be able to view a list of job-related time entries in a card window, select one or more time entries, and then add them to the invoice.

  1. Duplicate the Job List | Card layout.
  2. Rename it to Time Entry List | Card and choose to Show records from: the table occurrence named TIMEENTRY and place the layout in the "Time Entry" layout folder.
  3. Open the new layout and go to Layout mode.
  4. Rename the Header text from “Assign Job” to “Select Time Entries”.
  5. From the menu, select Layouts > Part Setup… Select the Trailing Grand Summary part and select Delete.
  6. Delete the background button from the Body of the layout.
  7. Resize fields to have a Width of 288 pt.
  8. Create a new number field in the TimeEntry table named “Flag_Add_Invoice”. This field will be used to flag whether or not a time entry has been selected for an invoice from the card window.
  9. Place the Flag_Add_Invoice field on the layout to the left of the rest of the fields. Open the Data tab of the Inspector and set the Control style to Checkbox Set. Then set Values from: to the value list named 1. In the Position tab, set the Width to 24pt and the Height to 35pt.
  10. From here, you can repurpose existing fields to fit your time entry needs. Double-click the z_Name_Display field and change it to the timeentry_TASK:: z_Name_Display field (select the timeentry_TASK table from the drop-down menu and select the ::z_Name_Display field). Style as txt Minimal.Black.Left.Bold.
  11. Then, change the Status field to the timeentry_STAFF:: z_Name_Display field.
  12. On the Position tab of the Inspector add a tooltip of Self to both fields. This will show the whole contents of the field when user hovers over each field.
  13. Add two more fields (Insert > Field) to the layout Body:
    1. TIMEENTRY::Date_TimeEntry
    2. TIMEENTRY::Hours
  14. Select the Date_TimeEntry field. Open the Data tab of the Inspector. In the Data Formatting section, set the Format to 12/25/14 with Zero as the leading number for both day numbers and month numbers.
  15. Select the Hours field. Set the Format to Decimal, Fixed number of decimals: 2, Symbol: h, and Format: -1234.
  1. Resize Date_TimeEntry and Hours fields to have a Width of 88 pt. Place the Date_TimeEntry field on top and the Hours field below, similar to the two fields and place them to the right. Style them as txt Minimal.Black.Right.
  2. Resize the Cancel button to have a Width of 200 pt and move it to the left side in the Footer.
  3. Create a new button and place it next to the Cancel button and name it “Add to Invoice”. You will create a script for this button later in this lesson.

Step 4 - Create "Open Time Entry List" script

You will create a script that allows a user to open the time entry list (card) layout from the Invoice Detail layout. With this layout, you will show time entries that belong to the invoice’s job. You'll only show time entries that are billable and are not assigned to another invoice.

  1. To see a list of time entries that can be added to the invoice, you will create a new relationship. Open the Manage Database dialog and create a new table occurrence from the TimeEntry table and name it “invoice_job_TIMEENTRY”.
  2. Create a relationship between invoice_JOB and invoice_job_TIMEENTRY matching invoice_JOB::ID to invoice_job_TIMEENTRY::ID_Job.
  1. Open the Script Workspace (Scripts > Script Workspace…). Duplicate the Template script.
  2. Name the new script “Open Time Entry List”. 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:

Set Variable [ $$ID_INVOICE ; Value: INVOICE::ID ]
Set Error Capture [ On ]

#find all time entries that belong to the current job
Go to Related Record [ Show only related records; From table: “invoice_job_TIMEENTRY”; Using layout: “Time Entry List | Card” (TIMEENTRY); New Window ]

  1. When setting up the Go to Related Record step and specifying New Window Options, make sure to select Window Style: Card, set the Height to 650 pt, and uncheck all of the Window Options except for Dim parent window.
  1. Continue with the following steps, where both Show Custom Dialog script steps have "Message" for the Title and should only have a Default Button of OK and no Button 2:

If [ Get ( LastError ) ≠ 0]
   Show Custom Dialog [ “Message” ;
   “There are no time entries that can be added to this invoice.]
Else

   #find all time entries that are billable and are not linked to any invoice
   Enter Find Mode [ Pause; Off ]
   Set Field [ TIMEENTRY::ID_Invoice; “=” ]
   Set Field [ TIMEENTRY::TypeBillable; “==Billable” ]
   Constrain Found Set []

   If [ Get ( LastError ) ≠ 0]
      Close Window [ Current Window]
      Show Custom Dialog [ “Message” ; “There are no time entries that can be added to this invoice.”]
   End If
End If

  1. Save the script and go to the Invoice Detail layout and edit the Add Time Entry button to perform this script without any script parameters.

    When you are ready to test, note that not all jobs will have time entries. Create a job and add billable time entries before testing, to make sure you have reliable data.

Step 5 - Create "Add Time Entry to Invoice" script.

  1. Open the Script Workspace (Scripts > Script Workspace…). Duplicate the Template script.
  2. Name the new script “Add Time Entry to Invoice”.
  3. Add the following steps to the script:

    NOTE: For additional details on the settings of Replace Field Contents steps, see Step 4 and 5 respectively.

Set Error Capture [ On ]

Enter Find Mode [ Pause; Off ]
Set Field [ TIMEENTRY::Flag_Add_Invoice; 1 ]
Constrain Found Set []

Replace Field Contents [ With dialog: Off; TIMEENTRY::ID_Invoice ; $$ID_INVOICE]
Replace Field Contents [ With dialog: Off; TIMEENTRY::Flag_Add_Invoice ; “”]

Close Window [ Current Window ]

Commit Records/Requests [ With dialog: Off]

  1. For the first Replace Field Contents step in the script, select the gear icon to the right and select Specify target field, then select the TIMEENTRY table and the ID_Invoice field.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 the second Replace Field Contents step in the script , select the gear icon to the right and select Specify target field, then select the TIMEENTRY table and the Flag_Add_Invoice field.Click the icon again and next to Replace with click Specify. In the dialog, select Replace with calculated result and enter ”” (no space between).
  1. Save the script and go to the Time Entry List | Card layout and have the Add to Invoice button perform this script without any script parameters.

Step 6 - Create Privilege Set

In order to prevent your users from modifying an invoice or its content when an invoice is already sent or paid, you will create a new Privilege Set that will limit what a user can do.

Note: Later, when you create accounts for your users, you will have to assign their account to the new Privilege Set in order to limit the user.

  1. In order to have access to the invoice status field from the TimeEntry table, create a new table occurrence based on the Invoice table and name it “timeentry_INVOICE". Create a relationship between TIMEENTRY to timeentry_INVOICE table occurrences. Match ID_Invoice from TIMEENTRY to the ID from timeentry_INVOICE.
  1. Open File > Manage > Security… and enter your full access credentials.
  2. Go to the Privilege Sets tab (If you do not see this tab, select the Use Detailed Setup… button at the bottom.)
  3. Click on the New… button and a new Privilege set will be created. Name it Invoice.
  4. Add the following Description: “Full access except sent or paid invoices and timeentries”. In the Data Access and Design part, set Layouts and Value Lists to All view only.
  5. Set Scripts to All executable only. Set up the rest of the options as shown below:
  1. To limit access to invoices, set Records to have Custom privileges… and the Custom Record Privileges window will appear.
  1. Select Invoice and in the Set Privileges: section, set the View and Create actions to yes, with Field Access set to all.
  1. Set the Edit action to limited… and in the Specify Calculation window, type the following calculation:

    INVOICE::Status = "Not Sent" or INVOICE::Status = “Canceled”.

    This means that user with the Invoice Privilege Set will only be able to edit invoice records if the status of an invoice is either “Not Sent” or “Canceled”.
  2. The Delete action should be limited… as well with the same condition as the Edit action.
  3. The TimeEntry table will need similar settings but from the timeentry_INVOICE context:

    timeentry_INVOICE::Status = "Not Sent" or timeentry_INVOICE::Status = "Canceled" or IsEmpty ( timeentry_INVOICE::Status )
  4. Allow the user to have full access to the rest of the tables. To do so, set the View, Edit, Create, and Delete actions to yes and the Field Access to all.

    Note: You can Command-click or Control-click to select more than one table and to apply changes to the selected tables at once.
  1. Now create a test account and assign the Invoice privilege set to it:
    1. Go to Accounts tab of the Manage Security window and click on the New… button.
    2. Type in the name test and password 123.
    3. Set the Privilege Set to Invoice.
  1. Save all of your changes by entering your credentials. Note that you’ll want to delete this test account once you’ve made sure your account works as expected.

Step 7 - Review your work

You’ve completed this lesson. Go back in Browse mode and test your changes.

  1. You should be able to delete time entries from the invoice for an invoice set to a status of “Not Sent” or “Canceled".
  2. You should be able to add billable time entry to invoice set to a status of “Not Sent” or “Canceled".
  3. You should not be able to modify and delete invoices or related time entries, if the status is “Sent” or “Paid”. To test this functionality, log in under the test account and try to modify and delete an invoice with a status of “Sent” to “Paid”. Note that the limitation won’t take effect until the invoice record is committed, after changing the invoice status. Perform the same test for time entries that belong to a sent or paid invoice.