3 Replies Latest reply on Feb 2, 2017 8:27 AM by philmodjunk

    Auto loop script using field value

    CompassAFM

      Hi,

      I currently have a manually triggered script that checks if a job or jobs from a contractor (chosen from a drop down menu - value list) are overdue and then sends the chosen contractor an email with the list of overdue jobs attached as a .pdf file. The value list gets its data from a field in a different table (contractors).

       

      I want to automate this as a script loop, so that the script starts with the first field (contractor name) and then works through all the contractor records until the last record. If jobs are overdue an email is sent, if not it skips to the next contractor name and so on.

       

      Many thanks

        • 1. Re: Auto loop script using field value
          Johan Hedman

          Then you use following Script Steps

           

          Go to Layout (Contractors)

          Show All Records

          Go to records (First)

          Loop

               If ( Patterncount( RelatedContracts::YourStatusField ) ; "overdue" ) >= 1

                    Your Action to send PDF

               End if

               Go to Record (Next) Finish after last

          End Loop

          • 2. Re: Auto loop script using field value
            siplus

            A basic (at least for me) Filemaker principle is "do your search where the data is". Not in a related table.

             

            Of course it begins to matter when you have many records.

             

            So instead of looping on contractors I'd loop on contracts and gather the Contractor's pk's.

             

            But in the days of SQL you can formulate this otherwise:

             

            $allDue = ExecuteSQL("SELECT pk_Contractor from Contracts WHERE contractStaus = 'overdue';"";"")

             

            $uniqueDue = < get unique values from $allDue > (*)

             

            loop $i on 1 to valueCount($uniqueDue)

               set a global to getValue($UniqueDue; $i)

               send mail to  get data from related record (relationship from global to contractors pk)

            end loop

             

            -----

             

            (*) To achieve this you have

             

            - plugins  (MBS, BaseElements)

            - custom functions (like FileMaker Custom Function:UniqueValues( values )))

            - other tricks, like creating a valueList and using ValueListItems

            - use the DISTINCT clause in $allDue, but it might be slower

            • 3. Re: Auto loop script using field value
              philmodjunk

              You can also just perform a find on a layout based on the related records to find only those that are overdue, then loop through them. Those note overdue are already omitted so the need to check and skip records is eliminated. Your relationship will still allow you to access data from the contractor table when sending out your emails.

               

              You can use a sorting by contractor and a loop to build a list of the contracts that are overdue for a given contractor in order to send out a single email to each such contractor with the list either in the body of the email or attached to it as a PDF.