Build-On Tutorial:

Committees

Committees: Lesson 3

See committees assignments for a member

Lesson Overview

The objective of this lesson is to change the Member Detail layout to show the member's committee assignments. From there you can click-through to the committee to show the committee details.

Here’s a sneak peek from what you will build

Step 1 - Create and relate table occurrences and add a field

In the previous lessons you created Committee and Assignment tables. In this step you will create new table occurrences of these tables and relate them to the member table occurrence.

  1. Open the Manage Database dialog (File > Manage > Database...) and switch to the Relationships tab.

    The ID_Committee field in the Assignment table is a foreign key that will be used to relate assignments back to committees. The ID_Member is a foreign key that will be used to relate assignments back to members. There is a one-to-many relationship from Member to Assignment, and a one-to-many relationship from Committee to Assignment. You will now set up the Relationships graph to reflect this.
  2. Select and duplicate the ASSIGNMENT table occurrence using Command-D (macOS) / Control-D (Windows).
  3. Double-click the new table occurrence and rename it to “member_ASSIGNMENT”.
  4. Drag the member_ASSIGNMENT table occurrence up the graph and position it to the right of the MEMBER table occurrence.
  5. Create a relationship between MEMBER and member_ASSIGNMENT by clicking on the ID field in MEMBER and dragging over to the ID_Member field in member_ASSIGNMENT.
  1. Double-click the line that connects the two table occurrences to open the Edit Relationship dialog. Click the option on the right 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 a member record is deleted, any related assignment records will also be deleted.

    Click OK to exit the Edit Relationships dialog
  1. Select and duplicate the COMMITTEE table occurrence.
  2. Double-click the new table occurrence and rename it to “member_assignment_COMMITTEE”.
  3. Drag this table occurrence up the graph and position it to the right of the member_ASSIGNMENT table occurrence.
  4. Create a relationship between member_ASSIGNMENT and member_assignment_COMMITTEE by clicking on the ID field in member_assignment_COMMITTEE and dragging over to the ID_Committee field in member_ASSIGNMENT.

    The member_ASSIGNMENT table occurrence serves now as a join table between the member and committee table.
  1. Switch to the Fields tab of the Manage Database dialog.
  2. Create a calculation field named "Count_Members" in the Committee table with the formula Count ( committee_ASSIGNMENT::ID ). Make sure the Calculation Result is set to Number.
  1. Close the Manage Database dialog to save all your changes.

Step 2 - View committees for a member

Add and format a portal on the Member Detail layout that shows related committees.

  1. Enter Layout mode and navigate to the Desktop > Member > Member Detail layout.
  2. Double-click the tab control object on the layout (for example, double-click on the Details tab label) to open the Tab Control Setup dialog, and create a new tab panel named “Committees”.
  1. Each tab panel has a blue line under the tab name to indicate the active tab. To add this to the new Committees tab:
    1. Copy the small blue line that is off the layout to the right, labeled “Use this for tab underline”.
    2. Navigate to the Committees tab and Paste it. Position and resize the line so that it underlines the Committees tab.
  2. Navigate to the Desktop > Committee > Committee Detail layout.
  3. Select and copy the “committee_ASSIGNMENT” portal using the Edit > Copy menu item, or the keyboard shortcut Command-C (macOS) / Control-C (Windows).
  4. Navigate back to the Member Detail layout and select the Committees tab.
  5. Paste the committee_ASSIGNMENT portal onto the Committees tab using the Edit > Paste menu item, or the keyboard shortcut Command-V (macOS) / Control-V (Windows).
  6. Double-click the portal to open the Portal Setup dialog:.
    1. Set the Show records from: option to member_ASSIGNMENT.
    2. Uncheck the Sort Portal Records checkbox.
    3. Set Number of rows to 5.
  1. Double-click the Member_Name field in the portal. In the Specify Field dialog, select the members_assignment_COMMITTEE table occurrence from the dropdown menu, then select the Name field.
  1. Repeat this step for the Role field in the portal, but change the table occurrence to member_ASSIGNMENT. Do not, however, change the field. You still want to see Role, just from a different context.
  1. Your layout should look similar to the image below. Make sure the portal is placed entirely on the Committees tab. You can check that by opening the Objects pane. The portal should be inside the Tab Control > Committees > Portal CommitteeMembers.

    Note: the delete button on the portal will work without any modifications.
  1. Enter Browse mode to save and review your work.

Step 3 - Create scripts

Next, you will adapt one of the master navigation scripts by adding some extra steps that will allow users to navigate from a member to a related committee.

  1. Open the Script Workspace (Scripts > Script Workspace...).
  2. Select the Member - Go To Related Record (context) script. You will extend the navigation options by adding the highlighted code. You find it near the end of the script.

Allow User Abort [ Off ]
Set Error Capture [ Off ]
Freeze Window

# Parse the parameters
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 [ Result: False ]
End If

If [ $context = "Member_Detail to Donation_Card" ]
   # Member_Detail to Donation_Card
   Set Variable [ $$CARD_ACTION; Value:"Edit" ]
   # Set this fields to prevent entering wrong data when saving the cardwindow when no edits are done
   Go to Related Record [ From table: “member_DONATION”; Using layout: “Donation Card” (DONATION) ] [ New window ]

Else If [ $context = "Member_Detail to Interaction_Card" ]
   # Member_Detail to Interaction_Card
   Set Variable [ $$CARD_ACTION; Value:"Edit" ]
   Go to Related Record [ From table: “member_INTERACTION”; Using layout: “Interaction Card” (INTERACTION) ] [ New window ]

Else If [ $context = "Member_List to Member_Detail" ]
   # Member_List to Member_Detail
   Go to Layout [ “Member Detail” (MEMBER) ]

Else If [ $context = “Member_Detail to Committee_Detail” ]
  # Member_Detail to Committee_Detail
  Go to Related Record [ From table: “member_assignment_COMMITTEE”; Using layout: “Committee Detail” (COMMITTEE) ]

End If

Exit Script [ Result: $null ]

  1. Save the script and close the Script Workspace.

Step 4 - Change button assignment

Your final task is to assign the adapted script to the portal row button in the member_ASSIGNMENT portal on the Committees tab on the Member Detail layout.

  1. Enter Layout mode and navigate to the Committees tab of the Member Detail layout.
  2. Double-click the transparent portal row button in the background of the member_ASSIGNMENT portal.
  3. In the Button Setup dialog, set the following properties for the button:
    1. Change the script to Member - Go To Related Record (context).
    2. Set the script parameterto: # ( "context" ; “Member_Detail to Committee_Detail”)

Step 5 - Review your work

With all of the schema, interface, and scripting now in place, you can test the new committee assignment features.

  1. When you go to the Member Detail layout, you should see a new tab named "Committees" with a portal that lists the committee assignments for that member. In the portal row there is a button to remove a member from the committee.
  2. When you click that button a dialog box should ask you if you want to remove this member from the committee.  
  3. If you click the Yes button the selected portal row will be deleted (the assignment from the member to a committee will be removed).  
  4. If you click No the dialog box closes and nothing happens.
  5. If you click on a portal row, you will navigate to the related record on the Committee Detail layout.