Build-On Tutorial:

Staff

Staff: Lesson 5

Putting it All Together

Lesson Overview

In previous lessons, you created functionality for adding staff roles for events and sessions, but that information is currently only visible from the event and session contexts. In the final lesson for the Staff Build-On, you will modify the Staff Detail layout to display event and session staff role information. You will also create buttons on the Staff Role | Card layout so that a user can navigate from an event or session—through the card window—to a related staff record, and vice versa.

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

Step 1 - Modify tables, fields, and relationships

Your first step is to create fields and relationships so that you can display related event and session staff data from the Staff context.

  1. Open the Manage Database dialog and add the following new fields to the Staff table: 
    1. zz_Flag_Session (calculation: "Session"; Calculation result is Text )
    2. zz_Flag_Event (calculation: "Event"; Calculation result is Text )
  2. Activate the Relationships tab. 
  3. Create a new table occurrence of the StaffRole table, name it staff_STAFFROLE_EventStaff, and position it near the STAFF table occurrence. 
  4. Create a relationship between STAFF and staff_STAFFROLE_EventStaff based on: 
    1. STAFF::ID = staff_STAFFROLE_EventStaff::ID_Staff
    2. STAFF::zz_Flag_Event = staff_STAFFROLE_EventStaff::Role_Type
    3. Enable cascade deletion for the table on the right.
  1. Create a new table occurrence of the Event table, name it staff_staffrole_eventstaff_EVENT, and position it near the the staff_STAFFROLE_EventStaff table occurrence.
  2. Create a relationship between staff_STAFFROLE_EventStaff::ID_Event and staff_staffrole_eventstaff_EVENT::ID. Do not enable any cascading deletes. 
  3. Position and style the new table occurrences and relationships as shown below.
  1. Create a new table occurrence based on the StaffRole table, name it staff_STAFFROLE_SessionStaff, and position it near the STAFF table occurrence.
  2. Create a relationship between STAFF and staff_STAFFROLE_SessionStaff based on: 
    1. STAFF::ID = staff_STAFFROLE_SessionStaff::ID_Staff
    2. STAFF::zz_Flag_Session = staff_STAFFROLE_SessionStaff::Role_Type
    3. Enable cascade deletion for the table on the right.
  1. Create a new table occurrence of the Session table, name it staff_staffrole_sessionstaff_SESSION, and position it near the staff_STAFFROLE_SessionStaff table occurrence. 
  2. Create a relationship between staff_STAFFROLE_SessionStaff::ID_Session and staff_staffrole_sessionstaff_SESSION::ID. Do not enable any cascading deletes.
  3. Position and style the new table occurrences as shown below.
  1. Create a new table occurrence of the Staff table, name it staffrole_STAFF, and position it near the STAFFROLE table occurrence.
  2. Create a relationship between STAFFROLE::ID_Staff and staffrole_STAFF::ID. Do not enable any cascading deletes.
  3. Position and style the new table occurrence as shown below.
  1. Close the Manage Database dialog to save your work.

Step 2 - View event and session staff roles on Staff Detail layout

Add a new tab and portal to the Staff Detail layout that shows related event staff roles.

  1. Make a backup of the Staff Detail layout in case you need to revert back to it.
  2. Enter Layout mode and navigate to the Staff Detail layout. 
  3. Modify the tab control to add two new tabs named "Event History" and "Session History”
  1. Copy the blue line from offscreen to both the Event History tab and the Session History tab. Increase the line’s width to span the width of the tab names as shown.
  1. Activate the Event History tab and create a large portal inside it. In the subsequent Portal Setup dialog: 
    1. Temporarily set Show records from: to staff_staffrole_eventstaff_EVENT.
    2. Check Sort portal records and set to sort by staff_staffrole_eventstaff_EVENT::Date_Start in descending order.
    3. Change Show records from: to staff_STAFFROLE_EventStaff.
    4. Check Allow vertical scrolling and set Show scroll bar: to When scrolling
    5. Set Number of rows: to 7
    6. Check Use alternate row state
    7. Click OK. In the subsequent Add Fields to Portal dialog, click OK for now; you will add fields shortly.
  1. Select the portal object and use the Inspector to modify it as follows: 
    1. Set its Width to 736 pt and Height to 366 pt.
    2. Set its Left position to 272 pt and Top position to 326 pt.
    3. Set top, bottom, left, and right anchors so the portal will stretch both vertically and horizontally to fill the space available as the window is resized.
  1. Use the Fields tab to add the following fields to the portal. Do not include field labels.
    1. staff_staffrole_eventstaff_EVENT::Event_Name
    2. staff_staffrole_eventstaff_EVENT::Date_Start
    3. staff_STAFFROLE_EventStaff::Role
  2. Use the Inspector to modify the three fields as follows:
    1. Set top, left, and right anchors on Event_Name. Set top and right anchors on Date_Start and Role.
    2. For all fields, set the style to txt Minimal.Black.Left.
    3. For all fields, in the Behavior section of the Data tab, disable the Browse Mode option under Field Entry.
    4. Still in the Data tab, set the Placeholder text for Event_Name and Date_Start to “Event Name” and “Date”, respectively. Do not add placeholder text for Role.
    5. Select the Date_Start field and in the Data tab, scroll down to Data Formatting and for Format select 12/25/2014. Set the Leading Character to Zero for both day and month numbers.
    6. Resize and arrange the fields as shown below.
  1. Add a text label in front of the Role field with the text “Role:”.
    1. Set top and right anchors.
    2. Set its style to: Label inPortal.Right
    3. Set its hide condition to the formula: IsEmpty ( staff_STAFFROLE_EventStaff::Role )
    4. Position it as shown below.
  1. For editing an event staff role, you will place an invisible button over the entire portal row, similar to how you set up the event staff portal on the Event Detail layout.

    Use the Button tool to draw a button over the entire first portal row. In the Button Setup dialog:
    1. Select the Display only a label button and leave the name blank.
    2. Set the Action to perform the script: Find Staff Role by ID ( id_staffrole )
    3. For the script parameter, specify the formula: # ( “id_staffrole" ; staff_STAFFROLE_EventStaff::ID )
  1. Use the Inspector to finish configuring the button: 
    1. Assign it the style btn Row.Hover.
    2. Set the hide condition to the formula _in_mode_Find, and check the Apply in Find Mode box.
    3. Set the button Width to 734 pt wide and Height to 50 pt. Then position the button as needed so that it is situated entirely within the portal row (Left: 273 pt, Top: 327 pt).
    4. Set top, left, and right anchors for the button.
    5. In the Arrange & Align section of the Position tab, click the Send to back button to place the button behind the fields in the portal row.
    6. Your completed button should look similar to the image below.
  1. You will now set up a nearly identical portal on the Session History tab. To start, copy the staff_STAFFROLE_EventStaff portal from the Event History tab and paste it on the Session History tab. Set its Left and Top positions the same as the original portal (272 pt and 326 pt, respectively). 
  2. Open the Portal Setup Dialog and modify the portal as follows:
    1. Temporarily set Show records from: to staff_staffrole_sessionstaff_SESSION.
    2. Check Sort portal records and set to sort by staff_staffrole_sessionstaff_SESSION::Date in descending order.
    3. Change Show records from: to staff_STAFFROLE_SessionStaff
  3. Modify the fields in the portal to the following: 
    1. staff_staffrole_sessionstaff_SESSION::Session_Name
    2. staff_staffrole_sessionstaff_SESSION::Date
    3. staff_STAFFROLE_SessionStaff::Role 
  4. Set the Placeholder text for Session_Name to “Session Name”. 
  5. Set the hide condition for the Role: text object to the following calculation: IsEmpty ( staff_STAFFROLE_SessionStaff::Role ) 
  6. Change the parameter for the portal row button to # ( "id_staffrole" ; staff_STAFFROLE_SessionStaff::ID ).

Step 3 - Script for finding a Staff record

Next, you will will create a script to find an existing Staff record. In a later step, you will use this script to create a way to navigate from Staff Role | Card layout to the related Staff record.

This script is very similar to the existing script Find Attendee by ID ( id_attendee ). You will duplicate this script and modify the necessary steps so that it handles searching for a Staff record rather than an Attendee record.

  1. Open the Script Workspace and duplicate the Find Attendee by ID ( id_attendee ) script. Name the new script “Find Staff by ID ( id_staff )” and move it into the STAFF folder.
  2. Modify the new script so that it searches for a Staff record. The steps requiring updates are highlighted below.

    NOTE: For additional detail on the Set Variable steps, see below.

#===============================================================
# Purpose:           Goes to the staff detail layout and finds the specified staff record

# Context:           No dependencies
#--------------------------------------------------------------
# Parameters:      Specify using # function:
          $id_staff (required, unless $gtrr_flag included)
          $gtrr_flag (optional) = True; include this when performing a gtrr action to gracefully handle resulting found set and no record found
#--------------------------------------------------------------
# Returns:           "error" if problem encountered
#--------------------------------------------------------------
# Notes:              None
#===============================================================

Allow User Abort [ Off ]
Set Error Capture [ Off ]
Enter Browse Mode [ Pause: Off ]
Freeze Window

# load parameter(s)
Set Variable [ $parameter_parsing_ok ; Value: #Assign ( Get ( ScriptParameter ) ) ]
If [ $parameter_parsing_ok = False ]
     # parameters could not be parsed, exit this script and report back
     Exit Script [ Text Result: "error" ]
Else If [ IsEmpty ( $id_staff ) and $gtrr_flag ]
     # no related record; just exit  
     Exit Script [ Text Result: $null ]
Else If [ IsEmpty ( $id_staff ) ]
     # invalid parameter(s) passed
     Show Custom Dialog [ “Error" ; "Invalid parameter(s) passed to script: " & Get ( ScriptName ) ]
     Exit Script [ Text Result: "error" ]
End If
# verify record exists before leaving current layout or window
Set Variable [ $sql ; Value:
     Let ( [
          ~sql_before =
                 "SELECT Count(*) " &
                 "FROM ~Staff " &
                 "WHERE ~ID_Staff = ?" ;

          ~sql_after = Substitute ( ~sql_before ;
                 [ "~Staff" ; _FieldRef_getTO ( STAFF::ID ; 1 ) ] ;
                 [ "~ID_Staff" ; _FieldRef_getField ( STAFF::ID ; 1 ) ]
                 )
] ;

          ~sql_after
)
]
Set Variable [ $record_exists ; Value:
     Let ( [
          ~result = ExecuteSQL ( $sql ; "" ; "" ; $id_staff ) ;
          ~num = GetAsNumber ( ~result )
   ] ;
          ~num > 0
)
]

If [ not $record_exists ]
          Show Custom Dialog [ “Error" ; "The staff person could not be found.” ]
          Exit Script [ Text Result: "error" ]
End If
# layout nav
Perform Script [ Specified: From list ; “Go to Staff Layout ( view )” ; Parameter: ]

# find record
Enter Find Mode [ Pause: Off ]
Set Field [ STAFF::ID ; "==" & $id_staff ]
Set Error Capture [ On ]
Perform Find [ ]
Set Variable [ $error ; Value:Get ( LastError ) ]
Set Error Capture [ Off ]
If [ $error ]
          # we shouldn't get here since we already made sure record exists, but handle errors just to be safe
          Show Custom Dialog [ “Error" ; If ( $error = 401 ; "The staff person could not be found." ; "FileMaker error: " & $error & "." ) & " Unable to perform this action.” ]
          Exit Script [ Text Result: "error" ]
End If
# if gtrr, stay on found record but show all
If [ $gtrr_flag ]
          Show All Records
End If

Exit Script [ Text Result: $null ]

  1. In the Set Variable script steps, an SQL query is defined and performed using the ExecuteSQL function. This query determines whether a Staff record matching the supplied $id_staff exists. The script goes on to close the card window and find and navigate to the specified Staff record. By determining whether the Staff record exists before closing the card window, the script allows the user to remain on the card window in the event of an error.

    In the first Set Variable step, the $sql variable is set to the text of the SQL query itself. The query is defined to return the count of Staff records with the specified ID. The calculation which defines the query uses a set of custom functions added to your file that allow the table and field references to dynamically reflect any name changes. Setting the query to a variable before executing it allows for easier troubleshooting using the Script Debugger. Set the Value of $sql to the following calculation:

Let ( [
          ~sql_before =
                    "SELECT Count(*) " &
                    "FROM ~Staff " &
                    "WHERE ~ID_Staff = ?" ;

           ~sql_after = Substitute ( ~sql_before ;
                    [ "~Staff" ; _FieldRef_getTO ( STAFF::ID ; 1 ) ] ;
                    [ "~ID_Staff" ; _FieldRef_getField ( STAFF::ID ; 1 ) ]
                    )
] ;
           ~sql_after
)

  1. In the second Set Variable step which defines the variable $record_exists, the SQL query is executed. The result is then converted into a Boolean (True/False) value indicating whether a matching Staff record exists. Set the Value of $record_exists to the following calculation:

Let ( [
          ~result = ExecuteSQL ( $sql ; "" ; "" ; $id_staff ) ;
          ~num = GetAsNumber ( ~result )
] ;
          ~num > 0
)

  1. Save and close the script.

Step 4 - Script for validating and saving a staff role

For the purpose of this lesson, imagine that when a user is done adding or editing a staff role, their entry needs to pass certain validation rules before they can proceed. This will provide you with a good example of how to define and implement business rules in your apps. The validation rules you will enforce are as follows:

ID_Staff is a required field 

• A staff person cannot have more than one role for the same session or event. In other words, the system should not allow duplicates on ID_Staff and ID_Event (for event staff roles) or ID_Staff and ID_Session (for session staff roles).

  1. Open the Manage Database dialog and activate the Relationships tab. 
  2. Create a new table occurrence based on the STAFFROLE table named “staffrole_SELF_DuplicateEventStaff". Position it near the STAFFROLE table occurrence. 
  3. Create a relationship between STAFFROLE and staffrole_SELF_DuplicateEventStaff based on matching ID_Staff, ID_Event, Role_Type, and non-matching ID. Match the corresponding fields from each table occurrence, as shown below. Do not enable any cascading deletes.

    The purpose of including the non-matching ID is that so a record will not find itself as a duplicate.
  1. Create another new table occurrence of the STAFFROLE table named “staffrole_SELF_DuplicateSessionStaff". Position it near the STAFFROLE table occurrence.
  2. Create a relationship between STAFFROLE and staffrole_SELF_DuplicateSessionStaff based on matching ID_Staff, ID_Session, Role_Type, and non-matching ID.
  1. Position and style the new table occurrences as shown below.
  1. Open the Script Workspace and duplicate the Template Script. Name the new script "Validate and Save Staff Role” and move it into the STAFF script folder. 
  2. Writing scripts often requires an iterative approach since the required logic can be complicated. You will now build the script in parts.

    Begin the script by requiring StaffRole layout context with a record showing. Note that each Show Custom Dialog script step should only show the Default Button, labeled “OK”.

Allow User Abort [ Off ]
Set Error Capture [ Off ]
Enter Browse Mode [ Pause: Off ]
Freeze Window

# context requirements
If [ Get ( LayoutTableName ) ≠ "STAFFROLE" ]
   # required table context
   Show Custom Dialog [ “Alert" ; "This script can only be run from a Staff Role screen.” ]
   Exit Script [ Text Result: "error" ]

Else If [ Get ( FoundCount ) = 0 or IsEmpty ( STAFFROLE::ID ) ]
   # require record showing
   Show Custom Dialog [ “Alert" ; "This script can only be run with a Staff Role record displayed.” ]
   Exit Script [ Text Result: "error" ]

  1. Next, evaluate the current StaffRole record against the validation rules: ID_Staff is required, and duplicate event staff or session staff are not allowed. You will use the relationships you created above to check for duplicate records. For now, only handle what happens when the record fails validation.

    The Show Custom Dialog step should only have a single button named “OK”.

    NOTE: For more detail on the Set Variable steps which set the variable $error_msg, see step 10 below.

# validate data If [
IsEmpty ( STAFFROLE::ID_Staff ) ]
   # staff must be entered
   Set Variable [ $error_msg ; Value: "Staff is required." ]

Else If [ STAFFROLE::Role_Type = "Event" and not IsEmpty
( staffrole_SELF_DuplicateEventStaff::ID ) ]

   # staff role already exists for this event and staff
   Set Variable [ $error_msg ; Value: staffrole_STAFF::Name_First_Last & " already has a staff role entered for this event. Either select a different person, or " & If ( $$CARD_ACTION = "new" ; "choose Cancel." ; "delete this staff role." ) ]

Else If [ STAFFROLE::Role_Type = "Session" and not IsEmpty
( staffrole_SELF_DuplicateSessionStaff::ID ) ]

   # staff role already exists for this session and staff
   Set Variable [ $error_msg ; Value: staffrole_STAFF::Name_First_Last & " already has a staff role entered for this session. Either select a different person, or " & If ( $$CARD_ACTION = "new" ; "choose Cancel." ; "delete this staff role." ) ]

End If

# handle validation
If [ not IsEmpty ( $error_msg ) ]
# if validation error, display message and remain in card window
   Show Custom Dialog [ “Alert" ; $error_msg ]
   Exit Script [ Text Result: $null ]
End If

  1. The three Set Variable steps are used to set an error message if any of the validation rules are not met. The latter two of these contain language that depends on whether the user is creating a new StaffRole record or editing an existing one, indicated by the global variable $$CARD_ACTION. The calculated error message for a duplicate event and staff is as follows:

staffrole_STAFF::Name_First_Last & " already has a staff role entered for this event. Either select a different person, or " &

If ( $$CARD_ACTION = "new" ;
          "choose Cancel." ; "
          delete this staff role."
)

  1. The calculation for a duplicate session and staff is similar; replace the word “event” with “session”.
  2. Now you will add logic that handles what happens when the StaffRole record passes the validation rules. In order to do that that, you need to identify all the situations in which this script will be run.

    Generally, when a user is creating or editing a StaffRole record, she will click the Create or Close button (respectively) at the bottom of the Staff Role | Card layout.

    However, now that I there are StaffRole portals on the Event Detail, Session Detail, and Staff Detail layouts, all of which which have buttons to open the Staff Role | Card layout, it will be helpful to add navigation buttons on the card layout to enable clicking through from an Event or Session to a related Staff record, or from a Staff record to a related Event or Session. This makes for three additional use cases for exiting the card layout: going to a related event, going to a related session, and going to a related staff record. In order to enforce the validation rules, all of these possible exit actions should also invoke this validation script.

    To achieve all of this, modify the script to accept a parameter named exit_action. This parameter may have four possible values: “close” (to simply close the card window), “event” (to go to the related event), “session” (to go to the related session), and “staff” (to go to the related staff record). Based on the parameter, the script should take the specified action after enforcing the validation rules.

    Start by modifying the Parameters comment to document the parameter, and then add steps to parse the parameters. The steps to add are highlighted below.

#===============================================================
# Purpose:           Validates the current staff role record in the card window, and exits with the specified action
# Context:           StaffRole table with record showing (required)
#--------------------------------------------------------------
# Parameters:            Specify using # function:
   $exit_action (optional) =
      close: just close the card window (default, if not specified)
      staff: gtrr staff
      event: gtrr event
      session: gtrr session
#--------------------------------------------------------------
# Returns:            "error" if problem encountered
#--------------------------------------------------------------
# Notes:            All buttons on the staff role card window run this script so that the record is validated before any exit action is taken
#===============================================================

Allow User Abort [ Off ]
Set Error Capture [ Off ]
Enter Browse Mode [ Pause: Off ]
Freeze Window

# load parameter(s)
Set Variable [ $parameter_parsing_ok ; Value: #Assign ( Get ( ScriptParameter ) ) ]
If [ $parameter_parsing_ok = False ]
   # parameters could not be parsed, exit this script and report back
   Exit Script [ Text Result: "error" ]
Else If [ not IsEmpty ( $exit_action ) and $exit_action ≠ "close" and $exit_action ≠ "staff" and $exit_action ≠ "event" and $exit_action ≠ "session" ]
   # invalid parameter(s) passed
   Show Custom Dialog [ “Error" ; "Invalid parameter(s) passed to script: " & Get ( ScriptName ) ]
   Exit Script [ Text Result: "error" ]
End If

  1. Finally, add script steps after the validation portion of the script to take the specified exit action.

    Note that the first three Perform Script steps below include two script parameters each: the appropriate record ID, as well as a parameter named gtrr_flag with the Boolean value True. The gtrr_flag parameter tells the “Find by ID” subscripts to take additional action to simulate the behavior of the Go to Related Record script step. (Go to Related Record cannot be used to go from one window to a related record in another existing window—or in this case, from the Staff Role | Card window to the main app window underneath.)

# passed validation, now handle the exit action
If [ $exit_action = "staff" ]
   # go to related staff
   Perform Script [ Specified: From list ; “Find Staff by ID ( id_staff )” ;
Parameter: # ( "id_staff" ; STAFFROLE::ID_Staff ) & # ( "gtrr_flag" ; True ) ]

Else If [ $exit_action = "event" ]
   # go to related event
   Perform Script [ Specified: From list ; “Find Event by ID ( id_event )”; Parameter: # ( "id_event" ; STAFFROLE::ID_Event ) & # ( "gtrr_flag" ; True ) ]

Else If [ $exit_action = "session" ]
   # go to related session
   Perform Script [ Specified: From list ; “Find Session by ID ( id_session )”; Parameter: # ( "id_session" ; STAFFROLE::ID_Session ) & # ( "gtrr_flag" ; True ) ]

Else
   # close card window
   If [ Get ( WindowStyle ) = 3 ]
      Perform Script [ Specified: From list ; “Close Window” ; Parameter: ]
   End If

End If

Exit Script [ Text Result: $null ]

  1. Your final script should look as follows.
  1. Save and close the script.

Step 5 - Modify buttons on the card layout

With the validation script now in place, your final task is to modify existing and add new buttons on the Staff Role | Card layout to handle validating the record and take all four possible exit actions: close the window, go to related event, go to related session, and go to related staff.

  1. Enter Layout mode and navigate to the Staff Role | Card layout. 
  2. Modify the button in the header labeled Event: <<staffrole_EVENT::Event_Name>>
    1. Configure it to perform the script Validate and Save Staff Role ( exit_action ).
    2. Set the script parameter to the following formula: # ( “exit_action” ; “event” ).
  1. Modify the button in the header labeled Session<<staffrole_SESSION::Session_Name>>.
    1. Configure it to perform the script: Validate and Save Staff Role ( exit_action ).
    2. Set the script parameter to the following formula: # ( “exit_action” ; “session” ).
  2. Duplicate one of the buttons and modify it as follows:
    1. Set the label to “Staff: <<staffrole_STAFF::Name_First_Last>>”.
    2. Keep the existing script, but set the script parameter to the following formula: # ( “exit_action” ; “staff” ).
  3. Use the Inspector to further modify the new button:
    1. Using the Text tool, select only the “Staff:” text in the button label. Use the Appearance tab of the Inspector to change the text color to gray.
    2. Assign the button the hide condition: IsEmpty ( STAFFROLE::ID_Staff )
    3. Position the button under the event and session buttons, as shown below.
  1. Modify the Create button to call the Validate and Save Staff Role ( exit_action ) script. You do not need to specify a parameter, since you wrote the script to take the default exit action of “close”. 
  2. Temporarily move the Create button to reveal the Close button. Modify the Close button to run the validation script as well. Then move the Create button back to its original position.
  1. Enter Browse mode to save and review your work.

Step 6 - Review your work

Congratulations! You have completed the Staff Build-On.

  1. Go to the Event Staff tab on the Event Detail layout. Create an event staff record for multiple events, always choosing the same Staff record for each.
  2. Go to the Session Staff tab on the Session Detail layout. Create several session staff records for several sessions, again choosing the same Staff record for each.
  3. Now go the Staff Detail layout, find the staff record you created several event staff records for, and go to the Event History tab. You should see a list of Events for that Staff person. Click on one of the Events. You should see the Edit Staff Role card window. Click the Event button in the header. You should now be viewing that Event on the Event Detail layout.
  4. Click the Event Staff tab and you should see a list of Staff who are assigned to that Event. Click on one of the event staff rows and you should see the Edit Staff Role card window. Click the Staff button in the header. You should now see that Staff record on the Staff Detail layout.
  5. Repeat these test for the Session History tab on the Staff Detail layout and the Session Staff tab on the Session Detail layout.
  6. Go to the Event Staff tab on the Event Detail layout. Start creating an event staff record, but leave the Staff field blank and try to save it.
  7. Still on the Event Staff tab on the Event Detail layout, find an event with an event staff record. Try creating a new event staff record for that same event and staff and see what happens.