9 Replies Latest reply on Dec 13, 2016 8:23 AM by philmodjunk

    Keeping related records up to date


      Hello, I've got a question about reconciliation of a field in my database. To set up the question, I've got a DB with let's say 3 tables. Table 1 holds all user information and has a field (pk_userid) and Table 2 and 3 each have another user id field (fk_userid). The parent key is a unique ID used to reference any records in any of the other related tables. My issue is with trying to reconcile all the entries for this field. I have a very rudimentary script that Freezes the layout and copies the userid field, goes to the other layouts and copies them. It seems to me that there would be easier ways to keep these up to date so when a new user is added in Table 1, Filemaker can add the userid to the other tables. Any guidance or help is appreciated. Thanks!

        • 1. Re: Keeping related records up to date

          Why would you need to add the userID immediately after adding a new user to the parent table (Table 1)?


          You don't really need to do that until you are actually adding a related record in table 2 or 3. If you use relationships to the other tables that have the "create" option specified for tables 2 and 3, You can use portals to tables 2 and 3 on your table 1 layout to add new related records as needed. In that context, the ID will be automatically entered into each new record. If you did not use the portal and just put the fields on the layout, you could still do this, but you would only be able to create one related record for a given user. Likewise, you could use set field to set data to a field in one of the related records and this would also allow you to create one and only one record.


          Beyond that, there are a number of scripted methods that can automate this process.


          Look up the "MagicKey" method as a way to create multiple related records in other tables.

          The OnRecordLoad script trigger on your Table 1 based layout can set a global variable to the current record's ID. then, any time that you or a script switch to a layout for table 2 or 3 and create a record, an auto-enter calculation can enter the value of that global variable.


          A script can set a variable to the value of the user ID from the current record and then create the new record and set its ID field. From a Table 2 or 3 layout, this can produce another record with the same ID as the current record. From Table 1, the script can set the variable, change to a layout based on table 2 or 3 before creating the new record and setting the ID field as a way to create a new related record linked to the current record on your Table 1 layout,

          • 2. Re: Keeping related records up to date
            David Moyer

            ... when a new user is added in Table 1, Filemaker can add the userid to the other tables.


            your question seems to change there at the end.  Reconciliation is something that would require more explanation.  If you want to create new child records when a new parent record is created, then I would do it the way you are doing it, scripted ... store the pk in a $variable; go to layout; add new record; set fk to $variable; etc.

            I would make this operation a "subscript" so that you can call it from other scripts.  In FM, a subscript is just a script - with subroutine implied by name and function (e.g. sub_CreateNewRecordSet).

            • 3. Re: Keeping related records up to date



              I've uploaded a sample file.

              Hope it helps


              If not, maybe you can upload your database, or add a screenshot of your script and your relationship graph.




              • 4. Re: Keeping related records up to date

                A thought, based on a guess at what you want...


                If you create a new user in Table 1...it gets a pk_UserID...no issue.


                If you want a related record to exist in the child table, simply setting a field in the child table will create the record AND set the fk_UserID.


                Sitting on my Table 1 layout...create the record...SetField (Table2::fk_UserID ; Table1::pk_UserID)...done.


                Is this what you're trying to do?  If so, why do you really need that blank record over there in Table2?

                Why not just create it when you put real data in?


                From the layout on Table1, the step of SetField(Table2::Address ; "whatever") will set the address AND the fk_UserID.

                • 5. Re: Keeping related records up to date

                  I need the UserID automatically created in the other tables because of how the operators will use the DB. One person is in charge of keeping that Table 1 with all the user info. Another subset of users will operate on the other tables filling in the information pertinent to their role in the group.


                  I guess I'm not just looking for a way to add the UserID to the child tables, I'd also like it to reconcile records using the pk_UserID field as the basis for said reconciliation. A much more complex beast entirely.

                  • 6. Re: Keeping related records up to date
                    David Moyer

                    so, that reinforces the need for a script to create the new parent record and needed child records all at once (using the method in post #4).

                    You would no longer use the script step New Record/Request to create new parent records, per se; you would use your new script to do so instead.

                    • 7. Re: Keeping related records up to date

                      Is there an issue with just using this script when you add a new record on Table 1?

                      {I am assuming that Table1::pk_UserID is an autoentered value}

                      {I am further assuming that all three tables are related solely by UserID}


                      New Record/Request

                      Setfield (Table2::fk_UserID ; Table1::pk_UserID)

                      Setfield (Table3::fk_UserID ; Table1::pk_UserID)


                      When run from a layout based on Table 1, that would give you want you want, no?

                      • 8. Re: Keeping related records up to date

                        Actually, the pk_UserID is not autoentered. They receive that number from another group entirely that will set the ID prior to us getting it. In this case, I could maybe have the script perform on commit rather than on New Record creation?

                        • 9. Re: Keeping related records up to date
                          Actually, the pk_UserID is not autoentered.

                          That is not good. You need an auto-entered pk field in your table. If you have an ID from an external source, that's OK, but don't use it as your primary key as that leaves your solution at the mercy of that other source's ability to accurately deliver unique,never changing ID values.

                          1 of 1 people found this helpful