Build-On Tutorial:

iPad Time Entry

iPad Time Entry: Lesson 2

See Past Week's Time

Lesson Overview

The objective for this build-on is to create a new portal on the iPad | Time Entry layout you created in Lesson 1 that shows a week’s worth of time entries for the selected staff member. You will create some new relationships and fields to accomplish this, and you will also use tables, fields, and relationships that already exist in the Job Tracking Template core file.

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

Step 1 - Create and set date fields

This step makes use of global fields, which are fields that have been set to use global storage and contain one value that is shared across all records in the file. You will use two global fields to control the date span for the portal. You will then update the TRIGGER_On_Open script to set these fields when file is opened.

  1. Open the Manage Database dialog (File > Manage > Database...).
  2. Select the Fields tab and add the following fields to the Staff table:
    1. zg_Date_Start — Set the Type: to Date, click Create and then with the new field highlighted, select Options. Select the Storage tab and check Use global storage (one value for all records).
    2. zg_Date_End — Set the Type: to Date, click Create and then with the new field highlighted, select Options. Select the Storage tab and check Use global storage (one value for all records).
  1. Open the Script Workspace and select the TRIGGER_On_Open script.
  2. Modify the script so that when the file is opened on the iPad, the date span will be set to the previous week. Set the end date to the current date, and the start date to a week prior. To do this, add the following steps after the last Else If script step:

# set global fields to current date and week prior
Set Field [ STAFF::zg_Date_End ; Get (CurrentDate) ]
Set Field [ STAFF::zq_Date_Start ; Get (CurrentDate) - 6 ]

  1. Save and close the script.

Step 2 - Add relationships

In order to view a list of the past week’s time entries for a specific staff member, you will create a relationship with multiple criteria: one matching the staff member to his/her time entries, and two matching the time span dates created in Step 1 to the corresponding time entry dates. You will also create additional relationships in order to see Job and Task data related to the time entries in the portal.

  1. Open the Manage Database dialog, and select the Relationships tab. Scroll down to the STAFF table occurrence.
  2. Add a new table occurrence by selecting the Add a table icon from the toolbar below the graph.
  1. From the Specify Table dialog, select the TimeEntry table and name it “staff_TIMEENTRY__pastWeek”.

    Note: You use “__pastWeek” suffix here to denote the relationship has special match criteria beyond a normal primary key to foreign key.
  1. First, you will relate staff members to their time entries. You will use the ID field (primary key) from the Staff table and the ID_Staff field (foreign key) from the TimeEntry table to create a relationship between the STAFF table occurrence and the new table occurrence you just created.
    1. Select Create a relationship icon from the toolbar.
    2. On the left, select the STAFF table occurrence form the drop-down.
    3. On the right, select the staff_TIMEENTRY__pastWeek table occurrence from the drop-down.
    4. Under the STAFF table occurrence, select the ID field.
    5. Make sure the = is selected from the drop-down in the center.
    6. Under the staff_TIMEENTRY__pastWeek table, select the ID_Staff field.
    7. Select Add.

      Note: You can also create this relationship within the graph itself: Under the STAFF table occurrence, select the ID field and click and drag the line to the ID_Staff field of the staff_TIMEENTRY__pastWeek table occurrence.
  1. Next, you will match the global date fields you created in Step 1 to the date of the time entries. This way your portal will only show time entries that have dates between the zg_Date_Start and zg_Date_End fields (the past week).
    1. You should still be in the Edit Relationship dialog from the previous step. If not, you can open it by double-clicking on the relationship line connecting the STAFF table occurrence to the staff_TIMEENTRY__pastWeek table occurrence.
    2. Select STAFF::zg_Date_End and staff_TIMEENTRY__pastWeek::Date_TimeEntry.
    3. Select the operator from the drop-down in the middle.
    4. Click Add.
    5. Select STAFF::zg_Date_Start and staff_TIMEENTRY__pastWeek::Date_TimeEntry.
    6. Select the operator from the drop-down in the middle.
    7. Click Add.
  1. Finally, you will add two more relationships to show related job and task data on time entry your portal. Create a new relationship between TimeEntry and Job:
    1. Add a new table occurrence to the relationships graph.
    2. Select the Job table and name the new occurrence “staff_timeentry_JOB”.
    3. Create a relationship between this new table occurrence and the staff_TIMEENTRY__pastWeek table occurrence by matching the ID_Job field in staff_TIMEENTRY__pastWeek to the ID field from staff_timeentry_JOB.
  1. Now, create another new relationship between TimeEntry and Task:
    1. Add a new table occurrence to the relationship graph.
    2. Select the Task table and name the new occurrence “staff_timeentry_TASK”.
    3. Create a relationship between this new table occurrence and the staff_TIMEENTRY__pastWeek table occurrence by matching the ID_Task field in staff_TIMEENTRY__pastWeek to the ID field from staff_timeentry_TASK.
  1. Position and size the new table occurrences so your relationship graph looks similar to this:

Step 3 - Add portal and fields

To show a list of all of the user’s time entries for the past week, add a portal to the iPad | Time Entry layout.

  1. In Layout mode, add a new portal (Insert > Portal or using the Portal tool in thge status toolbar) to the Body of the layout.
  2. In the Portal Setup dialog:
    1. Set the Show records from: option to staff_TIMEENTRY__pastWeek
    2. Set Show scroll bar: to When scrolling
    3. Set Number of rows: to 5
    4. Check Use alternate row state
  3. Next, in the Add Fields to Portal dialog, select the Date_TimeEntry, Time_Start, and Time_End fields and move them to Included fields. Click OK to complete the portal setup.
  4. In the Inspector, set the Autosizing option for the portal to anchor top, bottom, right and left so the portal will stretch both vertically and horizontally to fill the body of the layout.
  5. Within the portal, style the Date_TimeEntry field as txt Minimal.Black.Left, and the Time_Start and Time_End field as txt Minimal.Black.Right via the Styles tab in the Inspector.
  6. Format the time fields as “hh:mm” format by selecting the Time_Start field and navigate to the Data tab of the Inspector. Scroll down to Data Formatting:
    1. Set Format to hhmm.
    2. Set Separator to Symbol and :
    3. Select 12 hour.
    4. Set before noon to AM and afternoon to PM and select trailing from the drop-down.
    5. Set For hours to None and For minutes/seconds to Zero.
  1. Repeat step 6 for the Time_End field.
  2. Next, set the date to the format of “mm/dd”. Select the Date_TimeEntry field and go to the Data tab of the Inspector. Scroll down to Data Formatting:
    1. Set Format to Custom.
    2. In the first drop-down, select 12 to set month to the number format.
    3. In the drop-down to the right, select the / as the separator.
    4. In the drop-down below, select 25 to set the day to the number format.
    5. Set the remaining separator drop-downs to None.
    6. Set the remaining date drop-downs to None.
    7. Set both Leading character options to Zero.
  1. Now add the related job and task names to the portal:
    1. Add the staff_timeentry_JOB::Job_Name field to the portal (Insert > Field) and style it as txt Minimal.Black.Left.
    2. Add the staff_timeentry_TASK::Task_Name field to the portal (Insert > Field) and style it as txt Minimal.Gray.Left.
    3. Set the height of the portal to 347 pt via the Position tab in the Inspector.
    4. Arrange the fields in the portal similar to the image below:
  1. On the Position tab of the Inspector, adjust the Autosizing for the portal and each field in the portal to anchor as follows:
    1. Portal: top, left, bottom, right
    2. Job_Name: top, left and right
    3. Task_Name: top, left and right
    4. Date_TimeEntry: top and right
    5. Time_Start: top and right
    6. Time_End: top and right
  2. You do not want users to edit data in the portal. Select all of the fields in the portal. On the Data tab of the Inspector, scroll down to Behavior and under Field entry uncheck Browse mode.
  3. Finally, set the portal to sort so that the user will see the most time entries from oldest to newest:
    1. Open the Portal Setup dialog by double-clicking on the portal, or right-click on the portal and select Portal Setup…
    2. Check Sort portal records.
    3. Move Date_TimeEntry and Time_Start to the sort order.
    4. Set both fields to sort by Ascending order.

Step 4 - Add summary fields

Within the body of the layout, you will want to see a summary of the data shown in the portal (for instance, total hours worked for that week and total number of time entries submitted). To do this, you will create calculations as fields in the STAFF table.

  1. Open the Manage Database dialog. Navigate to the Fields tab and select the Staff table from the drop-down.
  2. The first calculation will be a sum of the total hours in the portal (since the portal is set to only show the previous week’s data, this will be the total hours worked in the past week). Create a new field called “z_Hours_ByDate”:
    1. Set the Type to Calculation.
    2. Click Create.
    3. In the subsequent dialog, add the following calculation:

      Let(
      [
         c = Round ( Sum ( staff_TIMEENTRY__pastWeek::Hours ) ; 2 )
      ] ;

         If ( c > 0 ; c ; 0 )
      )

    4. At the bottom of the dialog, be sure Calculation result is set to Number, and uncheck Do no evaluate if all referenced fields are empty.

      Note: This calculation uses a Let statement to set a variable, c, to the the sum of of the total hours and then rounds it 2 decimal places. If the value of c is greater than zero, the result will be that value, otherwise the value will be 0. If you do not use a Let statement, the result will be blank, and that will look odd on the layout. 

      Also, note: Unchecking Do no evaluate if all referenced fields are empty, allows this calculation to run even if there are no hours in that field.
  3. The second calculation will be a count of the number time entries that have been created during the past week. Create another new calculation field called “z_Time_Entries_ByDate”:
    1. Use the following formula:

      Count ( staff_TIMEENTRY__pastWeek::ID )

    2. Be sure the Calculation result is set to Number, and uncheck Do not evaluate if all referenced fields are empty.
  4. Back on the layout, go into Layout mode. If the “z_Hours_ByDate” and the “z_Time_Entries_ByDate” calculation fields were automatically added to your layout, delete them and their label.
  5. Insert z_Hours_ByDate as a Merge Field (Insert > Merge Field).
    1. Within the merge field, move the cursor to the beginning of the field and add the text, “Hours: “ to the field. The field should now look like Hours: <<z_Hours_ByDate>>.
    2. Style the field as label Right.
    3. Position the field above the portal on the right side.
    4. Adjust the Autosizing to anchor top and right.
  6. Insert z_Time_Entries_ByDate as another Merge Field.
    1. Add “Time Entries: “ to the beginning of this merge field.
    2. Style the field as label Left.
    3. Position the field above the portal on the left side.

      Note: You can also add merge fields by selecting the Text Tool from the status toolbar and typing “Hours: <<z_Hours_ByDate>>” in the text box.
  7. Add a merge field in the header that represents the date range of the data displayed in the portal.
    1. Using the Text Tool, add “<<zg_Date_Start>> - <<zg_Date_End>>” to the header, positioning it as shown below.
    2. Style it as label caps.Right.
    3. Adjust the Autosizing to anchor top and right.
  8. Finally, update the title to read, “Time Entries — Last 7 Days”.
  9. In Layout mode, your layout should look similar to this:

Step 5 - Review your work

  1. Using the iPad, open the file using FileMaker Go. The Time Entry detail layout should open and you should see a list of your time entries for the past week.

    NOTE: You may need to create sample data through the Desktop layouts to see data that fits the date range.
  2. As you tap through different names in the master-detail portal, you should see time time entries for the person you selected in the portal.