11 Replies Latest reply on Feb 27, 2009 9:32 AM by philmodjunk

    Go To Related Record fails to update found set when there are no matching records.

    philmodjunk

      Summary

      Go To Related Record fails to update found set when there are no matching records.

      Description of the issue

      I've been puzzled by this one since scripts that I've converted from a FMP 5.5 version seemed to work as expected unless I "touched" the relationship definition in some way. I think I now have this figured out: The script step will fail to update the found set if the Go To Related Record [Show Only Related Records]: a) Specifies a layout by name and...b) There are no matching records in the related table. Instead of showing an empty found set, you get whatever state the found set was in prior to this step's execution. If I use a separate script step to go to the desired layout and then used "Go To Related Records..." with [Current Layout] specified, the script works.

        • 1. Re: Go To Related Record fails to update found set when there are no matching records.
          davidhead
             This is not so much a bug as a very important (and potentially disastrous) change in behaviour introduced in version 7. When there are no related records, FileMaker Pro does not switch layouts in a GTRR script step. This does generate an error (101) that can be trapped. So it is now important to test for errors or use one of the other techniques to test for related records before you GTRR.
          • 2. Re: Go To Related Record fails to update found set when there are no matching records.
            philmodjunk
              

            In that case, I recommend updating the Knowledgebase. I checked there first before posting here. The only entry I found was a reference to Filemaker 7 which promised that an update would be released to fix the problem. Apparently, this fix was never released and instead developers have been using the work around you describe, which is documented in the same knowledgebase article, ever since.

             

            It would also help to add a note in FMP's help file to document this as well. This behavior is not intuitive and debugging a script that has this issue can be a real "hair puller" until you figure out the the found set is not updating.

            • 3. Re: Go To Related Record fails to update found set when there are no matching records.
              TSGal

              PhilModJunk:

               

              The fix in 7.0v2 showed the related table to show zero records.

               

              I have also tried several times to duplicate your problem but have been unable to do so.  This is what I have done.

               

              1. I created a table (TABLEA) with two fields:

              Field1 (Text)

              Field2 (Text)

               

              2. I created a second table (TABLEB) with two fields:

              Field1 (Text)

              Name (Text)

               

              3. I created a relationship between the two tables with the key field being "Field1".

               

              4. I duplicated the layout for TABLEB, and left it the default name of "TABLEB Copy".  I removed "Field1" from the layout.

               

              5. I entered the following records into TABLEB:

               

              1 - TSGal

              1 - TSPenguin

              1 - TSDiva

              2 - PhilModJunk

              2 - uLearnIT

               

              6. In the TABLEA layout, I added a portal into TABLEB

               

              7. I entered the following records into TABLEA:

               

              1 - Stuff

              2 - More Stuff

              3 - Even more stuff

               

               

              8. I created a script that did the following:

               

              Go to Related Record [ From Table: TABLEB ; Layout: "TABLEB Copy" ]

               

              I execute this from record 1 and 2, and it works correctly.  I try this from record 3, and it does not take me anywhere, because there are no records in the related table.

               

              Let me know what I am doing differently than you, and I'll duplicate it here.  Once I am able to duplicate it, I'll make the recommendation to update the Knowledge Base article (#493).

               

              TSGal

              FileMaker, Inc. 

               

              • 4. Re: Go To Related Record fails to update found set when there are no matching records.
                philmodjunk
                  

                The following Script reproduces the issue 100% of the time for me: In this example, I will define a self-join relationship with a global field but it also works with two tables.

                 

                Create a table: (Test)

                Field 1: gCategory (Global Text Field)

                Field 2: Name (Indexed text field)

                 

                You might need to create a 2nd table just so that there is a layout that does not reference the above table. (all of the instances I've dealt with were multi-table files.)

                 

                Create a relationship, "Selected Category" That uses "=" to match the two text fields.

                 

                Compare the following two scripts:

                Go to Layout (Test)

                Go To related records "Selected Category", "Show only related records", <Current Layout>

                 

                This script works and consistantly produces an empty found set if gCategory contains text that does not match any records in the table.

                 

                The following script:

                 

                Go To Related Records "Selected Category","Show only related records", test

                 

                Fails to create an empty found set if gCategory does not contain a matching value. The found set is left in whatever state was current before the script executes.

                 

                I can email you a sample DB that illustrates this if you send me a way to do this.

                 

                 

                • 5. Re: Go To Related Record fails to update found set when there are no matching records.
                  TSGal

                  PhilModJunk:

                   

                  I have tried to set up the database exactly like yours.  Here is what I did:

                   

                  >Create a table: (Test)

                  >Field 1: gCategory (Global Text Field)

                  >Field 2: Name (Indexed text field)

                   

                  Done.

                   

                  I added the following records for Name:

                  TSGal

                  PhilModJunk

                  TSDiva

                   

                  In the gCategory field, I entered "Dummy"

                   

                  > You might need to create a 2nd table just so that there is a layout that does not reference the above table.

                  > (all of the instances I've dealt with were multi-table files.)

                   

                  Not necessary at this time. 

                   

                  > Create a relationship, "Selected Category" That uses "=" to match the two text fields.

                   

                  Done.  I created a relationship into the second occurrence of Test and matched the two text fields: Name = Name and gCategory = gCategory.

                   

                  I put a portal on the layout and showed both fields from Test 2.  Needless to say, all three records appear in the portal. 

                   

                   

                  >Compare the following two scripts:

                  >Go to Layout (Test)

                  >Go To related records "Selected Category", "Show only related records", <Current Layout>

                   

                  >This script works and consistantly produces an empty found set if gCategory contains text that does not match any records in the table.

                   

                  gCategory is the same for every record in the table.  It matches all records.

                   

                  >The following script:

                   

                  >Go To Related Records "Selected Category","Show only related records", test

                   

                  >Fails to create an empty found set if gCategory does not contain a matching value. The found set is left in whatever state was

                  >current before the script executes.

                   

                  Sorry.  I cannot reproduce.  I have one record in each portal. 

                   

                  > I can email you a sample DB that illustrates this if you send me a way to do this.

                   

                  I have sent you a private message (top of this page - right side - X Messages) with instructions where to send the sample database.

                   

                  TSGal

                  FileMaker, Inc. 

                  • 6. Re: Go To Related Record fails to update found set when there are no matching records.
                    philmodjunk
                      

                    This is looking more and more like a "bug". I've created and sent you a demo file and it reproduces the issue on my machine:

                    Microsoft Windows XP Professional Service Pack 2.

                     

                    However,

                     

                    It does not consistently fail. Simply by clicking in and out of fields on the layout can cause the script to work when it previously failed.

                     

                    Thanks for your quick response.

                    • 7. Re: Go To Related Record fails to update found set when there are no matching records.
                      TSGal

                      PhilModJunk:

                       

                      I received your files.

                       

                      I assume you are talking about the value "Purple", which is not a related record.

                       

                      Like "uLearnIT" reported, a Go to Related Record will not switch layouts if there are no related records.  That is why your second script fails for the value "Purple".  All other values work fine where there are related records.  You can either check for related records by:

                       

                      If [ Count [ SelByCategory::Items ) > 0 ]

                       

                      or, you can trap for error 101 after the Go To Related Record step.

                       

                      TSGal

                      FileMaker, Inc. 

                      • 8. Re: Go To Related Record fails to update found set when there are no matching records.
                        philmodjunk
                          

                        Please note:

                         

                        a) There was no layout to change to--there was only one layout in the sample file that I sent to you. One script uses the current layout option and works as expected. The other script specifies this layout by name and does not consistently work as expected.

                        b) All the documentation that I've been able to find would lead one to think both scripts should work in identical fashion. That's why I've previously suggested documenting this behavior both in the Knowledge Base and in the On Line help.

                        c) One script works consistently. The other script did work part of the time. At other times it does not. I did not discover this until I set up the example database and tested these "stripped to bare essentials" versions. The sample file used two different triggered scripts--each attached to different copies of the same global field that used the same value list to assign a value to one side of the relationship and used Go to related... to bring up any related records. I found that the problem version, which specifies the current layout by name, DID work, but not always. It depended on whether I simply selected "purple" from the menu or clicked in and out of other fields first. It looked like the underlying data structures that manage the relationship weren't always updating correctly. Given this inconsistent behavior, please take a closer look.

                        • 9. Re: Go To Related Record fails to update found set when there are no matching records.
                          TSGal

                          PhilModJunk:

                           

                          a) It may not appear that you are going to a different layout.  It just happens that the related layout is the same as the original layout.  Again, if there are no records in the related table, the Go to Related Records script step will not execute.  In the first case, you are actually switching to the related layout where no records exist before executing the Go to Related Records, which takes you back to the first layout.

                           

                          b) No, both scripts do not work in identical fashion, as defined above.

                           

                          c) Yes, the first script will work consistently.

                           

                          Perhaps you want to rewrite the script as follows:

                           

                          If [ Count ( SelByCategory:: Items ) > 0

                             Go to Related Record [ Show only related records; From table: "SelByCategory"; Using layout: "GotoRelatedDemo" ]

                          Else

                             # if you want no records to show 

                             Omit Multiple Records [ No dialog; Get (FoundCount) ]

                          End If

                           

                          That is, check for the number of related records.  If there are some, then execute the Go to Related Record step.  Otherwise, if you don't want any records to show, omit the found set of records so none appear, sin Go to Related Record step will not execute when no related records are found.  You can easily insert a dialog box if desired.

                           

                          Another option is to Set Error Capture [On] and after the Go to Related Record script step, check for LastError = 101.  You can then decide what to do at that point.

                           

                          TSGal

                          FileMaker, Inc. 

                          • 10. Re: Go To Related Record fails to update found set when there are no matching records.
                            davidhead
                              

                            TSGal wrote:

                            Perhaps you want to rewrite the script as follows:

                             

                            If [ Count ( SelByCategory:: Items ) > 0

                               Go to Related Record [ Show only related records; From table: "SelByCategory"; Using layout: "GotoRelatedDemo" ]

                            Else

                               # if you want no records to show 

                               Omit Multiple Records [ No dialog; Get (FoundCount) ]

                            End If

                             


                            Just make sure that you are on the first record when you execute that Else bit ;) Otherwise you will not omit all records in the found set. So write:

                                    Else

                                      # if you want no records to show 

                                      Go to Record/Request/Page [First] 

                                      Omit Multiple Records [ No dialog; Get (FoundCount) ]

                                    End If 

                             


                            • 11. Re: Go To Related Record fails to update found set when there are no matching records.
                              philmodjunk
                                

                              Sigh....

                              There are several overlapping issues here. I really, truly get what you all are saying. But it seems like TS Gal is missing two of my "user feed back" items. Please note that I am quite aware of the work arounds.

                               

                              a) The help file documentation for Go to related record is incomplete and should be updated. "Show record using layout lets you choose a layout in the current file that will be used to display the related record" is the total documentation for this "feature". There is no information telling the developer of what will happen when there are no matching values.

                               

                              b) While creating the demo file to send to TS GAL, I discovered that Go to related records with a layout specified by name WILL work, but only part of the time. On my system, performing that script step will update the found set to display zero records and other times it will not. I've described this in my previous post. I respectfully submit that this intermittent behavior is not a good thing and should be investigated.

                               

                              For those reading this post, here's a description of the demo file I created and sent in so that you can test it for yourself:

                              1 Table, two text fields, One self-join relationship links the two fields as standard "=" matches.

                              1 text field Item in which I entered several color names "red", "blue" ... etc.

                              1 Global field which I placed 3 copies in the header of a single list view layout.

                              I created 2 scripts with a singe script step: Go To Related Record. Version 1 specified "current layout". Version 2 specified the list view layout by name.

                               

                              I defined a value list of color names that included one color (Purple) that was not entered in the 3 or 4 records that I had created. I defined two of the global fields in the header as drop down lists and defined script triggers so that each one performs a different script. I left the third field as an edit box.

                               

                              What I found was that if I manually entered a non matching color name into the edit box, clicked in and out of several other fields and then manually selected the 2nd script from the script menu, it would often work. If I selected the non matching color from the drop down menu of the field which triggered this same script, it would not work. If I then clicked in and out of fields and re-selected the non matching value it would often work.

                               

                              This is the "bug" behaviour I am asking FMP to examine. This is not exactly what I originally reported, but rather what I have encountered in the course of responding to TS Gal's report that she couldn't replicate the problem.