1 Reply Latest reply on Oct 7, 2014 4:06 PM by philmodjunk

    Script: best solution or brute force ugliness?



      Script: best solution or brute force ugliness?


      Hi All,

      I'm working on a script that will automatically add records to a table when a relationship is made. Specifically, we run an after school program where participants must sign consent forms and I'm adding a bit of functionality that can be used to keep track of which participants has signed what consent forms - groups within the program don't necessarily have to sign the same consent forms. 

      So, when a participant is added to a program's group the system will loop through the consent forms that are attached to that group and assign them to that participant. I also check to see if that participant has already signed that consent form - I loop through all of the records in the table that joins participants and consent forms to find an entry that matches the consent form ID and the participant ID. If a match is found I set a variable $Exists to 1 - in the next portion of the script I only add the record if $Exists = 0. 

      My question: is this the best way to do this? I've got a double loop - where the inner loop is going to loop through a join table that could potentially get quite large. This is likely how I'd write this if I were implementing this feature in something like php, but is this the best way to do it with filemaker? Is there some functionality or pattern I'm missing that would make this brute force approach more efficient?

      I've attached a screen grab of my script.


        • 1. Re: Script: best solution or brute force ugliness?

          There should be much simpler ways to do this, but the solution starts with your data model--the tables, relationships that make up the "foundation" of your database solution.

          From what you describe, you might have these tables and relationships:


          Participant::_fkGroupID = Groups::__pkGroupID
          Groups::__pkGroupID = ConsentForms::_fkGroupID

          Consent_Forms::_fkGroupID = CompletedConsent::_fkGroupID AND
          Consent_Forms::_fkgParticipantID =CompletedConsent::_fkParticipantID

          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          _fkgParticipantID would be a number field with global storage. It will be updated via a script performed by the OnRecordLoad Trigger on the Participant layout.  A portal to ConsentForms would list all the required forms for the participant's group and a field or two from CompletedConsent, such as the date the form was received can be included in that portal row. If you enable "allow creation of records via this relationship" for CompletedConsent in the above relationships, you can log completion of a particular consent form by entering/selecting the date received in this field and this action will automatically generate a related record in CompletedConsent.

          No scripts are used to get you to this point, though this is just the basics to get you started.

          Note that this set up assumes that each participant is a member of exactly one group. If you need to support a participant being a member of multiple groups, the data model needed becomes more sophisticated.

          Caulkins Consulting, Home of Adventures In FileMaking