6 Replies Latest reply on Jul 10, 2014 8:19 AM by SteveMartino

    How to create new related records for all in a found set



      How to create new related records for all in a found set


           I have database with table A and related table B (one-to-many via unique ID) that's used to record commonly occurring actions (from a value list) for the records in A, along with a creation timestamp.

           How might one go about automating the creation of new records in table B simultaneously for every record in a found set in table A, which records the same action from the value list for them all?

           In other words, I have N records in a found set of table A, and I want to create a record in table B of action V1 for all N records, all with one button click.  Any suggestions about an elegant way to script this?

           -- Andrew

        • 1. Re: How to create new related records for all in a found set

               Since you have not indicated what version of FileMaker you are using, my answer may not work for you as it assumes FileMaker 13:

               I will assume these table names and match fields. Substitute yours for mine in the following script:

               TableA::__pkTableAID = TableB::_fkTableBID

               Define a summary field, sIDList, in TableA as the "List of" __pkTableAID

               #after performing the find or other actions to pull up a found set....
               Set Variable [$Value ; value: TableA::valueListField ]**
               Set Variable [$IDList ; Value: TableA::sIDList ]
               Go to Layout ["TableB" (TableB)]
                  Set Variable [$K ; value: $K + 1 ]
                  Exit Loop If [ $K > ValueCount ( $IDList ) ]
                  New Record/Request
                  Set Field [TableB::Value ; $Value ]**
                  Set Field [TableB::_fkTableAID ; GetValue ( $IDList ; $K ) ]
               End Loop
               Go to Layout [original layout ] ---> this steps assumes that you want to end up on the same layout where you started.

               ** The above script assumes that TableA::ValueListField is a field with regular storage. If you define it as a field with global storage, you can omit the set variable step and modify the set field step to be: Set Field [TableB::Value ; TableA::valueListField ]

               If you are new to scripting:

               When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

          • 2. Re: How to create new related records for all in a found set

                 This is so cool, PhilMod, and it's almost got me there. To get all the way, I should clarify a little on how my tables and value list are set up, and the attached screen capture will help. (And yes, I'm using version 13.)

                 The lower Applications window (table A) shows a tab panel with a portal to the active record's history (table B). I show a found set of three records to whose history I added an arbitrary value, "Test Value," which I entered as the value in the first Set Variable step of the nifty script you wrote, run using a Record Actions button

                 You can see the three newly created records in table B in the upper Applications-2 window, which is displaying all four fields that constitute table B.

                 Now, what I'd ideally like to do is modify the script so that it begins by opening a dialog box asking the user to select the action they wish to enter from the value list I use for this purpose -- the same list you see popped up from the portal on the History tab, and which, incidentally, is a custom list.

                 As I interpret your script, I'm guessing what I have to do is create a new table of my action values, and then use that to populate the value list, rather than using custom values. Because as I'm reading the reference helps on script commands, it seems like there's none that will allow me to set that variable by referencing a custom value list -- it's got to reference a discrete table of values.

                 Is that correct?

                 -- Andrew

            • 3. Re: How to create new related records for all in a found set

                   No need for a new table unless you want to move from a value list of custom values to a value list based on values in a field. As a list of values lengthens, the values can often be easier to manage in a table where you can use the standard database tools for searching, sorting and modifying records to better keep control of exactly what values are listed in a value list.

                   But to get a dialog that pops up with this value list, I'd:

                   A) Define a global text field to format with this value list as this simplifies the script slightly. (See ** note above).

                   B) set up a layout for this dialog, place the global field on it and format it with the value list.

                   c) Use one script with the New Window script step to open a modal dialog window and change layouts to this layout with the global field.

                   d) Use a button placed on this dialog window to perform the rest of the script. A "Cancel" button can just close the window.

                   e) and I very likely would not use a modal dialog window as you requested when using FileMaker 13. I'd use a popover in place of the dialog window in most cases as it simplifies a lot of the window management details of this script.

              • 4. Re: How to create new related records for all in a found set

                     Phil, quick question regarding your script, just for my own brain....after you perform a find and get the found set, why would you need the loop counter?  Wouldn't go to next record, exit after last work here?  Just wondering, and always trying to learn.


                • 5. Re: How to create new related records for all in a found set

                       If I was looping through records, yes. But the script loops through values in a list, not records in a found set. The loop counter is used with GetValue to access specific values in a return separated list of values created by Set Variable [$IDList ; Value: TableA::sIDList ]

                       Note: we could set up the script to loop through records in that found set, but then you have to use go to layout to keep flipping back and forth between layouts where this, slightly simpler script only flips back and forth a single time as all the needed values are put in the $IDList variable.

                  • 6. Re: How to create new related records for all in a found set

                         Ahhh of course I see it after you explain it :).......thank you Phil