1 2 Previous Next 18 Replies Latest reply on Apr 10, 2015 7:07 AM by Magnus Fransson

    gtrr wrong record no error

    user12837

      I have a GTRR statement which goes to the wrong record on the specified target layout and TO without triggering an error. 

       

      I get around this problem by testing to see if the selected record in the target layout is the right record.  I use a Find to go to the right record if the GTRR fails, but that seems preposterously silly.

       

      The left side of the relationship is a global field in a local table.  The other is a text field in an  ESS (SQL Server) table.  The text field is not the unique key used by ESS to attach the table.

       

      Has anyone else seen this?

        • 1. Re: gtrr wrong record no error
          ch0c0halic

          GTRR to an ESS table won't work.

           

          Look at the TOG. The bar at the end of the relationship going to the ESS TO indicates it is a one way link from the ESS to the FM table.

           

          See the Help system under "Working with the relationships graph". (Enter "relationship" in help search, no quote).

          • 2. Re: gtrr wrong record no error
            matthew_odell

            Is it going to another record that is part of the related set, or to some completely different record altogether (that isn't related)? Are you calling any other script steps before the GTRR like Commit Record, or is there an OnLayoutExit trigger on the layout?

            • 3. Re: gtrr wrong record no error
              user12837

              Hi Matthew,

               

              > Is it going to another record that is part of the related set, or to some completely different record altogether (that isn't related)

               

              It goes to the last record of the related set.

               

              >Are you calling any other script steps before the GTRR like Commit Record, or is there an OnLayoutExit trigger on the layout?

               

              There are no steps before the GTRR, and no triggers fire (per the debugger).

               

              Thanks,

               

              Tom

              • 4. Re: gtrr wrong record no error
                user12837

                I use GTRR all the time, going either way between two local tables, two ESS tables, or one local table and one ESS table.  This is the first time I've ever experienced a problem.

                 

                It's true, or course, that I might have been lucky -- like flipping a coin a thousand times and getting ninety-nine heads, followed by a tail.

                 

                Thanks,

                 

                Tom

                • 5. Re: gtrr wrong record no error
                  user12837

                  Whoops!  I meant getting 999 heads followed by one tail.

                   

                  (I must be getting arithmetically challenged in my old age.)

                  • 6. Re: gtrr wrong record no error
                    matthew_odell

                    Is the relationship sorted? Is there any chance the field or fields that it's using for the primary key aren't actually unique?

                    • 7. Re: gtrr wrong record no error
                      user12837

                      The relationship is not sorted.  The primary key field is unique.

                       

                      I should have mentioned that this GTRR statement goes to the correct layout all the time, and to the correct record on that layout most of the time.  There is no error when it goes to the wrong record.  It obviously thinks that it’s done everything correctly.

                       

                       

                      -          TT

                      • 8. Re: gtrr wrong record no error
                        siplus

                        If I am getting this correctly,

                         

                        if valuecount(List(yourrelation::yourdestfield) > 1, you have a problem. GTRR won't give an error, as there is a record to go to. In fact, more than one. Build on that.

                         

                        Keep in mind that going to a special layout where you only have 1 field and doing a quickFind is freaking fast, even with 1 million records.

                        • 9. Re: gtrr wrong record no error
                          CarstenLevin

                          Hi ch0ch0halic,

                           

                          You write that Go To Related Record does not work with external tables (ESS) from MySQL, MS SQL or Oracle.

                          Am I missing out on something here. I believe that I have used this method many times with ESS. Am I wrong?

                           

                          Best regards

                           

                          Carsten

                          • 10. Re: gtrr wrong record no error
                            matthew_odell

                            Actually I probably should have asked, this is being called from a portal, right? Is this a button in the portal calling this script, or something else?

                            • 11. Re: gtrr wrong record no error
                              ch0c0halic

                              In all my attempts to make this work none have been successful. Others disagree and say they have made it work. Maybe I'm doing it wrong?

                               

                              However, I've tried in several different systems and I've not been able to get a GTRR from a FMP table to an ESS table to work reliably and in some case it doesn't work at all, no related records are found. Yet using the same relationship a GTRR from the ESS table to the FMP table works.

                              • 12. Re: gtrr wrong record no error
                                Fred(CH)

                                Look at the TOG. The bar at the end of the relationship going to the ESS TO indicates it is a one way link from the ESS to the FM table.

                                 

                                Good catch ch0c0halic !

                                And it is probably due to the fact that FileMaker cannot index a field on ESS.

                                • 13. Re: gtrr wrong record no error
                                  gdurniak

                                  The External SQL Sources PDF implies that GTRR should work,  but goes on to show how a Relationship could fail


                                  http://www.filemaker-solutions.be/IMG/pdf/techbrief_ess_en.pdf 


                                  It is also a known issue,  that a GTRR Query, in the background,  might get too large,  and fail


                                  https://community.filemaker.com/thread/62700 


                                  It would probably be safer to do the Find


                                  It would also pay to verify your indexes on the SQL side


                                  greg 


                                  > I have a GTRR statement which goes to the wrong record on the specified target layout and TO without triggering an error

                                  • 14. Re: gtrr wrong record no error
                                    rrrichie

                                    I have had similar issues.  One was caused by a "snitch" somewhere... In that case making a new field/column and copying the data solved the problem.  Suspect a corruption in that particular case.

                                     

                                    The other is "caching" I suspect...   Sometimes new entries in the ESS table just don't show up yet.  When doing a find they do.  So for example a new record is added externally (it is committed on that database (mySQL))  Then when I go to the layout with the ESS and go to the last record it won't show.  Even after minutes... Clearing Cache etc doesn't trigger the refresh for the ESS table.   When I do a find for that particular record (cause I know the id e.g.) it immediately shows up.  If I first try to find something else it won't show up either (with the goto last record step)  (that I found peculiar by the way).  Anyways solved by searching records after a certain date / timestamp. Then I have the new ones for sure.

                                     

                                    So all in all when working external ESS Tables I always do a double check if I'm on the right record...

                                    1 2 Previous Next