1 2 3 Previous Next 40 Replies Latest reply on Nov 23, 2011 9:26 AM by MikeF

    Auto-population of primary key fields in related tables

    MikeF

      Title

      Auto-population of primary key fields in related tables

      Post

       

      From former message in a thread now too long and unrelated…

      Whenever I add a record in tblEvents, thereby triggering an EventID, need to have that, and indeed each, EventID in tblEventID automatically add itself to three tables. Again, have used Auto Enter Looked Up Value in each of those tables' EventID fields, expecting them to auto-populate. To no avail ..! How can this --- "auto-population" of EventID --- be accomplished??

      I Put this in a script trigger OnObjectSave for EventID in the parent table, but it doesn’t work. Uncertain why not ...

      Set Variable [$ID ; value: ParentTable::__pk_PrimaryKeyfield]
      Freeze window
      Go To Layout [//Layout for child table]
      New Record/request
      Set Field [ChildTable::_fk_ForeignKeyField ; $ID ]

        

      At any rate, I wasn’t clear enough.

      The records in the parent table will sometimes be imported en masse from Excel, sometimes added one at a time, and sometimes added [en masse] as a result of a script I’ll be working on down the road.

      So to add one record at a time to the parent table won’t work.

      Will effectively need to “sync” the tables’ primary key fields so all the records are the same all the time.

      For clarity, the child tables do not have foreign keys, they all have one primary key field that needs to be identical to the parent.

      Thanks,

        Mike

       

       

        • 1. Re: Auto-population of primary key fields in related tables
          philmodjunk

          I Put this in a script trigger OnObjectSave for EventID in the parent table, but it doesn’t work. Uncertain why not ...

          Set Variable [$ID ; value: ParentTable::__pk_PrimaryKeyfield]
          Freeze window
          Go To Layout [//Layout for child table]
          New Record/request
          Set Field [ChildTable::_fk_ForeignKeyField ; $ID ]

          Assuming that __pk_PrimaryKeyField is an auto-entered serial number, the OnObjectSave event never happens and thus your script is never performed. You'd need to use a different method of performing the script. OnObjectSave and OnObjectExit are triggers which are only tripped when the cursor is in this field and then the focus is changed to another part of the layout. OnObjectExit will trip every time this happens. OnObjectSave will trip if the value in the field was changed while the cursor was in this field.

          It's possible to set up a loop that does the above steps for a group of records in the parent table as a way to do a "batch" operation to generate child records.

          For clarity, the child tables do not have foreign keys, they all have one primary key field that needs to be identical to the parent.

          Hmmm, just to clarify the terminology, unless this is a one to one relationship, your child tables do not have Primary Keys. Instead they only have a Foreign Key field to match to the Primary Key field of the parent record.

          A primary key that unqiuely identifies each record in the table. Foreign keys are values that match a record to a value in another table. I believe you have this relationship here:

          ParentTable::PrimaryKey = ChildTable::ForeignKey

          This is the typical relationship found in a one to many relationship with one ParentTable record linking to potentially many child records.

          • 2. Re: Auto-population of primary key fields in related tables
            MikeF

             

            The child tables I'm referring to have only *one* record that would match the primary key of the parent table.

            They are a one to one relationship.

             

            There are other child tables that are many to one with this parent, but they are not relevant to this particular auto-populate thread.

             

            Thanks,

              Mike

            • 3. Re: Auto-population of primary key fields in related tables
              philmodjunk

              The child tables I'm referring to have only *one* record that would match the primary key of the parent table.

              Then I suggest you simply include the fields from this child table as fields that are part of the parent table and dispense with the child table. This eliminates the need for a script to populate the child record.

              • 4. Re: Auto-population of primary key fields in related tables
                MikeF

                 

                Wanted to do that initially, but the child tables have a lot of fields, including many calculated fields.

                Their manageability will be an issue if I combine the fields.

                Am concerned about system performance as well.

                So looking for an appropriate method to sync the primary key fields.

                Thanks,

                 Mike

                • 5. Re: Auto-population of primary key fields in related tables
                  philmodjunk

                  So looking for an appropriate method to sync the primary key fields.

                  Already explained why your script trigger wasn't firing. The script will work, you just need to use another method to perform your script. What method you use depends on the layout design and how the data is input into the table. A looping script that loops through your parent records can do this for any number of records in your parent table all in one batch operation.

                  Their manageability will be an issue if I combine the fields.

                  I doubt that will really be much of a problem even if you end up with a table with over 100 fields. And performance may actually be slower in some cases when you use the related table--meanwhile you have to script your synchronization--which would be avoided if you combined the fields into a single table.

                  That leaves you with two possible solutions a script or a merged table. If you need more detail on the scripted approach let me know.

                  • 6. Re: Auto-population of primary key fields in related tables
                    MikeF

                     

                    Phil,

                    Would very much appreciate detail on the scripted approach.    **Understand your explanation re the correct trigger to use.

                    At least I'll get it working, and assess how it impacts everything else.

                    Thanks.

                      Mike

                    • 7. Re: Auto-population of primary key fields in related tables
                      MikeF

                       

                      Ok, changed the script trigger and it works when I add one record.

                      Does not work when importing multiple records, but presume a loop will take care of that.

                      Question -- I need to get the primary key into about three tables. Do I need three separate loops or can it all be accomplished with one?

                      Thanks,

                        Mike

                       

                      • 8. Re: Auto-population of primary key fields in related tables
                        philmodjunk

                        You can use one loop, to loop through your found set of parent records with three sets of go to layout, new record, set field steps to create the related records.

                        Import Records....
                        Go To Record/Request/Page [first]
                        Loop
                           Set Variable [$ID ; ParentTable::__pk_PrimaryKey]
                           #First child table
                           Go to Layout [Child1]
                           New Record/Request
                           Set Field [Child1::ID ; $ID]
                           #second child table
                           Go to layout [child2]
                           New Record/Request
                           Set Field [Child2::ID ; $ID]
                           #Thirdchild table
                           New Record/Request
                           Set Field [Child3::ID ; $ID]
                           #Return to parent layout
                           Go to layout [original layout]
                           Go to Record/request/page [next ; exit after last]
                        End Loop

                        • 9. Re: Auto-population of primary key fields in related tables
                          MikeF

                           

                          Phil, thank you for this, and all your assistance.

                          Can I trigger it with the OnObjectExit ??

                           Mike

                          • 10. Re: Auto-population of primary key fields in related tables
                            philmodjunk

                            OnObjectExit on what field?

                            Not if you are importing records. THere's no user interaction with your layout due to the import records action that would trip the script trigger. That's one of two reasons why I included the Import Records step in this script:

                            1) This type of script is usually performed by clicking a button to initiate a records import and then the script generates the additiional related records after importing them.

                            2) Import records produces a found set of all the newly imported records, thus this script can loop through this group of records immediately after the import to produce the matching records in the related tables for every imported records. If you don't combine the import records action with the create related records process, you leave the door open for a user to accidentally modify your found set--performing a find, show all records, omit record... and then this script will not be able to create the correct set of related records--it may in fact create extensive problems for your database by skipping some records (no longer in found set) and creating a second set of related records (older record now included in found set) for a record that already has a set.

                            • 11. Re: Auto-population of primary key fields in related tables
                              MikeF

                               Got it, just need to specify the ImportRecords parameters.

                              Thanks,

                               Mike

                              • 12. Re: Auto-population of primary key fields in related tables
                                philmodjunk

                                They can be a challenge unless it's a case where the file from which the data is imported always has the same name and is always placed in the same location prior to importing.

                                It is possible, to use a container field and Insert file, to modify the above script so that the user get's an open file dialog, selects the file from which to import and then the script imports data from the file the user selected. IF that's something you need, let me know.

                                • 13. Re: Auto-population of primary key fields in related tables
                                  MikeF

                                   

                                  "They can be a challenge unless it's a case where the file from which the data is imported always has the same name and is always placed in the same location prior to importing."

                                  ... That's a bit synchronistic [!!], as I was just going to ask ...

                                  Re ImportRecords ... When that script step runs, would like it to [pause and?] merely open a dialog box accessing my directory structure, allowing me to choose which directory/file I would like to import from.

                                  Can that be done?

                                  Thanks,

                                    Mike

                                  • 14. Re: Auto-population of primary key fields in related tables
                                    philmodjunk

                                    Two options:

                                    1) If you don't specify the "no dialog" option, this script will stop and you'll get the same screens for specifying the field mapping and other options that you do from a manual import records action from the File menu. This works and is simple to specify, but may provide the user (if it's not you), with more options than they need and can be confusing.

                                    2) The other option is to add a container field to your system and use insert File with the the "store a reference" option specified to insert a reference to the source file into the container field. The user gets an open file dialog to select the file and click open.

                                    Then the script extracts the filepath and file name from the container field and puts this data into a variable. Your import records step then uses this variable to import records from the selected file. This way, you can import data from any file with any file name and from any accessible location, but all other import options such as the field mapping are kept the same for each import.

                                    1 2 3 Previous Next