5 Replies Latest reply on May 12, 2011 5:09 AM by crtopher

    GTRR

    eibcga

      Title

      GTRR

      Post

      Hello all,

      I have finally been able to make good use of the Go To Related Record (GTRR) script step so it works as intended.  I had to learn the hard way.  I was able to replace a relatively long and inefficient script with a new more efficient script that only had a few steps making full use of GTRR.

      My GTRR script now works as intended, but I had to create a self-join table for it to work as I wanted it to.  I still scratch my head why it was necessary to make a self-join table when I thought the GTRR script step would do the job without it.  Can anybody shed some light on why?

      I attach my ERD.  What I wanted to do was for FMPA11v2 for Mac to (step 1) give me all related records from the LEDGER (aka transaction detail) table that match the specified Account record only in the ACCOUNT table.  Then, (step 2) of that found set from step 1, run GTRR again to give me all related records in the current found set of step 1 from the LEDGER table.  Step 2 did not work as intended unless I replaced LEDGER table with a self-join LEDGER_GTRR table.  Why was that necessary for it to work?  If I had not created a self-join table, then the found set from step 2 would be the exact same records as those found in step 1.

      Thank you and Happy Holidays!  

      Screen_shot_2010-12-12_at_12.15.10_PM.png

        • 1. Re: GTRR
          eibcga

          I figured out why I had the problem mentioned in my above post (I know… I realized I didn't give you enough information).  Each "journal entry" in the Journal table effects at least two or more accounts (since, in double-entry bookkeeping rules, an Account record can relate to more than one Ledger record (i.e., transaction detail), and one Ledger record can relate to only one Account record).  So, when I did a GTRR to an Account record, it creates a found set of all related Ledger records matching the active Account record.  Then, when I ran the GTRR script step the second time on that found set, it had only those records in the found set to work with (rather than all the records in the Ledger table to work with).  What I wanted it to do was to give me a new found set of all related records (of all the records that exist in the Ledger table) that related to the found set.  The script is:

          #The purpose of this script is to analyze the account distribution of all transactions in an Account record, Name record, etc.

          Set Error Capture [On]

          #Close GTRR window that was created the last time this script ran

          Close Window [Name: "GTRR"; Current file]

          #Show only related records in Ledger table that match the active Account record

          Go to Related Record [From table: "LEDGER"; Using layout: "LEDGER"(LEDGER)]

            [Show only related records; New Window]

          Enter Find Mode[]

          #enter transaction date range

            [Pause]

          Constrain Found Set[]

          #Show only related records in the Ledger table that match the found set

          Go to Related Records [From table: "LEDGER_GTRR"; Using layout: "Trial Balance" (LEDGER)]

             [Show only related records; Match found set]

          ---

          Is there another way to do this without having to create the LEDGER_GTRR self-join table, if so, any ideas?  Thanks.  The above script replaces a long, inefficient looping script I had previously when I wasn't quite familiar with GTRR.  I'm learning.

          • 2. Re: GTRR
            philmodjunk

            Instead of GTRR, you could also perform a find to pull up the same set of records. You can capture the appropriate values from your current record in variables, enter find mode, use set field to use the variables to specify criteria, then perform the find.

            It's a trade off: an extra Table Occurrence vs. a slightly longer script. If implemented correctly, the end results will be the same.

            • 3. Re: GTRR
              eibcga

              Thanks very much PhilModJunk, my previous script did exactly that (using set variable, set field, looping, etc.), but I found it inefficient, but it does provide another option.  Good to know there's more than one way to accomplish the same thing, each with their own +/-.  Thanks again.

              • 4. Re: GTRR
                philmodjunk

                No looping should be necessary if you construct the correct find request. As I read your earlier posts, you are finding all Ledger Records that all have the same value in _kf_trans_id as the current record. If you are use GTRR with the "match found set" option, you haven't indicated that.

                Even with that possible wrinkle, it's possible to avoid a loop, but the approach uses a separate layout and copy all records--so I'd go with GTRR in that case.

                • 5. Re: GTRR
                  crtopher

                  How did you add in that screenshot?