9 Replies Latest reply on Feb 8, 2015 8:04 AM by philmodjunk

    Join/Find using Repetition Field Change?

    fred@kca-inc.com

      Title

      Join/Find using Repetition Field Change?

      Post

      Was there a change in the latest release of FMPA to joins using repetition fields?  I used to be able to create a relationship between a global repetition field in one table to an index field in another table and get the records from the second table which match ANY of the values in any repetition of the repetition field in the first table:

      - Go to Layout table2Layout

      - Enter Find Mode []

      - Set Field [table1::gRepetitionField1; table2::IndexField1]

      - Perform Find

      Previously, I would get all records in table2 which matched ANY of the repetitions in table1::gRepetitionField1

      From what I could see, there were no changes in my script but the function changed.  I have fixed it by looping and creating find records for each repetition of table1::gRepetitionField1, but I'd like to know if this was a change I missed in the last update??

      Fred

        • 1. Re: Join/Find using Repetition Field Change?
          philmodjunk

          Don't know off hand, almost never use repeating fields and would much rather use a join table in place of a repeating match field like this.

          In cases where I don't want to use a join table, I use a single text field with the values separated by returns. Not only does this support a flexible number of values instead of a fixed number of repetitions, it allows me to work with a wide range of different tools for working with return separated lists of values from ExecuteSQL, to check box formatted match fields.

          But every developer has the right to design as they see fit so I ran a quick test. I was able to use FileMaker 13.0v5 on a Windows 7 system with a 5 repetition field to match to records in a related table. I could enter a different value into each repetition and the each value matched as expected.

          • 2. Re: Join/Find using Repetition Field Change?
            fred@kca-inc.com

            Thanks for the feedback.  I'm not sure if something changed on my end.  I couldn't identify anything.  Did you happen to try using the Find function to see if the Found Set included records matching all 5 repetitions?  It's not a big deal at this point since I've coded around it, but it would be nice to know if something changed on the FMP end.

            • 3. Re: Join/Find using Repetition Field Change?
              philmodjunk

              Why would you use Find when you have a perfectly good relationship to use for this? Go To Related Records should take you right to those related records.

              And you cannot, never could, specify search criteria in a field that is global--whether one repetition or many.

              It would not make sense to do so as a Global field has the same value (or array of values in repetitions) for every field in the table where it was defined....

              so what you show in your script only makes sense if the repeating field is NOT a global repeating field.

              • 4. Re: Join/Find using Repetition Field Change?
                fred@kca-inc.com

                Thanks Phil,

                That seems to be a great idea, but I'm having trouble getting it to work.  I have a table of all Globals.  I link a table of Groups to Globals with a Layout with the fields from Groups then execute the Go To Related Record and, basically, nothing happens.  If I do a Find with the same tables, it works fine.

                Globals::gField1---JOIN--->Groups::Field1:::::Groups::Field2

                Go to Related Record [Show only related records; From table: "Groups"; Using layout: "Groups" (Groups)]

                The goal is to create a "found set" of all of the Field2's where Field1 matches gField1.

                I've never used Go To Related Record before and I'm sure I'm doing something wrong.  I read your nice writeup on GTRR, but couldn't seem to get it to work.  Any suggestions?

                 

                • 5. Re: Join/Find using Repetition Field Change?
                  philmodjunk

                  Two things to check first. The GTRR step should be performed from a layout based on Globals to pull up a found set of records on a layout based on Groups, not the other way around. The full syntax for that might look like this:

                  Go To Related Record [Show only related records; From table: Groups; Using layout: "Groups" (Groups) ]

                  Basically, if you can put an unfiltered portal to Groups on a globals layout, GTRR will pull up a found set of the records shown in the portal, but on a layout based on the portal's table occurrence, not the layout's. (Unless this is a self join where both table occurrences are based on the same data source table...)

                  Next thing to check is whether you truly have matching values. That portal I just mentioned is a quick way to check to see if your relationship is working for you or not. (If I get a relationship that doesn't seem to be working, I often add an unfiltered layout temporarily to my layout as a quick check on whether the relationship is matching to the records the way I expected or not.)

                   

                  • 6. Re: Join/Find using Repetition Field Change?
                    fred@kca-inc.com

                    Thanks Phil,

                    Let me play this back.  I should go to my layout based on Globals then

                    Go To Related Record [Show only related records; From table: Groups; Using layout: "Groups" (Groups) ]

                    to show the Groups records that match the Global field?

                    I did not try that permutation.

                    • 7. Re: Join/Find using Repetition Field Change?
                      philmodjunk

                      That's what I said and that's how GTRR works. Think of it this way: If you can use an unfiltered portal to display records on your layout, GTRR can be used to pull up a found set of the records that appear in the portal on a layout based on the same table as the table on which the portal is based.

                      • 8. Re: Join/Find using Repetition Field Change?
                        fred@kca-inc.com

                        That is AWESOME!!!  I got it to work perfectly based on your instructions.  I wish I had been smart enough to ask about GTRR years ago.  This will allow me to recode many scripts and make them much faster. I have always been able to use relationships/joins with portals, but never figured out how to use them like this. New Toy :)

                        Thanks

                        Fred

                        • 9. Re: Join/Find using Repetition Field Change?
                          philmodjunk

                          Just keep in mind the limitations of GTRR as documented in that The Complete Go To Related Record that you referred to. You can seriously scramble data using GTRR if you have a script that uses GTRR to pull up a found set and then your script modifies the records it pulls up such as deleting records so some such. It's good practice to always check for either the presence of related records just before the GTRR or for an error code immediately after the GTRR.