1 Reply Latest reply on Sep 9, 2013 3:31 PM by philmodjunk

    Master Detail synchronization with mix of portals and non-portal fields



      Master Detail synchronization with mix of portals and non-portal fields


           I have a Master table (Client) with many associated Detail tables. Some of these detail tables are accessed through portals - but others are not. How do i make sure that the Client-ID value is propogated to all the Detail tables Client-ID fields - whether they are in a portal on my layout  - or not? Do i need to use Global Variables and assign in a script? Is there a more automatic way?


        • 1. Re: Master Detail synchronization with mix of portals and non-portal fields

               Much will depend on the design of your user interface.

               If you enable "allow creation of records via this relationship" and add new related records by entering data in the blank "add" row this option places after the last record in your portal, the synchronization is handled for you. This fact explains why that method is often recommended to "newbies" setting up related tables even though it isn't always the best method to use.

               Likewise, if you use a button with a script to create the new related record, your script can set the foreign key field to the value of the parent record as part of the script that creates the new record. This is usually done by setting a variable to this value while on the layout for the parent record (Client in your case). Then switching to a layout based on the related table and creating the new record--using set field to copy the value from the variable into the matching FK field of this new record.

               But in other contexts, you have to be very careful that any automatically entered ID value is the correct value. Sometimes this works to use OnRecordLoad to perform a script that sets a global variable or global field to the current record's ID. And then you can define an auto-enter calculation to copy that value from the global into the new records FK field. But if you use this approach you have to make sure that the global always has the correct value before you create a new record. That may be very easy to do or somewhat complex depending on the design of your system.

               Another method, when creating a new record on a layout based on a child table, is to set up the FK field with a value list of the Parent Record ID's. A name field from the Parent Table can be included in the value list so that you can select a record by name even though the value list is entering an ID number. You can set a required value validation on the FK field so that the user has to select something in this field to keep them from creating a record and then failing to link it to a parent record. This no longer enters the ID automatically, but it also is not locked into any one record in the Master table and thus this can be a better option in some situations.

               And you aren't limited to an ID number based drop down list or pop up menu for this--a method best used for relatively small lists of values. There are mores sophisticated approaches that combine a control on the layout with scripting such that you can enter all or part of a name and the system looks up and enters the ID number.