Build-On Tutorial:

Task Report

Task Report: Lesson 1

Create Task Report

Lesson Overview

In this lesson, you will create a report that finds and lists tasks that are pending or in progress. The report will be modified further in the next lesson to summarize those tasks either by Staff or Job.

Step 1 - Create fields

The task report will include new fields to track the estimated and remaining time (in hours) for a given task. Begin by adding those fields to the Task table.

  1. Open the Manage Database dialog (File > Manage > Database...).
  2. Activate the Fields tab and select the Task table from the table dropdown.
  3. Create a new field named Hours_Expected. For Type, select Number.
  4. Create a new field called Hours_Remaining. For Type, select Calculation with the following formula: Hours_Expected - z_Hours_Total

    Be sure the Calculation result is set to Number.
  1. Exit the Manage Database dialog.

Step 2 - Add fields to the Task Detail layout

Next, you will add the two fields created in Step 1 to the Task 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 > Task > Task Detail layout.
  3. Using the Fields tab, drag and drop the Hours_Expected and Hours_Remaining fields to the body of the layout. (If the Fields tab is not open, click the objects pane icon in the status toolbar, then click Fields.)
  1. Place the fields with their labels below the Date_Start and Date_Due fields. Change the label from Hours_Expected to "Expected Hours" and Hours_Remaining to "Hours Remaining"
  2. If it is not already open, 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 Styles tab of the Inspector, which is used for specifying object styles.
  1. Select each of the new fields on the layout and apply the txt Black.left style for it in the Inspector.
  2. Select each of the field labels and apply the label Left style.
  1. Use the Exit Layout button to return to Browse mode and review your progress. Enter data for the Hours_Expected field for several records to help with your testing.

    Note: You can also enter Browse mode using the View > Browse Mode menu item, or the keyboard shortcut Command-B (macOS) / Control-B (Windows).

Step 3 - Create the new report layout

Create the new task report layout.

  1. Enter Layout mode.
  2. Use the Layout pulldown menu in the status toolbar to switch to the Blank > Blank | Desktop - List 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…)
    1. Rename the layout to "Task Progress Report".
    2. Change the Show records from: dropdown to the TASK table occurrence. This setting determines the context of the layout.
  1. Activate the Position tab of the Inspector, then select the Header part by clicking on its label and change the Height setting to 200. Remove the sample label and field.
  1. Double-click on the "Title" text in the header so you can edit it. Change the text to "Task Report - Pending and In Progress".
  2. Add the following fields to the Body part of the layout.
    1. Date_Due
    2. Status
    3. Hours_Expected
    4. z_Hours_Total
    5. Hours_Remaining
  3. Set the style for Date_Due and Status to txt Minimal.Black.Left. Set the style for three other fields to txt Minimal.Black.Right.
  4. Create text objects for use as column header labels as shown below.
    1. Use label List.Left as the style for Due Date and Status
    2. Use label List.Right as the style for Expected, Worked and Remaining.
    3. Use caption Right for the Hours labels.

      Note: Sizing the labels to the same width as the fields below makes aligning and positioning them easier.
  1. Use the Data tab of the Inspector to specify data formatting for the three hours fields:
    1. Set the Format to Decimal.
    2. Set Fixed number of decimals: to 2.
    3. For the Hours_Remaining field, set the Color to be red for negative numbers, as shown below.
  1. For the Date_Due field, format it as 12/25/14 with Zero as the leading character for both day numbers and month numbers.
  2. Open the Manage Database dialog, and add calculation field named Staff_Name with the following formula, setting the calculation result to Text:

       If ( not IsEmpty( ID_Staff ) ; Task_STAFF::Name_Full ; "N/A" )

    This formula returns the name of the staff person assigned to the task, or N/A if there isn't anyone assigned to it.
  3. Select Insert > Merge Field... to add two merge fields to the layout.
    1. Add the z_Name_Display to the layout in the blank area after the Status field.
    2. Add the Staff Name field as a second merge field.
    3. Double-click into the Staff Name object to add the text "Assigned to: " before the merge field. Use the caption Left style for this text block.

      Merge fields are text objects that show field data. Users cannot click into them or modify them as they can with regular field objects.
  1. Enter Browse mode and review your progress on the report.

Step 4 - Create script

The next step is to create a script that will find just the Pending and In Progress tasks.

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...).
  2. Create a new folder named "Task Progress Report".
  3. Duplicate the Template Script. Rename the script to "TRIGGER_Task Report On Entry" and move it into the folder you just created.
  4. Modify the script so that it performs a find for Pending and In Progress tasks. The new steps you need to add are highlighted below.

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 []

Exit Script [ Text Result ; $null ]

  1. Save the script and close the Script Workspace.

Step 5 - Set up script trigger

Next, you will add a script trigger to the report layout so that the script from Step 4 is activated upon entering the layout.

  1. Open the Manage Layouts dialog (File > Manage > Layouts...).

    Note: You do not need to be in Layout mode to access this dialog. The instructions for this step can also be completed by accessing the Layout Setup dialog from Layout mode.
  2. Locate and select the Task Progress Report layout, and then click the Edit button at the bottom of the dialog.

    Note: This would also be a good time to move the layout into a new Desktop > Task Progress Report folder.
  3. Switch to the Script Triggers tab in the resulting Layout Setup dialog. Select the OnLayoutEnter event and specify the TRIGGER_Task Report On Entry script.
  1. Close the Layout Setup and Manage Layout dialogs. The find script will now trigger anytime a user or script navigates to your report layout.

Step 6 - Create report workflow

The final step is to create the workflow that will allow a user to access the report you have created. You will add buttons to the Dashboard and Menu | Card layouts to accomplish this.

  1. Enter Layout mode and navigate to the Desktop > Dashboard layout.
  2. Duplicate one of the existing navigation buttons by selecting it and then using the keyboard shortcut Command-D (macOS) / Control-D (Windows).
  3. Double-click the new button to open the Button Setup dialog. While in this dialog, make the following changes:
    1. Change the button name to Task Report.
    2. Change the icon to an appropriate icon.
    3. Change the Optional Script Parameter: to "Task Progress Report". The button calls an existing script that will navigate to the layout named by the script parameter, which will in turn activate the script trigger you set up in Step 5.
  1. Rearrange all of the icons on the layout so they are in a pleasing configuration.
  1. Enter Layout mode and navigate to the Desktop > Menu | Card layout.
  2. Double-click any of the existing links to bring up the Button Bar Setup dialog. Navigate to the last segment in the button bar and then click the plus button to add a new segment.
  1. In the Button Bar Setup dialog:
    1. Set the name of the segment to "Task Report".
    2. Set the Action: for the new segment to Perform Script.
    3. Select the Go To... From Navigation ( navigation ) script.
    4. Set the script parameter to "Task Progress Report".
  1. Adjust the height of the Button Bar so each button is 50 pt. Close the Button Bar Setup dialog. The layout should now appear as shown below, and you have completed this lesson!

Step 7 - Review your work

  1. You should now have new fields on the Task Detail layout for tracking the estimated hours for a task and seeing the hours remaining.
  2. From the Dashboard or the navigation menu on any layout, you should have the option of running a Task Report.
  3. When you run the Task Report, you should only see tasks that have a status of Pending or In Progress.
  4. The Task Report should list key information about tasks, including the name of the person assigned to do it.