12 Replies Latest reply on Feb 23, 2011 9:16 AM by TSGal

    No error thrown on GTRR

    LaRetta_1

      Summary

      No error thrown on GTRR

      Product

      FileMaker Pro

      Version

      FMPA9 and FMPA11

      Operating system version

      Windows XP Professional SP2

      Description of the issue

      There are times we use a GTRR and then want to delete the found related records.  However, if something has happened that the child is no longer valid (foreign key changed to unstored, indexing turned off or index for the child table has trashed), script does NOT produce an error code.

      Steps to reproduce the problem

      File with two tables (Parent and Child).  Set the child key to unstored or set index to none (without automatic index).  This would be the situation also if the index in the child table corrupts.

      Script with error capture on then GTRR to child table.  Once there and if no error, delete all records, something like:

      Script:
      Set Error Capture [ on ]
      GTRR to child table
      If [ Get ( LastError ) ]
      Go to Layout [ original layout ]
      Halt Script
      Else
      Delete all Records
      End If

      Expected result

      There should be a error generated.  Instead it produces a message of “This operation could not be performed because one or more of the relationships between these tables are invalid.”

      Error should be generated so script can be controlled.

      Actual result

      There is only an OK button.  User clicks okay and the SCRIPT CONTINUES and deletes the records in the parent table instead.

      Exact text of any error message(s) that appear

      “This operation could not be performed because one or more of the relationships between these tables are invalid.”

      Configuration information

      This uncontrolled message will also cause a server hang or robot hang because nobody is there to press OK.  FMS11 allows time out on scheduled scripts if they run too long but that is not the issue.

      Workaround

      All I can come up with is to check for related records as well, something like:

      If [ not IsEmpty ( child::primarykey ) ]
      … continue script
      Else
      … stop script
      End If

      This may already have been reported.  I have given up on trying to keep track of all the unfixed bugs.  This is very serious because we had an index fail in the child table and it deleted all of the parent records even though I had generic error trap on to protect from ANY failure.

      The other option may be to check that the script moved to the other layout but that means hard-coding the layout in the script and that should not be necessary.

      AN ERROR SHOULD BE THROWN ... at least one of these:

      101 record is missing
      103 relationship is missing
      107 Index is missing
      110 related tables are missing
      401 no records match the request
      407 one or both match fields are missing (invalid relationship)
      415 one ore more of the required related records are not available
      510 related value is empty or unavailable

        • 1. Re: No error thrown on GTRR
          philmodjunk

          Absolutely. At the very least the error dialog should include a cancel script option so you can cancel the script before the script trashes the system for you like some other error dialogs provide. As it is, FileMaker tells you that your brakes have failed, your on glare ice sliding toward a cliff but with no way to avoid the crash and burn...Frown

          I would think putting this test before the GTRR would also be an acceptable work around:

          If [ not IsEmpty ( ChildTable::ID ) ]
              GTRR here

          Or you can use a get function to check the name or number of the current layout after the GTRR. (If the current layout didn't change the GTRR failed.) I use this option whenever I need to use GTRR with the Match Found Set option as Get (LastError) doesn't work there either.

          • 2. Re: No error thrown on GTRR
            LaRetta_1

            I like your analogy. Smile

            Phil said, "I would think putting this test before the GTRR would also be an acceptable work around:

            If [ not IsEmpty ( ChildTable::ID ) ]
                GTRR here"

            That's exactly what I said ... test for related and then GTRR if test passes.

            I  disagree about the GTRR failing with Match Found Set.  The difference  is that you need to test for 401 instead of 101.  If the first related  record doesn't have a match, it will throw 101.  It will only throw 401  if there are NO related records matching any of your parent record set.

            But  all of this is moot if a test for ANY ERROR can't stop the break.  When  I am deleting records, I had switched to using If ( Get ( LastError )  so I could eliminate possibility of deleting parent records ... which  should catch ALL issues such as corrupted layout etc.  I didn't imagine  that something as serious as this wouldn't throw an error AND/OR  wouldn't provide an out for the User.

            • 3. Re: No error thrown on GTRR
              philmodjunk

              Thanks for the correction on the 101 vs. 401 error. I must have checked for just 101 the last time I used GTRR with the matching found set option and jumped to the wrong conclusion. Embarassed

              • 4. Re: No error thrown on GTRR
                LaRetta_1

                This would be a natural mistake.  In fact many people only check for global "Get ( LastError )" which wouldn't be what they want either.  Sometimes one may WANT to have the step fail if ANY parents lack children in which case 101 is the proper trap.

                I use 401 for match found set unless I'm following with Delete All Records.  And that's when it bit me in behind ... FM generates NO error. What a worthless message it produces!  I truly hope this is fixed quickly but it's been around since vs. 9 so I don't hold out hope.

                And of course, if someone accidently changes a child key to unstored (by either adding a test which includes a related or global field or by changing the indexing to none), FM doesn't warn them that it is being used in a relationship when they exit Field Definitions.  Since relationships go both ways, FM wouldn't know that it wasn't meant to be the unstored 'parent' side so I can see how FM wouldn't know to throw a warning dialog.  Although value lists throw errors properly so I would think this situation would warrant a warning such as, "this is unstored but used in a relationship.  If this is the child side, it will fail."

                Anyway, scripts should allow trap of invalid relationship and there are several error codes which would easily fit this situation.

                • 5. Re: No error thrown on GTRR
                  LaRetta_1

                  I realized that testing for a child record won't work if ‘match found set’ is required because that is based upon only the current parent record's relationship so it doesn't help  if we need match found set.

                  We cannot grab the parent layout number (into variable) and then test that the GTRR worked by basing it on a different layout because it will be too late ... at that point, the dialog will have popped up and hung up the script and the script will finish (and run on the parent table instead) before testing whether the layout switch was successful.

                  Until this is fixed, I’ll test for related record using not IsEmpty ( Child::ChildID ) if matching on current record.  And I’ll write multiple parent IDs to a global and test the relationship same way, checking for related records first.  In truth, it is faster to write IDs to global and ‘match current record’ than match found set anyway.  But it means unnecessary global and relationship when it should work right to begin with.

                  Regardless, if the relationship is invalid and you GTRR, it can break and script will not throw an error.  I hope we can get an official response on this issue; simply an error MUST be thrown. 

                  • 6. Re: No error thrown on GTRR
                    LaRetta_1

                    Any response from FMI?  I would at least like to get this acknowledged as a bug so customers might have a chance to protect themselves from it.

                    • 7. Re: No error thrown on GTRR
                      TSGal

                      LaRetta:

                      Thank you for your post, and I apologize for the late reply.

                      I am unable to replicate the problem.  This is what I have done:

                      1. I created a new file, Test.fp7.  In the "Test" table, I created the following two fields:

                      ID (Number)
                      Name (Text)

                      2. I created a new table, "a", with the following fields:

                      ID (Number)
                      Music (Text)

                      3. In table "a", I added the following records:

                      1 - The Beatles
                      3 - The Rolling Stones

                      4. In table "Test", I added the following records:

                      1 - TSGal
                      2 - PhilModJunk
                      3 - LaRetta

                      5. I created a Relationship between table "Test" and table "a" based on ID = ID

                      6. On the layout for "Test", I placed a portal into "a" showing the Music field.

                      7. I then added another field to the Test table named "Error" of type Text.

                      8. I created a script with the following steps:

                      Set Error Capture [ On ]
                      Go to Related Record [ From table "a" ; Using layout "a" (a) ]
                      Set Field [ Error ; Get (LastError) ]

                      9. In the first record of "Test" (TSGal), I executed the script, and Error displays 0 (zero).

                      10. In the second record of "Test" (PhilModJunk), I executed the script, and Error displays 101.

                      11. In Manage Database, I removed the relationship between "Test" and "a".

                      12. In the third record of "Test' (LaRetta), I executed the script, and Error displays 103.

                      I've tried a few other tests, but I always get an error message.  I've tried this only with FileMaker Pro 11.0v3 on Windows XP and Mac OS X 10.6.6.

                      Please let me know what I'm doing differently than you so I can try to replicate the error.

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: No error thrown on GTRR
                        philmodjunk

                        In the GTRR step, did you specify "match all records in current found set"?

                        • 9. Re: No error thrown on GTRR
                          TSGal

                          PhilModJunk:

                          Got it.  I had Match current record only.  When I set it to "Match all records in current found set", the error is placed int he first record of the found set (since a match exists in the first record).  That is, in Step #10, when you execute the script from the second record, the Error field is populated with 101 for the first record (TSGal).  I see that this error cannot be trapped in the current record, but I do get the Get (LastError) = "101".

                          When the relationship is removed in step #11 and script executed in step #12, I do not get the error “This operation could not be performed because one or more of the relationships between these tables are invalid.” that LaRetta reported.

                          TSGal
                          FileMaker, Inc.

                          • 10. Re: No error thrown on GTRR
                            philmodjunk

                            On my windows XP system, I get "This operation could not be completed because the target is not part of a related table", when I delete the relationship.

                            Hmmm, how could the target be "part of a related table"? seems like  a strange way to phrase this when the target either is or is not a related table.

                            • 11. Re: No error thrown on GTRR
                              LaRetta_1

                              Thank you for following up on this ...  

                              The match fields make no difference in this problem.  You don’t need to specify a match option for GTRR to function.  This error occurs no matter the ‘result options’ specified in a GTRR. The relationship was based upon Main::pk_customer_ID = Child::fk_customer_ID, which were standard data number fields with indexing minimal and unchecked to ‘automatically create indexes’ which is the regular setup for primary key relationships. 

                              The User got the error “This operation could not be performed because one or more of the relationships between these tables are invalid”.  When the User said ok to the message (only option for the user) the script continued and no error was thrown even though I had error capture on and tested for any error.  I knew it had to be a trashed index (it happens on FM sometimes) so I rebuilt the index using Recovery Advanced Options with only ‘rebuild field indexed’ checked.  The problem was solved in this case. 

                              I  did not know how to trash an index to replicate the test but it wasn't necessary.   I realized that, if the child side loses its index (for whatever reason) then it will cause this same break.  See link. 

                              http://www.4shared.com/file/3EGI5nOw/GTRR_break.html

                              It is very dangerous even if an index doesn’t trash.  What if the Developer doesn’t know that they should label their fields so they are clear that it is used as (or in) a key for a relationship?  If Category is used in a multikey relationship, I modify the name to  Category_x so I know it needs to remain indexed but does everyone?  Always?  If one of the field's indexing is turned off or the field is changed to calculation unstored, the key will break and when we exit the field definition, FM doesn’t tell us that the field is used in a ‘child side’ key and thus should remain indexable – FM probably couldn’t know (especially if a table occurrence resides in another file).

                              Bottom line … this MUST throw an error just as any problem must throw an error; that is the only way to protect from these issues.  :^)

                              BTW, if you re-read my original post, I clearly said it was indexing issue and to replicate it, you would need to remove index on child side key.  I am aware that removing the table occurrence, removing the link between the tables and other issues would produce proper errors; it is only when the child key loses its index that an error isn't thrown.

                              • 12. Re: No error thrown on GTRR
                                TSGal

                                LaRetta:

                                Thanks for the sample file.  I am able to replicate the problem on both Mac OS X 10.6.6 and Windows XP (SP3).  I have sent the file along with your description and my notes to our Development and Software Quality Assurance (Testing) departments for further review and confirmation.  I will keep you posted as information becomes available to me.

                                TSGal
                                FileMaker, Inc.