8 Replies Latest reply on Apr 25, 2013 9:58 AM by philmodjunk

    Create Records Automatically from Related Table

    ClaireCollin

      Title

      Create Records Automatically from Related Table

      Post

            

           I am trying to have my database create new records in a table related to my initial table which is created with a raw data import.  I think I need to use a script however, I am not familiar with scripting and it seems a bit confusing to me.  I saw the post below and tried to follow it but I am not sure I am undertstanding what the $K means or if this script works with Filemaker Pro 11 because I can't seem to make the 3rd step. Can you explain this method in more detail?

      Previous Post: Create and populate records automatically July 22 2012

      The system will need to know how many records to create and any other data such as a value in a foreign key field that might need to be entered into each new record.

           Define a global field and put it on a layout or use it as an input field with Show Custom Dialog so that the user can enter a number into it.

           I'll refer to it in this script as Globals::gNumRecs

           Loop
              Set Variable [$K ; value: $K + 1]
              Exit Loop If [$K > Globals::gNumRecs ]
              New Record/Request
              Set Field [YourTable::SequenceField ; $K ]
           End Loop

           Add additional set field steps at the end of the loop if there is other data to enter into the new record.

            

        • 1. Re: Create Records Automatically from Related Table
          philmodjunk

               $K is a variable and it works equally well in FileMaker 11

               Exit Loop If [$K > Globals::gNumRecs ]

               Compares the value of $K to the value of the field: Globals::gNumRecs. The g is a typical naming convention for a field where you have specified global storage. Often, such fields are defined in a separate tables which is often named "Globals" as it is in this example, but fields with global storage can be defined in any table in your file and their values will remain accessible from any script, layout or calculation in  your file.

               If you are importing records and then want to generate related records for each newly imported record, this script may need modification as it relies on the user to enter a number into gNumRecs in order to specify the number of related records to create.

          • 2. Re: Create Records Automatically from Related Table
            philmodjunk

                 There is also nothing in the sample script portion that you quoted that will set a value in a field to link it to a record in another table, so additional steps are needed to acquire the needed primary key value in a variable and another set field step is need to set that value to the foreign key field of the related table.

            • 3. Re: Create Records Automatically from Related Table
              ClaireCollin

                   Okay, So if my imported table (Table1) has the following fields SKU, Name, Week, In Stock, Committed, and Available; I want the second table (Table2) to automatically create new records for each Week by Name. So that there will be a record for each Week and Name, however many this takes. I do not have anything stored globally because everything has unique values.  The tables linked by Week with the option for Table2 to create new records based on this relationship.  How do I write a script to have Table2 create new records based on Table1?

              • 4. Re: Create Records Automatically from Related Table
                philmodjunk

                     This is the key phrase that I've picked out of your last post:

                     

                          So that there will be a record for each Week and Name

                     Create a new Tutorial: What are Table Occurrences? that refers to Table 2 (In manage | Database | Relationships, select Table 2 and click the ++ button.)

                     Link it like this:

                     Table1::week = Table2|weekName::week And
                     Table1::Name = Table2|weekName::name

                     Enable "allow creation" for Table2|weekName in this relationship.

                     Then this script will create one new record in Table2 for each record in the current found set of records in Table1:

                     Go to Record/Request/Page [First]
                     Loop
                       Set Field [Table2|weekName::week ; Table1::week]
                       Go to Record/Request/Page [Next ; Exit after last]
                     End Loop

                     Note 1: I don't know exacatly what data you are entering in Week. It should be data that is not only unique to a given week but unique for the week for the year or the relationship will start matching to existing records once you do this for the data for the same week of a new year. A date that records the date of the first day of the week would work well for this.

                     Note 2: I renamed the new table occurrence to be Table2|weekName. You can do this by double clicking the "box" produced when you click the ++ button.

                     Note 3: I am suggesting a new table occurrence so in order to keep your existing relationship to table 2 unchanged.

                • 5. Re: Create Records Automatically from Related Table
                  philmodjunk

                       Note 4: I would consider using SKU instead of name for this relationship if SKU holds the type of data I'd expect for this situation.

                  • 6. Re: Create Records Automatically from Related Table
                    ClaireCollin

                         This is great! It worked perfectly. But now I have two follow up questions.

                         I have Table3 (imported data) that uses a similar structure (SKU, Name, Week, Shipped) as Table1 but may or may not have different SKUs. Is there a way to find the unique SKUs from Table3 and add those records to Table2? Can records from Table1 and Table3 be created in Table2 within the same script? Or would I need to run the first script and then run a second that does a comparison to the SKUs already in Table2?

                    • 7. Re: Create Records Automatically from Related Table
                      ClaireCollin

                           I did end up using SKU instead of Name

                      • 8. Re: Create Records Automatically from Related Table
                        philmodjunk

                             You started a new thread and I posted my answer for this there. (Just so you know, I've had to limit my responses to this forum to one response per question per day so you won't see follow up comments until the next day in most cases.)