Problem with GTRR : resolution and other useful stuff

Discussion created by miqaels on Jan 15, 2015

I had a puzzle come up that I finally managed to solve after quite a lot of research. There are lots of problem discussions about GTRR and reading them I found hints that led me to the solution but no clear explanation of what was going on. So, in the hope of helping others who my hit the same issue I offer this with apologies to those of you who are so knowledgeable that you say “of course, that’s obvious!”


Background: I set up a database for my wife to keep track of her coaching clients. There is a table for demographics, a table for sessions in which she keeps track of who, what and when for her one-on-one sessions and a table for payments. All related by a Client ID; very straightforward. The next table was one for “Issues” e.g. stress, work, smoking etc. etc. Rather than try to incorporate that into the Demographic record, I created a table with each record holding an issue (selected from a value list) and the Client ID and used portals to enter and display that data on various layouts. So far so good. Next, I wanted to create a way for her to assemble an E-Mail that could go to a set of clients who had one or more issues in common. In order to assemble such a set, I found myself delving into several less-documented features of FileMaker and that was where the tricky problem finally arose.


SET-UP: I created a global field, issues-list, that I displayed on a layout as check-boxes populated from the Issues value list. NOTE: The nice thing about displaying a field this way is that the underlying value of the field at any moment is a <CR> delimited text field of all the values that are checked currently. When the user checks one or more values, a script converts the <CR> delimited text into a variable containing comma delimited text with each value in apostrophes: e.g. ‘stress’,’work’,smoking’. This is passed to an Execute SQL “SELECT DISTINCT” command to load a global field (“some_clients”) with a <CR> delimited list of the Client ID values for each client possessing any of the issues that were checked.


So far so good - I have a list of unique Client ID’s in a global field and I just need to use GTRR to turn it into a found-set of the relevant client records from the Demographics table. And indeed it worked beautifully on my set of made up data. What could go wrong?


PROBLEM: I created a run-time version and gave to to my wife who loaded in all her historical demographic data but only loaded sessions, payments and issues for her current clients. When I checked the working of the issue selection routine it was broken! The found set was the complete client list, no matter what issues were chosen as the selectors. It was easy to check that the script steps leading up to the GTRR were all working properly and loading fields and variables as designed but GTRR was not working. I read many discussion threads on GTRR not working and none quite explained what was happening although, as I said at the beginning, I got glimmers of an idea. So here is what the problem was and I hope its resolution helps others who might fall into the same trap.


EXPLANATION: The global fields for the issues list and for the list of Client ID's generated by the SQL command were fields in the Issues table.  The 'some-clients' field was related to Client ID in a second instance of the Demographics table. But my layout on which I put my checkbox field was assigned to the Demographics table (because I was using it for the Email function and it just worked out that way). Now here is the hidden trap number one. The layout used global fields only BUT there is ALWAYS a specific active record even if no field from that record is shown - so it easy to forget. Trap two is that if that client had not had any issues assigned to him, there is no record in the Issues table corresponding to that Client ID and that makes the relationship between the Issues table and the Demographics table fail; GTRR does not work. I finally realised this was the problem and proved it by clicking on the record number slider until a demographic record that did have an issue recorded was the active record. Then the script worked as it was supposed to. Note that the issue in the demographic record did NOT have to be one of those selected and the active demographic record for the layout did NOT have to be one of those in the eventual found set. It just had to have a value in the related field (Client ID) that was also present in some record in the Issues table.


SOLUTION: The solution was to recreate the layout for choosing the issues assigned to the Issue table. I also made it more convenient by splitting my script into two - the first part does the SQL step and is triggered by object modify on the checkboxes. That allows me to display dynamically the number of clients who have any one of the issues checked. The second script contains the GTRR and is activated by a button that brings up the list of selected clients from the demographic table layout. The Email function I left in the original layout. The GTRR always works  now because it is triggered by a layout that, by definition, has an active record with a Client ID that is related to the Demographics table.


It all works - and I hope this rather lengthy description is helpful to anyone who is struggling with unexplained or apparently random behaviour of GTRR.