GTRR – Checking Whether Records Exist

Go To Related Record is a wonderful thing, but not without its pitfalls.

One thing that’s tripped me up (but which isn’t entirely surprising) is that if there are NO related records, you go nowhere.  Even if you’ve asked for a new window in which to display the related recs, the window is not opened – you just stay with the source table in the original window.  So it’s vital to be able to test for the presence/absence of related recs, before deciding how to carry on.  To take an extreme example, following a fruitless GTRR with a “Delete all records” would have especially unwelcome consequences (not that I would do anything so stupid, of course….).

If you’re only interested in records related to the CURRENT record, the simplest and most straightforward way to determine whether related records exist in a related table is not to use GTRR for the test, but rather to check for a value in a related field, using “IsEmpty”.  The field that you test must be in the related table and, of course, it must always contain a value – a key field being the obvious candidate, especially the field which is used to establish the relationship between the tables.

But the isempty test is only useful in the context of the current record, and is therefore insufficient when the GTRR is matching ALL records. To establish whether the GTRR succeeded when matching all records, there are various options.  One is to test the layout number before and after the GTRR step.

set variable [$LayoutNumber ; Get(LayoutNumber)]
GTRR
if ( Get(LayoutNumber) = $LayoutNumber )

— we didn’t move

Another way of testing for the presence of related recs by using error codes returned by GTRR.  But things aren’t straightforward here, especially when “matching all records in current found set”.  Testing for an error code of “0” is not good enough, because GTRR returns “101” even when there ARE related recs.  “101” means that not ALL recs in the current found set have a related record in the destination table, but you still get taken to the records that DO exist.

Error “401” is more conclusive,  meaning that NONE of the current records has a related record.

So, in summary:

  • Error code “0” means that EVERY record in the current found set has related record(s).
  • Error code “101” means that ONE or MORE records in the current found set have related records, but some don’t.
  • Error code “401” means that NONE of the records in the current found set have related records.
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s