Build-On Tutorial:

Job Report

Job Report: Lesson 1

Create the Job Report

Lesson Overview

The objective for this lesson is to create a new report for showing the status of jobs in their various stages and the number of hours that have been spent on them. You will use the tables, fields, and relationships that already exist in the Job Tracking Template core file to accomplish these objectives.

Here is a sneak peak of what you will build.

Step 1 - Create the layout

Create a new iPad layout by duplicating the existing Blank | Desktop - List layout.

  1. Open the Manage Layouts dialog (File > Manage > Layouts...).
  2. Locate the Blank > Blank | Desktop - List layout and Duplicate it.
  3. With the new layout selected, click the Edit button to open the Layout Setup dialog, and make the following changes:
    1. Set the Layout Name to “Job Report".
    2. Set the Show records from: to JOB.
    3. Set the Menu Set: to Minimal for Card Windows.
  1. Close the Layout Setup dialog.
  2. Use the dropdown arrow next to the New button at the bottom of the dialog to create a new folder named Job Report. Move it into the Desktop folder, and then place the new Job Report layout into it, as shown below. Make sure the Include in layout menus check box is deselected.
  1. Click the Open button to open the Job Report layout, and enter Layout mode.

    Note: Because of the limited menu set installed for this layout, you will need to manually switch to the standard menu set to have access to familiar menu items. (Tools > Custom Menus > [Standard FileMaker Menus])
  2. Using the Part Setup dialog (Layouts > Part Setup…), add a Trailing Grand Summary part
  1. Edit the title text at the top of the layout to say “Job Report".
  1. Delete the "Field Label" and sample field objects.
  2. Open the Inspector using the View > Inspector menu item (you can also access it using keyboard shortcut Command-I (macOS) / Control-I (Windows)).
  3. Activate the Position tab of the Inspector. Set the Height of the layout parts:
    1. Header: 192 pt
    2. Body: 64 pt
    3. Trailing Grand Summary: 64 pt
  1. Activate the Styles tab of the Inspector. Set the style of the Trailing Grand Summary to background Minimal.White

Step 2 - Add fields to the layout

Next, you will add fields and text objects to the new layout and specify styles for them using the Inspector tool.

  1. You should still be in Layout mode after completing Step 1. If not, re-enter Layout mode.
  2. Using the Fields tab of the Objects pane, drag and drop the _GLOBAL::g_Filter_Status field to the header of the layout. To locate the field, first select _GLOBAL in the table occurrence drop down list at the top of the tab. (If the Fields tab is not open, click the Objects pane icon in the status toolbar, then click Fields.)
    1. Change the field label to “Status:”.
    2. Activate the Data tab of the Inspector.
    3. Set the _GLOBAL::g_Filter_Status field’s control style to Pop-up Menu.
    4. Choose to show values from the Job Status value list.
  1. Using the Fields tab again, select the JOB table occurrence, and drag and drop the following fields to the Body of the layout. (Set the Drag Preferences to drop the fields on the layout horizontally and with labels on top, as shown below, to make things easier later.)
    • Status
    • z_Name_Display
    • Date_Start
    • Date_Complete
    • z_Time_Entry_Hours
  1. Using the Styles tab of the Inspector, apply the following styles:
    1. Status - style: txt Minimal.Black.Left
    2. z_Name_Display - style: txt Minimal.Black.Left
    3. Date_Start - style: txt Minimal.Black.Right
    4. Date_Complete - style: txt Minimal.Black.Right
    5. z_Time_Entry_Hours - style: txt Minimal.Black.Right
  2. Using the Data tab of the Inspector, apply data formatting on the following fields:
    1. Date_Start - format: 12/25/2014, with Zero as the leading character for both day number and month number
    2. Date_Complete - format: 12/25/2014, with Zero as the leading character for both day number and month number
    3. z_Time_Entry_Hours - format: Decimal, with a fixed number of decimals of 2
  3. Select the header labels for Status and z_Name_Display and assign the style label List.Left.
  4. Select the header labels for Date_Start, Date_Complete, and z_Time_Entry_Hours and assign the style label List.Right.
  5. Double-click the labels and rename them as follows:
    1. “z_Name_Display” to “Name”
    2. “Date_Start” to “Date Opened”
    3. “Date_Complete” to “Date Closed”
    4. “z_Time_Entry_Hours” to “Hours Worked”
  6. Create a summary field to count total hours:
    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. Select the Job table and create a summary field named z_sum_Hours.
    3. Set the summary options to Total of the z_Time_Entry_Hours field.
  1. On your Job Report layout, in Layout mode, duplicate the z_Time_Entry_Hours field. Change it to the z_sum_Hours field and move it into the footer. By doing so, your new field will have the same currency formatting as the z_Time_Entry_Hours field.
    1. Select the field and assign the style txt Minimal.Black.Right.Bold.
    2. Select the field label and rename it "Total Hours”.
  2. Place the objects as you see in the image below.
  1. Select the z_Name_Display field and go to the Position tab of the Inspector. Add a tooltip of Self, which will show a tooltip of the full job name when the job name is too long to display in the field.
  1. Select all of the fields in the body and footer. In the Data tab of the Inspector, turn off access in Browse mode so that records cannot be modified while in this report.
  1. Using the Position tab of the Inspector to set the Autosizing, so that fields move or stretch when the window is made larger.
    1. Choose the z_Name_Display field and anchor to the top, left, and right.
    2. Choose the Date_Start, Date_Complete, z_Time_Entry_Hours, and z_Sum_Hours along with their labels, and anchor them top and right.

Step 3 - Set up a script trigger

You need to create a script that will filter the Job Report to show a set of jobs based on the Status filter field.

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...) and select and duplicate the Template Script from the list of scripts on the left of the dialog. You will use this as the foundation for your new script. Make sure to place your script in the Job folder.
  2. Name the script TRIGGER_Job Report Find. This script will be run anytime the _Global::g_Filter_Status field is changed by the user.
  3. Following the Freeze Window step, add the following steps to the script:

    NOTE: This script will attempt to find Job records if there is a value in the Status filter field. If not, all records will be displayed to the user.

If [ not IsEmpty ( _GLOBAL::g_Filter_Status ) ]
   Set Error Capture [On]
   Enter Find Mode [ Pause: Off ]
   Set Field [ JOB::Status ; _GLOBAL::g_Filter_Status ]
   Perform Find []
   Show All Records
End If

  1. Close the Script Workspace. In Layout mode, right-click the g_Filter_Status field, then select Set Script Triggers… from the contextual menu. Select the OnObjectModify event and then the TRIGGER_Job Report Find script.
  1. Close the Set Script Triggers dialog, enter Browse mode, and test your new script by selecting a job status in the Status filter field.

Step 4 - Add a clear button

Users need to be able to clear the contents of the Status filter to show all records, so you will now create a new script and button to do that.

  1. Open the Script Workspace again. Duplicate the Template Script, and name the new script “Clear Job Report Filter”. Move this new script into the Job folder.
  2. Following the Freeze Window step, add two new steps.

Set Field [ _GLOBAL::g_Filter_Status ; "" ]

Perform Script [ Specified: From List ; "TRIGGER_Job Report Find" ; Parameter: ]

  1. Save the script and close the Script Workspace.
  2. In Layout mode, select the Button tool from the status toolbar and draw a rectangle in the Header part next to the Status field.
  1. In the subsequent Button Setup dialog:
    1. Set the title to “Clear".
    2. Set the Action: to Perform Script.
    3. Select the Clear Job Report Filter script.
  2. Close the Button setup dialog, and set the object style to btn Minimal.Blue.Previous.
  3. Select the new button and activate the Data tab of the Inspector. In the Hide object when calculation, add: IsEmpty ( _GLOBAL::g_Filter_Status )

    This will hide the button whenever the Status picker field is empty.

Step 5 - Add a message when no records are found

To gracefully display a message when no records are found by your filter, you will add some text to the trailing grand summary.

  1. Using the Text tool in the status toolbar, add some text to the Trailing Grand Summary: "No jobs found”
  2. With this text object selected, activate the Data tab of the Inspector. In the Hide object when calculation, add the following:

    Get( FoundCount ) > 0

Step 6 - Create sort functionality for the report

As a finishing touch for this report, you will add clickable field headers that sort the report ascending or descending by the specified column.

  1. Start by using the Position tab of the Inspector to name each of the fields in the Body part. These named field objects will be used to sort the report when they are called by parameter. Name each field identically to its column label.
  1. Open the Script Workspace and duplicate the Template Script. Name the new script "Sort Job Report", and move it to the Job folder.
  2. Add the following highlighted steps to the script.

    Note: The #( ) and #Assign ( ) custom functions are used to pass and parse parameters. The #( ) custom function is generally used to define a script parameter and accepts a key-value pair; the #Assign ( ) custom function is called in the script and turns the key-value pair into a script variable.

Allow User Abort [ Off ]
Set Error Capture [ Off ]

# parse the parameters
Set Variable [ $parameter_parsing_ok; Value: #Assign ( Get(ScriptParameter ) ) ]

# create global variables for use later
Set Variable [ $$SORT_FIELD; Value: $sort_field ]
Set Variable [ $$DIRECTION; Value: $direction ]

If [ $parameter_parsing_ok = False ]
  # parameters could not be passed, exit this script and report back
  Exit Script [ Result: "error" ]
End If

# go to the named layout field object to sort by that field
Go to Object [ Object Name: $$SORT_FIELD ]
If [ $$DIRECTION = "asc" ]
  Sort Records by Field [ Ascending ]
Else If [ $$DIRECTION = "dsc" ]
  Sort Records by Field [ Descending ]

End If
Go to Record/Request/Page [ First]

Exit Script [ Text Result: $null ]

  1. Save the script and close the Script Workspace.
  2. Next, turn the column headers into buttons that will sort the report:
    1. Right-click on each column header and select Button Setup.
    2. Set the Action: to Perform Script.
    3. Select the Sort Job Report script.
    4. Specify a script parameter, using the formula:

#( “SORT_FIELD" ; "" ) & #

( "DIRECTION" ; Case( $$DIRECTION = "dsc" or IsEmpty ( $$DIRECTION ) or $$SORT_FIELD ≠ "" ; "asc" ; "dsc" ))            


NOTE: Replace with the object name of each field that you previously assigned.      
For example, for "Date Opened", the parameter for the column sort button would be:

#( “SORT_FIELD" ; “Date Opened" ) & # 

( "DIRECTION" ; Case( $$DIRECTION = "dsc" or IsEmpty ( $$DIRECTION ) or $$SORT_FIELD ≠ "Date Opened" ; "asc" ; "dsc" ))

  1. Return to Browse mode to save your work.

Step 7 - Add the Job Report to the navigation

  1. Go to the Dashboard layout and enter Layout mode.
    1. Duplicate an existing button and rename it to Job Report.
    2. Change the script parameter to Job Report.
    3. Change the icon to something appropriate for this scenario.
    4. Rearrange the buttons to match the following screenshot.
  1. Open the Menu | Card layout in Layout mode.
    1. Double click on the Jobs button segment to activate the Button Bar Setup dialog.
    2. Add a new button segment, and name it Job Report.
    3. Set the Action: to Perform Script.
    4. Select the script Go To… From Navigation ( navigation )
    5. Set the Optional Script Parameter: to “Job Report”.
    6. Close the Button Bar Setup dialog and select the the Button Bar object. In the Position tab of the Inspector resize the height so that each button has a height of 50 pt (for example, if you have 9 button segments set the height to 450 pt, 9 x 50)

Step 8 - Review your work

Congratulations, you have finished creating the Job Report!

  1. When you run the Job Report from the Dashboard or the navigation card window for the first time, the report should show all records by default.
  2. You should be able to change the group of records you are viewing by selecting different statuses in the Status filter field.
  3. You should be able to clear your status selection by clicking the Clear button.
  4. You should be able to see a total number of hours worked for your found set, via the summary field in the footer.
  5. By clicking on the column headers, you should be able to sort the data ascending or descending.