AnsweredAssumed Answered

The Complete Go To Related Record

Question asked by philmodjunk on Sep 15, 2009
Latest reply on Jan 26, 2014 by carojo

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.

Outcomes