2 Replies Latest reply on Oct 27, 2014 7:30 PM by krod

    Script to check if record exists, and create it



      Script to check if record exists, and create it


      Each employee must be trained on any new version of a Standard of Operation (SOPs) document.

      SOPs Table. Lists the SOP version numbers.

      Employee Table. Lists each employee. Related to Employee Training Records Table by EmployeeID field.

      Employee Training Records Table. Contains the SOP version number and date employee passed it off. 

      What I'd like to do is create a script that when a new SOP version number is added to the SOPs Table will check if each employee in the Employee Table has a training record in the Employee Training Records Table containing that SOP version number, and if the employee does not have a training record for that SOP, it will create one. I can't, for the life of me, figure out how to cycle through each employee in the Employee Table to check in a related table (the Employee Training Records Table) for a record. Is this possible? Am I insane? Have I described this well enough?

      Also complicating things is that I only want to add a SOP training record for employees that have an "Active" status (contained in the Employee Table).


        • 1. Re: Script to check if record exists, and create it

          Good ole ISO 9000 document control procedures--been there done that...

          I suggest that you set up a special relationship to a different occurrence of the training records table just for this purpose with "allow creation of records via this relationship" enabled.

          The relationship would be defined as:

          Employees::EmployeeID = EmployeeTrainingRecords|add::EmployeeID AND
          Employees::gSelectedVersionNumber = EmployeeTrainingRecords|add::VersionNumber

          gSelectedVersionNumber would be a field with global storage specified so that you can specify this value once for all records in your Employees table.

          Then you can perform a find for all active employees and use a looping script such as this:

          Enter Find Mode []
          Set Field [Employee::Status ; "active"]
          Set Error Capture [on]
          Perform Find []
          IF [ Get ( FoundCount ) ]
                  Set Field [ EmployeeTrainingRecords|add::VersionNumber ; Employees::gSelectedVersionNumber ]
                   Go To Record/Request/Page [ Next ; exit after last ]
             End Loop
          End If

          The set field step will create a new record if one does not exist. If one exists, it assigns a value to the existing record that is already present in that field and thus no change to your data takes place.

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Script to check if record exists, and create it

            Phil, Thank you for your helpful response. As always, your vast knowledge is so appreciated. Thank you for sharing.