Build-On Tutorial:

Staff

Staff: Lesson 4

Session Staff Role

Lesson Overview

In the previous lesson you built a StaffRole table and functionality for tracking event staff. In this lesson, you will extend this functionality to track session staff. Many of the steps in this lesson will feel very familiar.

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

Step 1 - Modify the Staff Role table and update the relationships graph

With a few modifications, the same StaffRole table you created before can be used to manage session staff. This also requires adding a few new table occurrences and relationships.

  1. Open the Manage Database dialog and make the following changes to the StaffRole table:
    1. Add a new field: ID_Session (number)
    2. Modify an existing field: Role_Type; set the calculation formula to:

Case (
          not IsEmpty ( ID_Session ) ; “Session” ;
          not IsEmpty ( ID_Event ) ; “Event”
)

  1. Activate the the Relationships tab.
  2. Use the Add a table button at the bottom left corner of the graph to create a new table occurrence of the StaffRole table, name it session_STAFFROLE_SessionStaff, and move it to the right of the SESSION table occurrence.
  3. Click on the upper right corner of the SESSION table occurrence to reveal its full list of fields.
  1. Create a relationship between SESSION::ID and session_STAFFROLE_SessionStaff::ID_Session by clicking on the ID field in SESSION and dragging over to the ID_Session field in session_STAFFROLE_SessionStaff.
  2. In the Edit Relationship dialog, enable cascading deletes for the session_STAFFROLE_SessionStaff table occurrence on the right. This means that when a Session record is deleted, any related StaffRole records will be deleted.
  1. Position and style the new table occurrence as shown below.
  1. Create a new table occurrence of the Staff table, and name it session_staffrole_sessionstaff_STAFF.
  2. Create a relationship between session_STAFFROLE_SessionStaff::ID_Staff and session_staffrole_sessionstaff_STAFF::ID. Do not set any cascading deletes in this relationship. Position and style the new table occurrence as shown below.
  1. Create a new table occurrence of the Session table, and name it staffrole_SESSION.
  2. Create a relationship between STAFFROLE::ID_Session and staffrole_SESSION::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 Session table:
    1. Count_Session_Staff(calculation: Count ( session_STAFFROLE_SessionStaff::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 - View session staff

Add a new tab and portal to the Session Detail layout that shows related session staff.

  1. Using the Manage Layouts dialog, duplicate the Desktop > Session > Session Detail layout and rename it Session 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.

    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 Session Detail layout.
  3. Select and unlock the tab control, and use the Tab Control Setup dialog to add a new “Session 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 Session Staff tab:
    1. Click the Session 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.
    3. Duplicate the blue horizontal line and drag it under the Session Staff tab name. Increase its Width to 114 pt so that it is as wide as the tab name.
  1. Navigate to the Event Staff tab on the Event Detail layout and copy the event_STAFFROLE_EventStaff portal to the clipboard.
  2. Navigate back to the Session Staff tab on the Session Detail layout and paste the portal. Set the portal’s Left position to 272 pt and its Top position to 272 pt.
  1. Open the Portal Setup Dialog to modify the portal as follows:
    1. Temporarily set Show records from: to session_staffrole_sessionstaff_STAFF
    2. Open the Sort portal records dialog and remove the existing fields from the Sort Order. Then add the following fields to the sort order, all in ascending order: Name_Last and Name_First.
    3. Return to the Show records from: setting and change it to session_STAFFROLE_SessionStaff. Remember from the previous lesson that this two-step process is necessary to configure the portal sort order to use fields from another “hop” away in the relationships graph.
  1. Update the fields in the portal so they are based on table occurrences related to the current SESSION context:
    1. Name field: session_staffrole_sessionstaff_STAFF::Name_First_Last
    2. Role field: session_STAFFROLE_SessionStaff::Role
  2. Select Insert > Merge Field... to add the Count_Session_Staff (be sure the Current Table (“SESSION”) is selected from the drop-down) 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_Session_Staff>>”.
  3. Make several 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 3 - Update script

Now you will update the Add New Staff Role script so that it can create a new session staffrole. Currently the script accepts a single parameter named id_event. You will need to modify it to read a second parameter named id_session, and add logic to set either the ID_Event field or ID_Session field based on which parameter is supplied.

  1. Rename the Add New Staff Role ( id_event ) script to "Add New Staff Role ( id_event ; id_session )".
  2. Modify the Parameters comment:

# Parameters:     Specify using # function:
      Must specify one and only one of the following parameters:
      $id_event
      $id_session

  1. Make the highlighted changes to the script 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_event ) and IsEmpty ( $id_session ) ) or
        ( not IsEmpty ( $id_event ) and not IsEmpty ( $id_session ) )
        ]

    # 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
If [ not IsEmpty ( $id_event ) ]
     Set Field [ STAFFROLE::ID_Event ; $id_event ]
Else If [ not IsEmpty ( $id_session ) ]
     Set Field [ STAFFROLE::ID_Session ; $id_session ]
End If

Commit Records/Requests [ With dialog: Off ]

Exit Script [ Text Result: $null ]

  1. Save the script and close the Script Workspace.

Step 4 - Add buttons

Your final task is to add buttons to the Session Detail and Staff Role | Card layouts for creating and editing session staff.

  1. Enter Layout mode and navigate to the Event Staff tab on the Event Detail layout.
  2. Copy the Add Staff button to the clipboard.
  3. Navigate to the Session Staff tab on the Session Detail layout and paste the Add Staff button. Set the location of the button the same as it was on the other layout, with a Left position of 272 pt and a Top position of 600 pt
  1. Set the button’s script parameter to the formula: # ( "id_session" ; SESSION::ID )
  1. Select the transparent button that covers the first portal row.
  1. Set the button’s script parameter to the formula: # ( "id_staffrole" ; session_STAFFROLE_SessionStaff::ID ).
  1. Enter Browse mode to save and review your work.
  2. Enter Layout mode and navigate to the Staff Role | Card layout.
  3. Select the merge field button displaying <<staffrole_EVENT::Event_Name>>. Assign it the hide condition: STAFFROLE::Role_Type ≠ “Event”. This means the button will be hidden when the record is not for an event staff role.
  1. Duplicate the button, and modify the new button as follows:
    1. Set the label to “Session: <<staffrole_SESSION::Session_Name>>”
    2. Assign it the hide condition: STAFFROLE::Role_Type ≠ “Session”. This means the button will be hidden if the record is not for a session staff role.
    3. After modifying the text, you may have lost the gray text. If so, activate the Text tool. Click on the button and select the button label text “Session:”. Using the Appearance tab in the Inspector, change the text color to gray.
  1. Stack the new button directly on top of the first button, as shown. Based on the hide conditions you set, only one of the buttons will appear for any given record.
  1. Enter Browse mode to save and review your work.

Step 5 - Review your work

  1. Go to the new Session Staff tab on the Session Detail layout.
  2. 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 Session Staff tab.
  3. 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.