6 Replies Latest reply on Jan 26, 2014 6:04 PM by carojo

    The Complete Go To Related Record

    philmodjunk

      Title

      The Complete Go To Related Record

      Post

      GTRR is a sophisticated and powerful tool that can do many nice things for the programmer. However, its power--combined with some serious omissions in Filemaker's documentation--can lead to unexpected results and even serious damage to your data if not implemented correctly with in your script. This post is intended to function as a primer for novice developers who want to add this tool to their "toolbox," and to fill in the missing information not found in the on line help file.

      Useful facts not documented in Filemaker's help system are shown in blue. Dangerous undocumented  issues (In filemaker 10 and older versions' help systems) that could damage your data are shown in red.

      What it does:

      In short GTRR enables you to use a defined relationship between two tables to produce a new found set and/or current record in a specified table for a specified layout. The resulting found set can be displayed on a different layout or in a new window.

      There are quite a few different options you can specify when adding this step to your script. Each option can produce very different results. Let's start with the most common options for GTRR:

      Go to Related Record [ Show only related records ; From table: "Table Occurrence Name"; Using layout: "Layout Name" (table occurrence name) ]

      From Table:

      This is the key parameter. Refer to your Relationship graph and select a table occurrence (TO) that will pull up the set of records you want. You can think of it this way: If you were to create a portal on the current record of the current layout that referred to this table occurrence, what records would be visible?

      If you specify a table in an external file, GTRR will open that file if it is not already open. It will not, however, bring that file's window to the front unless you add a select window script step after the GTRR to bring this window to the front.

      The script editor will let you select any table occurrence even if there is no valid relationship linking your current table to this specified TO. If you specify an invalid TO, Filemaker displays an error message. After you dismiss the error message, script execution continues with the next step in your script--effectively skipping the GTRR script step. Thus, your script might continue on and modify a completely different set of records in a different table than what you intended.

      Show Only Related Records:

      This is the most frequently selected option. With this option chosen, all matching records in the related table will make up the found set and the first record in the found set will become the current record.

      If you do not select this option, GTRR will simply make the first related record the current record in  the foundset's unsorted record order. If this record is already part of the found set, the found set will be unchanged. If it is not, the found set will be modified to include all records and then the first related record will be made current.

      Match Current Record Only:

      This is the default sub-option for Show Only Related Records. It uses the values in the current record's fields to determine which records to select as the new found set.

      Match All Records in Current Found Set:

      This option brings up all matching records for all records in the found set of your current table. This can be a very useful and powerful option. You could, for example, bring up a found set of all invoice line item records for all invoices printed in a specified month. Performance penalties, however, can be incurred with this option if the found set and related records comprise a very large number of records.

      For both options, if you defined a sort order in your relationship, GTRR will bring up a found set that is sorted in this specified order. If you don't define a sort order in the relationship, the found set will be unsorted. (Exception: If you trigger a GTRR from a portal row, any sort order specified for the portal will be used to sort the matching records instead of that specified in the relationship.)

      Using Layout:

      You must select a layout that refers to a table occurrence that in turn refers to the same underlying table as you specified as your "from table" parameter. GTRR will make this layout the current layout for displaying its results.

      If you specify a layout that does not refer to the correct table, Filemaker displays an error message. After you dismiss the error message, script execution continues with the next step in your script-effectively skipping the GTRR script step. Thus, your script might continue on and modify a completely different set of records in a different table than what you intended.

      Note: this is an unlikely situation to encounter. Neither Filmaker 10 nor 11 allow you to specify a layout that refers to an unrelated table in the script editor. You can however, create this circumstance if you redirect an existing layout to a different table after the script has been created.

      <Current layout>, is a special and sometimes very handy option to specify here if you are using GTRR to refer to a self join relationship. As documented lower down, GTRR's behavior can be significantly different when this option is selected.

      If you have specified a "from table" that is in an external file, you can select a layout from this external file to show the results of your GTRR.

      New Window:

      You can also open a new window to display the results of your GTRR. It can be a useful way to bring up a new found set without modifying the found set for your related table in the current window. This option has all the same parameters as the New Window command, so look it up to learn more.

      Unfortunately, a known bug makes the New Window option more useful in Mac systems. In Windows systems, this option triggers an annoying resize of the current window that can't be completely avoided. You can, however, specify a different layout that refers to a different table occurrence for the same data source table to avoid modifying the found set and current records of your other layout. (And this additional Table Occurrence need not be related in any way to the starting table.)

      Portals and GTRR

      The above information describes how GTRR works when the layout's focus is NOT in a portal. If you add a button to the rows of a portal and use it to trigger a script that contains a GTRR that specifies the same table as its "From table", GTRR not only brings up the specified found set, but also makes the record whose portal row was current, the current record in the new found set.

      Undocumented and Possibly Dangerous GTRR results:

      There are two slightly different circumstances when GTRR will fail. Script execution will continue but any subsequent steps may be modifying a completely unexpected group of records with potentially catastrophic consequences for your data.

      1. GTRR is executed when the focus is outside of a portal, there are no matching related records and a layout is specified by name:
      2. GTRR is executed from an empty row of a portal that references the same table as GTRR.

      Item 2 occurs when you have a relationship that allows the creation of related records. In this case, the last row of the portal will be blank. It can fool you, because it will occur even if there are related records present in the upper rows of the portal.

      In both cases, the specified layout is not selected and there is no change to any found set. As a result, any following script steps will apply to the layout/found set that was current before the GTRR was executed.

      Key exception to the above:
      If you use the <current layout> option, a GTRR with no matching records will generate an empty found set.

      Avoiding the dangers

      You can include an If [] script step either just before or just after GTRR that tests one aspect or another of the same relationship or checks for an error code to avoid having follow on script steps trash your database when there are no matching records.

      For all options except "Match All Records in Current Found Set", Count ( relatedtable:: primarykeyfield ) will return zero if there are no related records. An even better test is: IsEmpty ( relatedTable:: PrimaryKeyField ). You can use either of these test before or after GTRR.  IsEmpty is also a good test for GTRR's that are triggered from a portal. In this situation, you might have any number of related records in the upper rows of the portal so Count() won't work in this case while the IsEmpty function will correctly determine that the current portal row is the empty "add data" row of the portal.

      You can also use Get ( LayoutName ) after the GTRR to see if the current layout has switched to the layout specified in the GTRR. This works for the "Match all records..." option as well. You can also use Get ( LastError ) immediately after GTRR calls. This function will return 0 if there were matching records and will return 101 if there are not.

      Conclusion:

      Used with care, GTRR can be a powerful tool for controlling which record and found set is current. Used carelessly, it can seriously trash your data.

      Edit Notes: 

      1. Fixed a few minor typos and added additional info on why IsEmpty is a good test when triggering GTRR from a portal row.
      2. Reformatted for new forum.

        • 1. Re: The Complete Go To Related Record
          TSGal

          PhilModJunk:

           

          This is excellent information.

           

          I have sent you a private message.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: The Complete Go To Related Record
            FentonJones
              

            I have one more undocumented behavior, which is a little obscure, but has happened to me more than once. If you use Go To Related Record with the (•) Match all records in the found set option, then the relationship must not be empty for the record you are on. Of course, you say. But consider this scenario. The above is a button in the Header. Almost all the records in the found set have a match; but not the particular one you are on. Or this step is in the middle of a process. You've landed on a set of records, and wish to go to records which match a relationship, but the particular one you're on does not match. 

             

            The method to overcome this is to Loop thru the records until you find one that matches (if any, starting at the 1st or last may be necessary if not already the case). Then the Go To Related Record will work. The idea being that some of them match, likely most; you just need to get on one that does.

            • 3. Re: The Complete Go To Related Record
              philmodjunk
                

              Fenton,

               

              I just tested this on a sample database and I cannot reproduce this behavior.

               

              Here's what I did:

               

              I created a DB with two tables: Parent and CHild

              Simple serial number field with allow creation of records enabled linked the two tables.

              I placed a portal on the Parent layout so that I could easily create related child records.

              I created three parent records and used the portal to create related child records for the 2nd and third parent records but not the first.

              I then triggered a GTRR (with match all selected) while the first Parent record was current.

              GTRR worked correctly, showing a found set of all child records. Just to confirm, I used Show Omitted to create a found set of zero child records and repeated the test.

               

              See any differences between your tests and mine?

               

              Using FMP 10.03 Adv on Windows XP

              • 4. Re: The Complete Go To Related Record
                mrvodka
                  

                I hardly ever use GTRR with match all records because of the speed issue. Instead I tend to grab all the IDs of the found set, throw it into a global and use GTRR without the option on. Much faster.

                 

                Anyway, when I rarely use GTRR with match all records, instead of testing with not IsEmpty () or not IsValid () where I can with a regular GTRR, I instead use Error capture.

                 

                 

                Since it will fail by testing for not IsEmpty ()if the record that you are on doesnt have any children instead I trap for error 401 error which is returned if there are NO matching child records at all for the set and 101 which returns if the first parent record does not have related records but some of the other parent records do.

                • 5. Re: The Complete Go To Related Record
                  philmodjunk

                       With the Match found set option, best error trappiung method that I have found is to capture the layout name in a variable just before the GTRR step then compare the name in that variable to Get ( LayoutName ) immediately after the GTRR. If the names are the same, then there were no matching records in the found set and I know that I am still on the original layout and can put steps in place to handle that situation when necessary.

                  • 6. Re: The Complete Go To Related Record
                    carojo

                         Thanks for posting this really useful additional information PhilModJunk - I agree that the official documentation is greatly lacking in detail.

                         I came across your post while trying to find more information on another aspect of using GTRR (with FileMaker 12) - when referring to an external file and using a layout from that file it seems that the "OnFirstWindowOpen" trigger in the external file does not get fired - perhaps because the behaviour of opening the new window defaults to "Open Hidden"?

                         The latter is only an assumption since I have found documentation that states "OnFirstWindowOpen" trigger does not fire ' when a file is opened by another file with the option "Open Hidden" ' (not in official FileMaker documentation but in the 'FileMaker 12 Developer Reference' by Bowers, Heady, Lane & Love) - also as you state in your post, opening a new window for external file will not bring that window to the front.

                    Would really appreciate your feedback on this - I guess FileMaker doesn't offer any way to control this behaviour, which is pretty annoying as it could be a much neater way to fire a script when the external file is first accessed (e.g. to re-login as required for my solution). Guess I will abandon using GTRR and instead run a script in the external file - pity. Would be nice if FileMaker lifted their game in the documentation arena too!