10 Replies Latest reply on Jan 18, 2015 1:02 PM by tays01s

    2 parent tables



      2 parent tables


      I have a Calc table, child of a Patient table. However, there are certain 'settings' that affect Calc that can change during the life of a Patient and that would affect >1 Patient. These I've grouped in a Config (configuration) table.

      So Patient < Calc & Config < Calc are both 1 to many relationships. But Patient and Config are neither parent or child to each other. Obviously if I record a new Patient or Config record, there needs to be a new Calc record. Calc is displayed in a portal on the Patient layout.

      Q: How do I ensure that Patient and Config are synchronised within a Calc record?

        • 1. Re: 2 parent tables

          What you describe is a many to many relationship with Calc set up as the "join" table linking Patient and Config in that many to many relationship. Presumably, many records in Patient share the same Config record and a given Config record can link to many Patient records.


          Patient::__pkPatientID = Calc::_fkPatientID
          Config::__pkConfigID = Calc::_fkConfigID

          You can place a portal to Calc on the Patient layout to list and select  Config records for each given Patient record. Fields from Config can be included in the Portal to show additional info about each selected Config record and the _fkConfigID field can be set up with a value list for selecting Config records by their ID field.

          For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: 2 parent tables

            Yes, I've done that and mostly the system works. The problem comes with Config. If someone wants to make a change here, it needs to be a new Config record or return of an old one, but for both a new Calc record needs to be automatically generated. Again, a script can take care of this.

            Q: How do I sequence generating a new Config, name it, adjust the pop-up to show it, make the changes to the new Config, then generate a new Calc (with the new Config::_fkID)? And once a Config is generated it mustn't be changed retrospectively because that would muck up all the previous Calc records. I'm wondering if I need a 'Set' button to lock the Config record once it's complete and generate the first new Calc?

            • 3. Re: 2 parent tables

              Why would a change require a new Config record?

              What do you mean by "return of an old one"?

              In any case, this can be done by Finding all related Calc records and updating their _fkConfigID fields to match to the new config record. Go To Related Records or a scripted find can pull up the desired found set. Replace Field Contents can update them all in a batch update.

              But this will be a change best done when you are sure that no other user is editing a Calc record. If another user opens a calc record for updating at the time your script uses Replace Field Contents to update them, that record will not be updated and an error will be generated telling that a record was not updated, but not which record failed to be updated...

              I do not see, from what you have posted thus far any reason why any of the changes to config should require generating a new calc record and since you have multiple calc records linked to a given Config record, that seems especially odd to need to do that.


              • 4. Re: 2 parent tables

                1. I do have the relationships set up as you describe.

                2. But when I change the Config record and I create a new Calc, the Calc is still using the old Config. If I alter the current Config, then yes, the changes are reflected in the Calc.

                However, the problem may lie in my 'New Calc' script as I'm really duplicating the last related Calc record to allow 'tweaking' rather than requiring re-entry of all data. The script follows:

                Go to Object [ Object Name: "calc" ]
                Set Variable [ $CalcID; Value:Patient::__ID ]
                #Need to go to Config in case this is part of a new Calc following new Config in order to pick up Calc::_ConfigID Go to Layout [ “Config” (Config) ]

                Set Variable [ $ConfigID; Value:Config::__ID ] Go to Layout [ “Calc” (Calc) ]
                If [ not IsEmpty (Calc::_PatientID) ]

                Go to Related Record [ From table: “Calc”; Using layout: “Calc” (Calc) ] [ Show only related records ]

                Go to Record/Request/Page

                [ Last ]
                Duplicate Record/Request

                New Record/Request

                End If

                Set Field [ Calc::_PatientID; $CalcID ]

                Set Field [ Calc::_ConfigID; $ConfigID ]

                Go to Layout [ “Patient_L” (Patient) ]

                • 5. Re: 2 parent tables

                  To repeat: I do not see why you are "creating a new calc". And from what you describe, this would actually be a case of creating a whole new set of calc records as there are potentially many different calc records all linked to the same config record.

                  Any new calc record will only be linked to the "old config record" if it's _fkConfigID field still has the ID of the original config record.

                  • 6. Re: 2 parent tables

                    Focusing down: The main problem, perhaps only problem, is that when I change Config record, this, current Config record is not the one on which the current or any new Calc record is based. The Calc::fkConfigID is stuck on the previous Config record. This despite me running a script when changing Config record to globally set gSelectConfigID.

                    • 7. Re: 2 parent tables

                      Clearly there are details to your design that are not yet accessible in this thread. This is the first mention of a global config ID field. If your _fkConfigID field is set to auto-enter gSelectConfigID and gSelectConfigID is truly a global field, the new record should link to the new config record automatically when a NEW calc record is created. So the first two things to check are the auto-enter calc--is one defined and does it refer to the correct field? and the storage options on your gSelectConfigID field--is it set to have global storage? Another way to set this up is to set a $$GlobalVariable to this value and to reference the global variable in your field's auto-enter calculation.

                      This, BTW, is a slightly different issue from the original--that of creating a new config record and linking existing calc records to it instead of  the previous config record to which they were linked. I think now that I may have mis-understood the issue here due to this missing detail in what you are trying to set up here.

                      • 8. Re: 2 parent tables

                        I've probably been unclear in my description. For each Calc, I'd want the Config to remain set, once the Calc has been made. If I allowed Config to change it would make the Calc potentially inaccurate and/ or no longer a true record for that Patient. Hence I'd be interested in how to 'lock' the Config once it's been set up. To answer the above queries:

                        Re. Global::gSelectConfigID, yes, definitely global storage.

                        Re. Calc::_ConfigID, I'd set an auto-enter calculation ="Global::gSelectConfigID". I've also tried "Config::__ID" in its place. However, although changing Config::__ID is reflected in Global::gSelectConfigID, it does not change Calc::_ConfigID. Again I have tried check/uncheck to the 'Do not replace.....' for the calculation.

                        • 9. Re: 2 parent tables

                          Something doesn't add up here when I review your posts.

                          If Calc::_ConfigID is set to auto-enter the value Global::gSelectConfigID then each new Calc record you create with the new record script step/menu option will automatically link to the Config record identified in that field. It won't matter whether you clear or select the "do not replace" option as this auto-enter feature is being triggered by the creation of a new record which won't have an existing value in that field.

                          It looks like gSelectConfigID is the wrong field, not global after all, or something else in your system, such as a script performed by a script trigger is changing the value of Calc::_ConfigID.

                          Another possibility would be that all of the above is working, but the values of your ConfigID fields in the Config table are not unique. For example, if Config::ConfigID is an auto-entered serial number but the next serial value setting in Field options specifies a value smaller than the largest configID value currently present in your config table.

                          So you might want to enter find mode on a layout based on the Config table, put the ! operator into the ConfigID field and perform a find to see if you find duplicate ID's in this field.

                          You'll also want to confirm whether this is working by checking the values of your actual ID fields, not the data in other fields. It's not impossible that some problem in how the values in the other fields in Calc field update in a new record that is creating the illusion that it still links to another Config record. This is especially possible if you are using duplicate record to create the new Calc record.

                          • 10. Re: 2 parent tables

                            You're brilliant!

                            Inside my New Calc script, prior to making a Global::SelectConfigID, I'd got a Set Field for Calc::ConfigID. Once I got rid of these steps it was still a problem IF I already had a Calc record, because I would be duplicating it......until you change the Calc::_ConfigID calc to allow replacement if any value already there.

                            The last query was 'locking' a Config record so that someone doesn't muck up there previous Calcs, by changing the Config settings.