4 Replies Latest reply on May 15, 2017 11:48 AM by user23814

    Pull values from a related table for script iterations.


      I am trying to write a script that pulls values, one at a time from another FM database, and uses those to search and iterate.  The basic workflow is:


      Database named "Divisions" holds the names of each division that need to the the search values.  There are 20 records in this DB, one for each division.

      Database named "Stage Results" holds all of the data that needs to be manipulated.  Each record in this database has a Divisions value.


      The script does the following:


      1) Take the division value from Record 1 of the Divisions Database

      2) perform a search in the "Stage Results" database with the division value for the Division Database

      3) perform calculations on the found set

      4) Iterate back to step 1 but take the division value from Record 2 of the Divisions Database


      Basically, it would iterate through this until all records in the Division Database have been used.





        • 1. Re: Pull values from a related table for script iterations.

          Set Variable [ $divisions ; List ( DivisionsDatabaseTable::Division ) ]

          Set Error Capture [ on ]


            Set Variable [ $i ; $i + 1 ]

            Set Variable [ $div ; GetValue($divisions ; $i) ]

            Enter Find Mode

            Set Field [ SearchTable::Division ; "==" & $div ]

            Perform Find

            If [ Get(LastError) <> 401 ]

               //DO STUFF ON FOUND SET HERE

            End If

            Exit Loop If [ $i = ValueCount($divisions) ]

          End Loop

          1 of 1 people found this helpful
          • 2. Re: Pull values from a related table for script iterations.

            "database" is a fuzzy term in FileMaker. Are Divisions and Stage Results two tables in the same file or two separate files? It doesn't make much difference here so far, but such details can be important.


            2) does not appear to need be set up as a "search" unless you need to work with a smaller set of records than just all State Result records with a given Division ID. A relationship can match to that set of records and your script can than loop through them to do any batch processing needed-or in some cases Replace field contents may be used. It all depends in the type of calculations that you need to perform in step 3.


            You have two basic options for doing this: a) You can use Go To Related Records or a scripted find to pull up a found set of records on a layout based on "State Result" in order to either loop thru them or do a replace field contents operaton. b) you can use List with your relationship or ExecuteSQL to get a list of Primary Key IDs from the State Results table and use them with a relationship that matches by ID in order to loop through them and do your calculations without changing layouts.

            1 of 1 people found this helpful