People Goal 5 - Part 7: Loop through a found set of records

Document created by Kedar on Feb 4, 2015Last modified by communitymanager on May 12, 2015
Version 10Show Document
  • View in full screen mode

 


At this point, your "Add portal row" script makes it easier for the user to add secondary table records manually by going to the bottom of the portal. But what if you could automate this process completely, and repeat it for an entire group of people? For example:

 

  • In a sales contact solution, you might want to create an “email” activity for everyone in a group of contacts because you sent them an email blast outside of your solution.

  • In a student solution, you might want to enroll a group of students in the same class.

  • In a patient solution, you might want to schedule a group of patients for a single group appointment.

 

To do this, you’ll need two new script steps: Loop and End Loop. These are used to mark the beginning and end of a group of script steps that you want to perform more than once. FileMaker Pro repeats any script steps enclosed within the loop until one of those steps tells the script to exit the loop.



Three different script steps allow you to exit a loop and continue with the script:

 

  • Exit Loop If

    Like the If step, this involves specifying a logical expression. When this step is encountered and expression is true, the script stops looping and moves to the script step immediately following the End Loop step.


  • Go to Record/Request/Page

    This script step moves from one record to another within your found set. When the option "Next" is selected, the script step moves to the next record, and when the option “Previous” is selected, the step moves to the previous record.


    For these two cases, a second option is available: “Exit Loop After Last” (for “Next”) and “Exit Loop After First” (for “Previous”). Let’s talk about “Exit Loop After Last”:

    When this option is selected, before moving to the next record, this script step checks whether the current record is already the last in the found set. If so, the step stops the script from looping and moves to the step immediately following the End Loop step.

    “Exit Loop After First” is identical but exits when the current record is the first in the found set.

  • Go to Portal Row

    This is just like the
    Go To Record/Request/Page script step, except that it moves through the rows in a portal instead of the records in a found set. It includes the options “Exit Loop After Last” and “Exit Loop After Previous”, similar to those in the Go To Record/Request/Page step.

 

 

 

In this case, you’ll use the Go to Record/Request/Page step to move through your found set of main table records (or people). For each main table record (or person), you’ll perform a group of script steps that add a secondary table record.

 

When you’re done with this script, it should resemble “Add portal ow - loop” in the example solution.

 

 

Goal:

 

Write a script that adds a secondary table record for every main table record in the found set.

 

 

Steps:


Create the loop structure


  1. Create a new script in the Script Workspace by clicking the + button above the left panel, or by pressing Command-N (OS X) or Control+N (Windows).

  2. Name your script:  Add portal row - loop

  3. Add a Go To Record/Request/Page script step.

    Since you want to process every record in the found set, let’s start at the first record. You can see that “First” is the default setting for the Go to Record/Request/Page script step.

  4. Add a Loop script step.

    This creates two script steps, Loop and End Loop.

  5. Duplicate the Go To Record/Request/Page script step, and drag the duplicate below the Loop script step.

  6. Change the Specify value from “First” to “Next”.

    Every time this script step is encountered, the script will move to the next record in the found set.

    When you select “Next”, a new Exit After Last option will appear.

  7. Select the Exit After Last option.

    This causes the script to stop looping after the last record is reached. Since you’re starting on the first record, each record will be processed by the loop, including the last one. After that, the script will exit the loop.

    At this point your loop structure is complete. Here’s how it should look:
    script add portal row progress.png

    This is a basic pattern that you will use many times in your scripts.

 

 

Add the repeating steps

 

  1. Select the Loop script step.

  2. Add a Perform Script script step.

    A new Perform Script script step is added below the Loop script step.

  3. Click the portion of the step that says "[<unknown>]".

    This specifies which script will be performed.

    A
    Specify Script dialog window appears.


  4. Select the “Add Portal Row” script.
  5. Click the OK button to dismiss the dialog window.

    At this point, the portal row will be created by the script step, but it won't have any identifying information. Now you need to decide what data points you want to set for each new row.

    In the example file, we’re setting the activity type to “Email”, since we want to document that an email was sent to a group of people. In your script, you might be setting an appointment type, a class number, etc.

  6. Add a Set Field script step.

    First you need to specify the table occurrence and field you’ll be working with:

  7. Click the blue gear to set the script step options.
    set field options.png
  8. Select the Specify target field option.

    A Specify Field dialog window appears.

  9. Choose your secondary table and select the field whose value you want to change.

    In the example solution, this is “Activities” and “Type”.

  10. Click the OK button to dismiss the dialog window.

    Next you need to specify the value you want to assign to this field:

  11. Click the second Specify button (“Calculated result:”).

    A Specify Calculation dialog window appears.

  12. In the large open box, enter an expression that will give the result you want.

    In the example file, this is a literal string (including quotes):  “Email”

  13. Click the OK button to dismiss the dialog window.

  14. If you need to set more than one field to accomplish your purpose, repeat steps 13 to 20.

    The example file also sets the value “Mass mailing” in the “Activities::Notes” field.

    For a patient, you might set the doctor’s name, or the building and room where the appointment takes place. For a student, you might set the teacher’s name or the number of credits.

  15. Save the script by typing ⌘S (Mac) or Ctrl-S (Windows).

    Great, the script is complete! It should look something like this:
    script add portal row loop.png

Display in the Scripts menu

This script will be used rarely enough that it probably doesn’t require a button on the interface. Instead, let’s make sure it’s available from the Scripts menu.

Every time you create a script, it is enabled in the
Scripts menu by default. So there’s nothing to do here right now — but later you’ll want to decide which of your scripts are available from this menu and which are not. To enable a script, select the checkbox on the left of the script name in the left pane of the Script Workspace:

script management checkboxes.png


This causes the script to appear in the Scripts menu as below. Note that each script listed in the menu (up to 10 of them) is given a keyboard shortcut.

scripts in scripts menu.png



Test the script

  1. Make sure you are viewing your Main Detail layout in Browse mode.

  2. Make sure that all records are displayed (⌘J on Mac or Ctrl-J on Windows).

  3. From the Scripts menu, select your script to run it (or use the keyboard shortcut).

  4. Navigate to the first record in the found set and check the portal to see whether a new secondary record appears with the data you specified.

  5. Navigate to the last record and check it.

  6. Spot check several other records as well.


     

      

Attachments

    Outcomes