Build-On Tutorial:

Staff

Staff: Lesson 3

Event Staff Role

Lesson Overview

In this lesson, you will add features to assign staff to events. You will create a join table to allow for a many-to-many relationship between Staff and Event: one staff member can be assigned to many events and one event can have many staff members.

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

Step 1 - Create the table and fields and update the Relationships graph

Create a new join table named StaffRole. In this lesson, you will use this table to track event staff. In the next lesson, you will learn how to extend this table so that it can also be used for managing staff for sessions.

  1. Open the Manage Database dialog, duplicate the Template table, and name the new table “StaffRole”.
  2. Add the following fields to the StaffRole table:
    1. ID_Event (number)
    2. ID_Staff (number)
    3. Role (text)
    4. Role_Type (calculation formula: “Event” ; calculation result is Text)

      Note: You will modify the Role_Type calculation in the next lesson, where you will add functionality for tracking staff for sessions.
  1. Activate the Relationships tab.
  2. Locate the new StaffRole table occurrence. Select and duplicate it using Command-D(macOS) / Control-D (Windows).
  3. Rename the new table occurrence to “event_STAFFROLE_EventStaff”.
  1. Rename the original StaffRole table occurrence to “STAFFROLE”. This is done for consistency with the graph organization techniques used in the file.
  2. Use the color tool at the bottom of the graph to change the color of the STAFFROLE table occurrence to the same blue used elsewhere and move it to the bottom of the graph.
  1. Create a relationship between the EVENT table occurrence and the event_STAFFROLE_EventStaff table occurrence:
    1. Move the event_STAFFROLE_EventStaff table occurrence to the right of the EVENT table occurrence.
    2. Click on the ID field in EVENT and drag over to the ID_Event field in event_STAFFROLE_EventStaff.
  1. Double-click the line that connects the two table occurrences to open the Edit Relationship dialog. Click the option on the right side that reads Delete related records in this table when a record is deleted in the other table.

    This is called a "cascading delete" and means that if an Event record is deleted, any related StaffRole records will also be deleted.
  1. Position and style the new table occurrence as shown below.
  1. Use the Add a table button at the bottom left corner of the graph to create a new table occurrence of the Staff table, and name it event_staffrole_eventstaff_STAFF.
  1. Create a relationship between event_STAFFROLE_EventStaff::ID_Staff and event_staffrole_eventstaff_STAFF::ID. Do not set any cascading deletes in this relationship. Position and style the new table occurrence as shown below.
  1. Use the Add a table button at the bottom of the graph to create a new table occurrence based on the Event table, and name it staffrole_EVENT.
  2. Create a relationship between STAFFROLE::ID_Event and staffrole_EVENT::ID. Do not set any cascading deletes in this relationship. Position and style the new table occurrence as shown below.
  1. Add the following new calculation field to the Event table:
    1. Count_Event_Staff (calculation: Count ( event_STAFFROLE_EventStaff::ID ); Calculation result is Number; uncheck Do not evaluate if all referenced fields are empty).
  1. Close the Manage Database dialog to save your work.

Step 2 - Rename and organize utility layout

Organize and rename the newly created StaffRole layout using the Manage Layouts dialog (File > Manage > Layouts...)

  1. Locate the new STAFFROLE layout at the bottom of the list. Then use the Layout Setup dialog to rename it to “StaffRole Utility”.
  2. Back in the Manage Layouts dialog, move the StaffRole Utility layout into the Developer folder, and uncheck the Include in layout menus checkbox.

Step 3 - View event staff

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

  1. Using the Manage Layouts dialog, duplicate the Desktop > Event > Event Detail layout and rename it Event Detail - BK <current date> . It’s a good idea to make a dated backup of a layout before you edit it in case you need to revert back to it. Note: It can be helpful to keep your backup layouts organized and separated from your working layouts by creating a layout folder named “Backups”.
  2. Enter Layout mode and navigate to the Event Detail layout.
  3. Select and unlock the tab control, and use the Tab Control Setup dialog to add a new “Event Staff” tab after the Notes tab.
  1. Each tab panel has a blue line under the tab name to indicate the active tab. To add this to the new Event Staff tab:
    1. Click the Event Staff tab to make sure it is the active tab.
    2. Scroll to the right and notice a blue line object off the visible right edge of the layout.c.Duplicate the blue horizontal line and drag it under the Event Staff tab name
  1. Use the Portal tool from the status toolbar and draw a rectangle inside the new Event Staff tab.
  1. In the subsequent Portal Setup dialog:
    1. Temporarily set the Show records from: option to event_staffrole_eventstaff_STAFF
    2. Check Sort portal records and set to sort by Name_Last and Name_First
    3. Return to the Show records from: setting and change it to event_STAFFROLE_EventStaff. Note that this two-step process is necessary to configure the portal sort order to use fields from another “hop” away in the relationships graph.
    4. Check Allow vertical scrolling and set Show scroll bar: to When scrolling
    5. Set Number of rows: to 6
    6. Check Use alternate row state
    7. Click OK. In the subsequent Add Fields to Portal dialog, just 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 314 pt.
    2. Set its Left position to 272 pt and Top position to 272 pt.
    3. In the Autosizing area of the Position tab in the Inspector, select the 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. event_staffrole_eventstaff_STAFF::Name_First_Last
    2. event_STAFFROLE_EventStaff::Role
  2. Use the Inspector to modify the two fields as follows:
    1. Set the style to txt Minimal.Black.Left
    2. In the Behavior section of the Data tab, disable the Browse Mode option under Field Entry.
    3. Still in the Data tab, set the Placeholder text for the name and role fields to “StaffName” and “Role”, respectively.
    4. Resize and arrange the fields as shown below.
  1. Select Insert > Merge Field... to add the Count_Event_Staff field to the layout above the staff role portal.

    Note: You can also use the Text tool to add a merge field by simply typing “<<Count_Event_Staff>>”.
  2. Make the following formatting changes to the merge field:
    1. Double-click into it and add the text "Staff: “ before the field name.
    2. Set the style to label Left
    3. Position it above the portal, as shown below.
  1. Enter Browse mode to save and review your work.

Step 4 - Create card layout for adding and editing event staff

The portal you created is ideal for viewing an event’s staff. For adding and editing event staff, however, you will make a new card window layout.

  1. Open the Manage Layouts dialog and Duplicate the Blank > Blank | Desktop - Card layout. In the new layout:
    1. Set the Layout Name to “Staff Role | Card”.
    2. Set Show records from: to STAFFROLE.
    3. Set the Menu Set: to Minimal.

      Note: The Event Management Template file includes a custom menu set called "Minimal." There are cases when you do not want users to be able to use every menu function available to them, such as when they are in a card window for a specific step. The Minimal menu set restricts users to working only with the layout and found set they are presented with. Use this when you want to tightly control what a user is able to do.
  1. Move the new Staff Role | Card layout into the Staff folder.
  2. Enter Layout mode and navigate to the new layout.
  3. You will need to change the heights of the Header and Body layout parts. Using the Inspector, select the Header part handle (shown below) and set its Height to 112 pt.
  1. Select the buttons and offscreen text object at the bottom of the layout and set their Bottom position to 416 pt. Then select the Body part handle and reduce its Height to 320 pt.
  1. Edit the merge field at the top of the layout to say “<<$$CARD_ACTION>> Staff Role”.

    The $$ syntax indicates that $$CARD_ACTION is a global variable. This variable will contain either "New" or "Edit" and will be set by scripts you create later in this lesson. This way, you can use the same layout both for creating a new record and for editing an existing one.
  2. Add the following fields and labels to the layout and position them as shown.
    1. ID_Staff, with the label “Staff”
    2. Role, with the label “Role”
  1. Select the ID_Staff field, and in the Data tab of the Inspector, set the Control Style to Pop-up Menu. Then click the pencil icon next to the Values from: option to open the Manage Value Lists dialog.
  1. In the Manage Value Lists dialog, create a new value list named "Staff | Staff ID”. In the Edit Value List dialog:
    1. Select the Use values from field: option.
    2. In the resulting Specify Fields for Value List dialog, in the Use value from first field area, first choose the table STAFF, and then choose the field ID.
    3. Select the Also display values from second field option, and in the same table, choose the field Name_First_Last.
    4. Select the Show values only from second field option.
    5. Save the new value list and exit all dialogs to return to the layout.
  1. With the ID_Staff field still selected, enable the Override data formatting with value list option in the Data tab of the Inspector.
  1. Select the Role field and enable the Auto-complete using existing values option in the Data tab of the Inspector.
  2. Assign the following styles to the labels and fields:
    1. Set the labels to label Left.
    2. Assign ID_Staff the Default style.
    3. Assign Role the style txt Black.Left.
  3. Enter Browse mode to save and review your work.

Step 5 - Add a script to open the card window

There are two use cases for the new staff role card layout: creating a new staff role, and editing an existing one. Because these processes will be defined in separate scripts you will write later in this lesson, it will be helpful to create a subscript to handle opening the card window and specifying the layout so that this logic is only defined once.

  1. Enter Layout mode and open the Script Workspace, duplicate the Template script, rename it “Open Staff Role Card”, and move it to the STAFF folder.
  2. Modify the script so that it checks to make sure the current window is not a card window (you cannot open a card window on top of an existing one) and then opens the Staff Role | Card layout as a card window.

    NOTE: For detail on the New Window script step, see Step 3 below.

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

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

# open card window
New Window [ Style: Card ; Name: "Staff Role” ; Using layout: “Staff Role | Card” (STAFFROLE) ]


Exit Script [ Text Result: $null ]

  1. For the New Window script step, double-click on the script step or click the gear icon to open the New Window Options dialog:
    1. Set Window Style to Card.
    2. Set Window Name to “Staff Role”.
    3. Set Layout to Staff Role | Card.
    4. Under Window Options, uncheck Close
  1. Save and close the script.

Step 6 - Add a script to add a new staff role

Next, you will create a script for adding a new staff role record. Later in this lesson, you will attach this script to a button on the Event Staff tab of the Event Detail layout.

The script begins by using the #Assign custom function to parse any script parameters and test to make sure they are are valid. It then proceeds to open the card window, set the $$CARD_ACTION variable, and create a new staff role record related to the specified event.

  1. Create a new script by duplicating the Template Script. Name the new script “Add New Staff Role ( id_event )” and move it into the STAFF folder.
  2. The script name indicates an expected parameter named id_event. Modify the Parameters line as follows to document the parameter:

# Parameters:      Specify using # function:
          $id_event (required)

  1. Modify the script by adding the highlighted steps below.

    NOTE: See below for further instructions on the Show Custom Dialog step.

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_event ) ]
     # invalid parameter(s) passed
     Show Custom Dialog [ “Error" ; "Invalid parameter(s) passed to script: " & Get ( ScriptName ) ]
     Exit Script [ Text Result: "error" ]
End If

# set card action
Set Variable [ $$CARD_ACTION ; Value: "New" ]

# open card window
Perform Script [ Specified: From list ; “Open Staff Role Card” ; Parameter: ]

# create record
New Record/Request
Set Field [ STAFFROLE::ID_Event ; $id_event ]
Commit Records/Requests [ With dialog: Off
]

Exit Script [ Text Result: $null ]

  1. For the Show Custom Dialog script step, only the Default Button should be specified, with the button text “OK”. The Button 2 value should be removed.
  1. Save and close the script.

Step 7 - Add a script to view an existing staff role

The final script you need is to edit an existing staff role. This script is similar in many ways to the one you wrote for adding a new staff role. It begins by using the #Assign custom function to parse any script parameters and test to make sure they are are valid. As in the other script, it then proceeds to open the card window and set the $$CARD_ACTION variable. Instead of creating a new record, however, this script then finds an existing staff role.

  1. Create a new script by duplicating the Template Script. Name the new script “Find Staff Role by ID ( id_staffrole )” and move it into the Staff folder.
  2. Modify the Parameters comment:

# Parameters:         Specify using # function:
       $id_staffrole (required)

  1. Modify the script by adding the highlighted steps below.

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_staffrole ) ]
     # invalid parameter(s) passed
     Show Custom Dialog [ “Error" ; "Invalid parameter(s) passed to script: " & Get ( ScriptName ) ]
     Exit Script [ Text Result: "error" ]
End If

# set card action
Set Variable [ $$CARD_ACTION ; Value: "Edit" ]

# open card window
Perform Script [ Specified: From list ; “Open Staff Role Card” ; Parameter: ]

# find record
Enter Find Mode [ Pause: Off ]
Set Field [ STAFFROLE::ID; "==" & $id_staffrole ]
Set Error Capture [ On ]
Perform Find [ ]
Set Variable [ $error ; Value: Get ( LastError ) ]
Set Error Capture [ Off ]
If [ $error ]
     Perform Script [ Specified: From list ; “Close Window” ; Parameter: ]
     Show Custom Dialog [ “Error" ; If ( $error = 401 ; "The staff role could not be found." ; "FileMaker error: " & $error & "." ) & " Unable to perform this action.” ]
     Exit Script [ Text Result: "error" ]
End If


Exit Script [ Text Result: $null ]

  1. Save and close the script.

Step 8 - Add buttons

With all of the scripts now in place, your final task is to make buttons to trigger the scripts. On the Event Staff tab of the Event Detail layout, you will create buttons for adding staff role records to track event staff. On the card window, you will review the existing buttons and add a new one.

  1. Enter Layout mode and navigate to the Event Staff tab on the Event Detail layout.
  2. Create a new button beneath the staff role portal. In the Button Setup dialog:
    1. Select the Display only a label button, and set the label to “Add Staff”.
    2. In the Action dropdown, choose Perform Script, and in the Specify Script dialog, choose the Add New Staff Role ( id_event ) script.
    3. In the Optional Script Parameter area, set the parameter to # ( “id_event" ; EVENT::ID )
  1. Using the Inspector, set left and bottom anchors on the button. Resize the button to a Width of 176 pt and a Height of 44 pt. Set the Left position to 272 pt and the Top position to 600 pt.
  1. For editing a staff role, you will place an invisible button over the entire portal row. 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, but 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" ; event_STAFFROLE_EventStaff::ID )
  1. Use the Inspector to finish configuring the button:
    1. Assign it the style btn Row.Hover. Note that this style makes the button invisible (including while in Layout mode, though you can still select it and see its boundaries when it’s the selected object).
    2. Set the hide condition to the formula _in_mode_Find, and check the Apply in Find Mode box. This makes it so the button does not appear in Find mode, and the user can search in fields in the portal without activating the button.
    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: 273 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 (shown below) to place the button behind the fields in the portal row.
  1. Your completed button should look similar to the image below.
  1. Enter Browse mode to save and review your work.
  2. Enter Layout mode and navigate to the Staff Role | Card layout.

    At the bottom of this layout, there are two sets of stacked buttons. If you temporarily move or hide the Cancel and Create buttons, you will see Delete and Close buttons underneath them (see below). Hide conditions have been specified for these objects so the first pair displays when $$CARD_ACTION is "New" and the second pair when it is not.

    The Cancel and Delete buttons are specified to call the Delete Record ( msg ) script. These do not require any modification by you.
  1. Create a new button in the Header part:
    1. Select the Display only a label button
    2. Set the label to: Event: <<staffrole_EVENT::Event_Name>>. Note that this includes a merge field as part of the button label.
    3. Leave the Action set to Do Nothing. You will modify this in a later lesson.
  1. Use the Inspector to further modify the button:
    1. Set the style to btn Minimal.Blue.Previous.
    2. Activate the Text tool. Click on the button and select the button label text Event:. Using the Appearance tab in the Inspector, change the text color to gray.
    3. Your completed button should look similar to the image below.
  1. Enter Browse mode to save and review your work.

Step 9 - Review your work

    • Go to the Staff Detail layout and create a few Staff records for testing.
    • Now go to the new Event Staff tab on the Event Detail layout.
    • Click the Add Staff button. You should see a card window with the heading New Staff Role. Click on the Staff field and choose a Staff person from the list, and then enter a value in the Role field. Click the Create button. You should see the staff person in the portal on the Event Staff tab.
    • Click on the Staff person in the portal. You should see a card window with the heading Edit Staff Role. Try changing values and test the Delete and Close buttons.