8 Replies Latest reply on Feb 6, 2013 11:52 AM by BrianMoylan

    finding next group of records



      finding next group of records


           I have a series of buttons in my database, the usual, "next record", and "previous record".

           What I would like to do is have a button that finds the next "group" of records. Specifically, there is a field in the records that identifies the group... ie "group1", "group1a", "group4" etc (note this is for clarity, the group names are not simply ordered numbers).

           I'd like to make a button that finds the next record where the group name changes, effectivly allowing you to jump from group to group. There may be as many as 500 records in the same group, ie the search will have to root through that many records before getting to a record where the field "groupname" changes.

           Thanks very much!


        • 1. Re: finding next group of records

               Sorry, I should not have used the word "find" in my question.

               I don't want to be left with a found set of records that belong to the next group. I simply want to "go to" the next record where the group name changes.

               Hope that is clearer. Sorry bout that.



          • 2. Re: finding next group of records

                 "the next record where the group name changes" is really fuzzy. Performing a find or changing the sort order will change what represents the "next record where the group name changes". I'd look for some other way to specify the "order" to your groups--such as a related table where you could have one record for each group name. This would make selecting the previous or next group name much faster and predictable.

                 If that sounds possible, let me know and I'll post a more detailed example of how to use that related table.

            • 3. Re: finding next group of records

                   Thanks PhilModJunk.

                   I'm a decent, but far from expert Filemaker user. The database I've built has some 4000 records in it and is in use by my client. The idea of splittig the data into tables is not wildly appealing, however I'm game to try if there's no easier solution.

                   I've attached an image of the record interface.

                   To make matters a little more complicated, the "kitname" which specifies the group is a combination of 2 fields, the 2 I've circled. "Kit" and "kitnumber" are added together to make a ktname, in this case, of AEJ2B. There are about 450 records with those fields filled with the same date, "AEJ" and "2B". At record 451, it becomes "AYR" and "3A" for a kitname of "AYR3A".

                   The magical button I'm looking for would take us to that record, where the new kitname is AYR3A.

              • 4. Re: finding next group of records

                     I am not recommending that you split any data into tables. I am suggesting that you add a new table, if you don't already have one, where you have one record for each 'kit name' where you can both list these groups and specify the desired order. Such a table could, in addition to making things faster and easier here, make it much easier to manage your kits. Your existing data would stay right where you already have it.


                          At record 451, it becomes "AYR" and "3A" for a kitname of "AYR3A".

                     The point that I am trying to make is that you can't guarantee that this will always occur at "record 451". Change the sort order and it can be that this "change" happens at a completely different record number. It is possible to set up a looping script that loops through a found set consisting of all the records in the table until the kit name changes on a different layout (that uses a different occurrence of the same table) or in a different, hidden window, then capture the value in a variable, return to the original layout/window and perform a find, but for large sets of data, that looping script could result in a noticeable delay between clicking the button and seeing your found set change to bring up the next group of records.

                     Going to a table of kit names and just accessing the preceding or next record in that table avoids the loop and with a the right relationship, Go to Related Records from the Kit Names table can pop up a found set of records for that kit in one step.

                     Also, a layout based on such a table of kit names could list the records in a portal and then the standard go to next record script step can move you through your kits one kit at a time.

                • 5. Re: finding next group of records

                       That sounds reasonable, thanks. I have been trying to make a looping script that searches and there is a noticeable delay.

                       I think I can make a new table and populate the records with the kitnames. You've already been really helpful, but I'm wondering if you could quickly lay out the basics of a script (I assume that's what I would use) that would go to the related table, go to the next record, then go back to the main table and park on the first record of the new group...

                       Thanks so much for your help!

                  • 6. Re: finding next group of records

                         First, you can populate your new table from your existing table if you import all records from the original to the new, but only after first setting a "unique values" validate always setting on your kit name to filter out duplications.

                         Say you have this relationships between your original table and the KitNamesTable:

                         KitNames::__pkKitID = OriginalTable::_fkKitID

                         Then this script can move you to the next group of records on a layout based on OriginalTable:

                         Freeze Window
                         Go To Layout ["KitNames" (KitNames)]
                         Go to Record/RequestPage [Next]
                         Go To Related Record [Show only related records; From table: OriginalTable ; Using layout: "OriginalTable" (OriginalTable) ]

                         Simply by varying the parameter in the 3rd line, you can get scripts that take you to the previous, first or last groups of records.

                         Note: scripts that change layouts can trip a number of different script triggers. You may need to take steps to disable such trigger controlled scripts when using a script like this in order to keep the trigger performed scripts from interfering.

                    • 7. Re: finding next group of records

                           Thanks very much Phil... tis sounds like ym answer. I'll try it today and send my results. I very much appreciate your taking the time to help.


                      • 8. Re: finding next group of records

                             This works great... there's was only one issue which I seem to have solved...

                             After running the "next kit" script a few times, the related table was parked on, say the 6th group. Then if I went back to record 1 in my original layout and ran the script again, it went to group 7 instead of group 2. So instead of just "goto layout kitnames" I used the "goto related record" script step twice, once to go to the kitnames layout and then once to get back. This seems to solve that issue.


                             Thank you Phil!!