The Original FileMaker Community
Business Templates - Demo Apps - Video Tutorials -Samples - Help - 46000 Member Forum

The Leading Filemaker Developer Tools

Primary Relations: Be Unique, Set Direction

Helpful notes before reading:

In today’s world, there are plenty of avenues of how people keep in touch with each other. Emails, addresses, phone numbers, etc. Most likely we have multiples of one of these contact methods. For example, the could be our work address versus our home address. Therefore, we categorize not only our information but for other people we that are in contact with, as well. That can be enough to categorize in what way to reach out to a person, but it also may be beneficial to know the primary connection. So, if a person has both a home phone number and a cell phone number and when one is selected as the primary method, we know easily which way to contact them.

Suppose in FileMaker, we have a contact database, and we want to specify the primary method of contact for a person. Let’s start with the example of a contact that has multiple emails. The layout would be based on that contact and there is a portal to a table that has a list of related emails. In the email table, there is a Boolean field that marks whether it is the primary email to be reached. In that portal, there is the phone number with other data about it.

Once that portal with the related email to the contact is set up, then on that line item of a portal we can add a checkbox based on the primary Boolean field. The problem with this is that more than one email that can be check as a primary for a person. Using a checkbox with a script trigger can help remedy this issue.

The script trigger that is being utilized is OnObjectModify that has a script with parameters. The parameters (with the variable names) are as follows:

  • $FieldName = GetFieldName ( contact_EMAIL::isPrimary )

This is the field name of the Boolean primary checkbox

  • $PrimaryValue = contact_EMAIL::isPrimary

The value of the checkbox before clicked.

  • $PrimaryCount = ValueCount ( List ( contact_EMAIL::isPrimary ) )

The amount of related records that is marked as a primary

After the parameters are set in the script, the rest of the script is as follows:

     #There is at least one other related records selected as primary
1    If [ $PrimaryCount ≥ 1 ]

          #If the field has not been selected as Primary and there is one that is selected as Primary
2         If [ $PrimaryCount = 1 and ( $PrimaryValue = 0 or IsEmpty ( $PrimaryValue ) ) ]


          #Set the result to not fill in
3         Set Field By Name [ $FieldName; "" ]

          #Tell the user
          Show Custom Dialog [ Title: "Existing Primary"; Message: "There is already a related record that is selected as a primary. To select this as a Primary, deselect that Primary record, and then select this record primary."; Default Button: “OK”, Commit: “Yes” ]

          End If

     End If
4    Refresh Window

 

The above script does a couple of checks about the related records and the current checkbox, and if it passes it allows the checkbox to be checked. So, the process in detail is (1) it checks if there is more than email selected as a primary. If so, (2) it checks if there is already an existing related record marked as primary and if it isn’t the one that was just checked (verified by the value). When there already a primary checked, (3) keep that value as empty and then tell the user they are unable to check that particular box. (4) Then make sure it shows the correct primary record in the portal. This script is modular with the help of using variables combined with “GetFieldName” and “Set Field by Name”, so it can be used on a checkbox for several portals.

Now the primary contact method can be set, but there is another option for how this works. The above version of the script alerts when more than one related primary record is being checked. This is to help ensure that a primary contact method is not erroneously marked, however, sometimes it may be preferable to check a new contact method a primary without unchecking the previous one. Below is the script of how to accomplish this process, best to be used with a button bar instead of a checkbox. These are the parameters for that script:

  • $ChoiceId = Email::__EmailId

The Id key of the Choice being picked in the portal

  • $Option = “Select” or “Deselect”

The value whether it is selecting or deselecting.

  • $ItemId = Contacts::__ContactId

The Id key of the main table this script is on, and everything is related to

 

After the parameters are set in the script, the rest of the script is as follows:

      #Set the item
1     Set Field [ Contacts::zzg_ContactId; $ItemId ]
2     Set Variable [ $ChoiceIdPicked; Value:contacts_EMAIL__CHOICE::__EmailId ]

3     Go to Layout [ “Email” (Email) ]
4     Set Field [ Email::zzg_ EmailId; $ChoiceId ]

5     Go to Related Record [ From table: “email_EMAIL__SELECTED”; Using layout: “Email” (Email) ][ Show only related records ]

      #Select as a choice
6     If [ $Option = "Select" ]

7          Set Field [ Email::isPrimary; 1 ]

           #If the choice that was previously marked isn't the same as the current choice marked, and if there was a previous choice marked 
8          If [ $ChoiceId ≠ $ChoiceIdPicked and not IsEmpty ( $ChoiceIdPicked ) ]

9               Set Field [ Email::zzg_EmailId; $ChoiceIdPicked ]
10              Go to Related Record [ From table: “email_EMAIL__SELECTED”; Using layout: “Email” (Email) ] [ Show only related records ]

                #Deselect as a correct choice
11              Set Field [ Email::isPrimary; "" ]
            End If

12    Else If [ $Option = "Deselect" ]

                      #Deselect as a choice
13                    Set Field [ Email::isPrimary; "" ]
      End If
14    Go to Layout [ original layout ]
15    Refresh Window

 

This script is not modular and also has more steps to it because it has to uncheck other selections. At the beginning of the script (1 & 2), it sets which contact record and using that it gets the current primary contact record Id. Then (3) it navigates to the table that has the value that is chosen, and (4 & 5) then to the specific record. If (6) the user is trying to select that record then (7) is marked as the primary contact record. It checks (8) whether there was an actual previous contact method picked and that it is different, then (9 & 10) it navigates to the previous contact method, and (11) deselects it. However, if (12) the user is deselecting that choice, then (13) it simply deselects that option. Navigating (14) back to the contact record, and (15) refreshing the window to see the update.

These are just a couple of options on how to pick a specific record in a related table through a portal. It just requires knowing the main, the related table, and the Boolean field that is to be marked. When controlling these elements, it can add to the user flow. In data entry, there is less likely to be an error. Hopefully, this can help you with the user interface!

 

For a copy of the demo file, please contact lbetz@crossit.com .

 

This website uses cookies to improve your experience. We'll assume you're ok with this, but you can opt-out if you wish. Accept Read More

Privacy & Cookies Policy