2 Replies Latest reply on Mar 22, 2016 9:15 AM by erolst

    Restrict the Number of Matching Records in a Found Set


      What would be the most efficient approach to script a process that restricts the number of matching records in a found set?


      Background: Say I have 100 separate newly-baked cookies and in a database table I have one corresponding FM record for each cookie. The cookies are not yet packaged and so are considered "loose."  A "status" drop-down field in each of the records can have either a value of "loose" or "packaged" but all 100 currently indicate "loose" because the cookies are still on the countertop cooling. (Hungry yet? )


      Now, after they've cooled, I need to package the cookies but each package only holds a maximum of 40 cookies.  In the database, I of course want to automate changing the status of 40 of the cookies from "loose" to "packaged" with one or two clicks and so I whip out the "Manage Scripts…" window and get busy, but I see that I am unfamiliar with the procedure that finds exactly 40 of the "loose" cookies instead of all 100.


      This has to be a problem with a super-simple solution, only I can't seem to come up with it.  Any ideas?


      Thanks in advance!


      MacBook Pro, FMPA 13, Intermediate Skill

        • 1. Re: Restrict the Number of Matching Records in a Found Set

          Hi bee13


          I think you could use the Omit Multiple Records script step as follow:

          1. find all loose cookies

          2. omit Get(found count) - 40 of them

          3. change the status of all cookies in the found set

          Now start again at one until all cookies are packaged.


          Hope that helps



          • 2. Re: Restrict the Number of Matching Records in a Found Set

            Good suggestion from User26869. To add:


            • for #2, you need the Omit Multiple Records step, where you can specify the value as the result of the mentioned calculation

            • to update the found set, use Replace Field Contents []


            Should you ever come around to manage packages, you could first create the package, store its ID, proceed as suggested, then update the found set with the package ID as a foreign key – which would take the role of the packaged flag; if it is in a package (has a package ID), it is packaged … (duh…)