Build-On Tutorial:

Task Report

Task Report: Lesson 2

Modify Task Report

Lesson Overview

In lesson 1, you created a task report to show Pending and In Progress tasks, as well as the workflow to access the report. Now you will modify the report to summarize those tasks either by the assigned staff member or by job. You will use sub-summary parts and some scripting to accomplish this.

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

Step 1 - Add sub-summary parts

Add sub-summary parts to the Task Progress Report to allow grouping by the assigned staff member or by job.

  1. Enter Layout mode and navigate to the Task Progress Report layout.
  1. Open the Part Setup dialog (Layouts > Part Setup...) and create a new part.
  2. In the resulting Part Definition dialog, chose the Sub-summary when sorted by option, and select task_STAFF::Name_Full as the break field.

    Records with the same value in the break field will be grouped together in a sub-summary report.
  1. Select the Print Above option when asked whether the new part should be above or below the records that it summarizes
  2. Create a second sub-summary part. Choose task_JOB::Job_Name as the break field, and select the Print Above option for this part, too.
  1. In the Part Setup dialog, order the parts so that the Job sub-summary is above the Staff sub-summary, and then click Done to exit the dialog.
  1. Use the Position tab of the Inspector to set the Height of each sub-summary part to 64 pt.
  1. Use the Styles tab of the Inspector to set the style for both of the sub-summary parts to background Gray. Set the style for the Body to body List.
  2. Add the task_JOB::Job_Name field to the top sub-summary part, and task_STAFF::Name_Full to the other. Size and position them as shown below.
  1. Set the style for each field to txt Minimal.Black.Left.Bold.
  2. On the Data tab of the Inspector set the placeholder text for each field to say "(Not Assigned)".

Step 2 - Additional layout modifications

There are a few other layout modifications that will be useful for the finished report.

  1. Use the Part Setup dialog to add a Trailing Grand Summary part to the layout.
  2. Set the Height of the new part to 64 pt, and assign it the background Minimal.White style.
  3. Add a text object to the layout that says "No tasks in progress".
    1. Use the Data tab in the Inspector to specify the following formula as the Hide object when condition:
         Get( FoundCount ) > 0
    2. Leave the style for this object as Default.
  1. Duplicate the text object that contains "Assigned To: <<Staff_Name>>".
  2. Double-click into the duplicated object and change the text to "Job: <<task_JOB::Job_Name>>". You can type the name of the field manually, or use the Insert > Merge Field... dialog to add it.

    Note: The benefit of duplicating an existing object rather than creating one from scratch is that it will have the same attributes and size as the original. That can also be a risk if there are attributes that would not be appropriate for the new object, so use your good judgement to determine which method to use.
  3. Position the duplicated objects directly on top of the previous one. In a later step, you will add hide conditions for each so that only one displays at a time.
  4. Enter Browse mode to save and review your work.

Step 3 - Add the group by selector

Next, you will add a dropdown menu that allows the user to specify whether they want the report sorted by Staff or by Job.

  1. Go to the Manage Database dialog and add a new text field named "g_Task_Report_Sort" to the Global table.
  2. Click the Options button for the new field. On the Storage tab, select the option to Use global storage (one value for all records).

    Note: A global field has the same value for every record in a table and is unique for each user, in a hosted solution. This makes it ideal for allowing users to specify preferences for sorting and filtering. Such preferences are not field data and do not need to be stored and shared.
  1. Exit the Manage Database dialog.
  2. Add the new field to the Header of the Task Progress Report layout. Add the words "Group By:" as a label for it, as shown below. You can use the Default style for both objects.
  1. Select g_TaskReport_Sort field, and then activate the Data tab of the Inspector.
  2. Set the Control style to Pop-up Menu.
  3. Click the pencil icon next to the Values from: option. This will take you to the Manage Value List dialog.
  4. Create a new value list called "Task Report Sort Options", and specify “Job" and “Staff” as the custom values as shown below.
  1. Save and exit from the Manage Value Lists dialog.
  2. Check the option to Include arrow to show and hide list.

Step 4 - Create script

The next step is to create a script that will sort the found set based on the selection in the global field.

  1. Open the Script Workspace (Scripts > Script Workspace...), and duplicate the Template Script.
  2. Name the new script "TRIGGER_Task Report Sort" and move it to the Task > Task Progress Report folder.
  3. Modify the script so that it sorts the found set based on the value in the g_Task_Report_Sort field. The new steps you need to add are highlighted below.

    (See Step 4 for further instructions on the Sort Records step.)

    Note: It is important that the fields you sort by in this script are the same ones you specified as the break fields for the parts added in step 1. If a subsummary report does not work properly, chances are that either the records are not sorted, or the break field is not in the sort order.

    A subsummary part will only be visible when records are sorted by the break field. This means that as long as the records are sorted either by Staff or Job, only one of the subsummary parts will ever be visible at a time. In effect, this allows you to have one layout that appears to display multiple different reports (here, tasks summarized by staff and tasks summarized by job).

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

If [ _GLOBAL::g_Task_Report_Sort = "Staff" ]
   # sort by task_STAFF::Name_Full and Date_Due - both ascending
   Sort Records [ Restore; With dialog: Off]

Else If [_GLOBAL::g_Task_Report_Sort = "Job" ]
   # sort by task_JOB::Job_Name and Date_Due - both ascending
   Sort Records [ Restore; With dialog: Off]

End If

Go to Record/Request/Page [First]

Exit Script [ Text Result ; $null ]

  1. For the first Sort Records script step, sort the task list by the full name of the assigned staff member and by due date. Open the Sort Records dialog by clicking the blue icon to the right of the script step and then Specify. Add the following fields to Sort Order:
    1. Task_STAFF::Name_Full — select Ascending order.
    2. Due_Date — select Ascending order.
  1. Specify the second Sort Records similarly, sorting first by task_JOB::Job_Name and then by Date_Due.
  1. Save the script and close the Script Workspace.

Step 5 - Set up script trigger

You will add a script trigger to the g_Task_Report_Sort field so that the records will sort appropriately whenever a user makes a selection.

  1. Right-click on the g_Task_Report_Sort field and select Set Script Triggers... from the contextual menu.

    Note: You can also select the field and then use the Format > Set Script Triggers... menu item.
  2. Select the OnObjectModify event in the Set Script Triggers dialog, and then choose the TRIGGER_Task Report Sort script that you wrote in step 4.

Step 6 - Final clean up

There are just a few final items to take care of to finish the Task Report! You will set hide conditions for the stacked merge fields on the layout, and you make a few modifications to the script you wrote in lesson 1 so that the report is sorted by Job when a user first views it.

  1. Select the text block that contains "Assigned To: <<Staff_Name>>". You can do this using the Objects tab of the objects pane, or by temporarily moving or hiding one of the stacked objects so you can independently select each easily.
  2. Use the Data tab in the Inspector to set a hide condition for the object, with the following formula:

       _GLOBAL::g_Task_Report_Sort ≠ "Job"

    This will cause the assigned staff's name only to appear when the records are sub summarized by Job. It would be redundant for the name to appear when they are summarized by Staff because the name will appear in the sub-summary part.
  1. Similarly, select the object that contains "Job: <<task_JOB::Job_Name>>" and on the Data tab of the Inspector set the Hide object when condition with the formula: 

     _GLOBAL::g_Task_Report_Sort ≠ "Staff"
  2. In the Script Workspace, find and select the TRIGGER_Task Report On Entry you wrote for Lesson 1.
  3. Modify the script by adding the highlighted steps below. These steps will ensure that the report is summarized by Job when a user first views it.

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

Set Error Capture [On]

Enter Find Mode [Pause: Off]
Set Field [ TASK::Status ; "Pending" ]
New Record/Request
Set Field [ TASK::Status ; "In Progress" ]
Perform Find []

Set Field [ _GLOBAL::g_Task_Report_Sort ; "Job" ]

Perform Script [ Specified from list ; "TRIGGER_Task Report Sort" ; Parameter: ]

Exit Script [ Text Result ; $null ]

  1. Finally, in Layout mode, select all of the fields except for g_Task_Report_Sort. On the Data tab on the Inspector, in the Field Entry section, turn off Browse mode, so users cannot enter the fields.

Step 7 - Review your work

Congratulations, you have completed all the lessons for creating the Task Report!

  1. When you run the Task Report from the Dashboard or the navigation card window, the report should default to being summarized by Job.
  2. You should be able to change the summarization just by selecting Job or Staff in the Group By dropdown.
  3. When the report is summarized by Staff, you should see the Job Name in the body of each record.
  4. When the report is summarized by Job, you should see the Staff Name (or N/A) in the body of each record.