6 Replies Latest reply on Apr 24, 2014 12:33 PM by pinchvalve

    Constrain a set based on a record, then show all, then constrain a set based on next unique record.

    pinchvalve

      Title

      Constrain a set based on a record, then show all, then constrain a set based on next unique record.

      Post

           I'm using generic records for clarity, but imagine a table that includes ID_1>, ID_2 and ID_3 records.  

           I constrain by ID_1 when I enter my Print Layout.

           I then want to constrain that Found Set by each UNIQUE number in ID_2.  

           So out of 100 records, the first 50 have a "UUID1" as their ID_2, the next 30 have "UUID2" and the last 20 have a "UUID3")

           I need to set the first ID_2 record as a local variable, constrain by it and then print.  (This is where I have gotten to so far) 

           Then I need to show all ID_2 records again, grab the next Unique ID_2 number, and constrain by that, then print. 

           Then grab the next UNIQUE UD_2 record, and so on until the end of the Found Set.  

            

           Does that make any sense?  Anyone have any thoughts?  Every example I come across doesn't work for one reason or another.  Nothing I can find fits this scenario.  

        • 1. Re: Constrain a set based on a record, then show all, then constrain a set based on next unique record.
          philmodjunk
               

                    I constrain by ID_1 when I enter my Print Layout.

               Is the part that concerns me here. Are you really constraining the found set to just one value in ID_1 or are you actually pulling up all records with a particular value in ID_1? It makes a very key difference here in what will work. If you constrain a found set for ID_1 = 555, then there could be other records in the table with ID_1 = 555 that were not in the found set and thus were not included in the found set produced by that constrain. But if you perform a find for ID_1 =555 then all records with that value are in the found set.

               With the second option, I can think of two different methods for putting a list of unique values from ID_2 into a field or variable so that you can pull up found sets of records for a given value of ID_1 and ID_2 to get your different groups of records. But if you are truly constraining the found set on a value in ID_1, this this is harder to do.

          • 2. Re: Constrain a set based on a record, then show all, then constrain a set based on next unique record.
            pinchvalve

                 It is indeed the second option.  I am setting a local variable, going to a layout, performing a find, then entering a search value from that local variable.  The result is a group of records, all with the same data in the ID_1 field.  

                 The problem I keep having is once I constrain it to ID_1, then refine it with ID_2, I have to go back to all records that match ID_1.  Then I have to figure out a way to go to the next unique record.  And repeat that for the next unique record.  Anything that goes to "first record" won't work, and loops don't work because I have to constrain the data each time. 

            • 3. Re: Constrain a set based on a record, then show all, then constrain a set based on next unique record.
              philmodjunk

                   Apologies for asking yet another question, but I seem to have forgotten to ask this in my last post: What version of FileMaker are you using? There's a new summary field type added in FileMaker 13 that can short cut this process and another method we might use only works with FileMaker 12 or later as it requires the ExecuteSQL function. More laborious methods are possible with older versions.

                   The basic method that I have in mind is to build a return separated list of all unique values of ID_2 found in your found set. You can then perform a series of finds that each specify the same value for ID_1, but a different value from the list of ID_2 values in a loop to do what you need.

                   But the best way to build that list of ID_2 unique values will depend on the version of FileMaker that you are using.

              • 4. Re: Constrain a set based on a record, then show all, then constrain a set based on next unique record.
                pinchvalve

                     I am using FileMaker 13.  

                     I kept playing with it, and came up with a simple solution.  Instead of trying to sort or work with the ID_3 table where multiple occurrences  of ID_2 exist, I simply open table layout based on ID_2 in a new window.   When I perform a find for ID_1, I get one occurrence each of ID_2.  Then it is just a matter of starting at the first record, looping, and exiting after the last.  

                • 5. Re: Constrain a set based on a record, then show all, then constrain a set based on next unique record.
                  philmodjunk

                       Hmmm, I see we've had a misunderstanding here. I assumed that ID_1, ID_2 and ID_3 were fields in the same table, not different tables in your database.

                       

                            but imagine a table that includes ID_1>, ID_2 and ID_3 records.

                  • 6. Re: Constrain a set based on a record, then show all, then constrain a set based on next unique record.
                    pinchvalve

                         They are both, hence the confusion.  It's hard to explain without ALL of the details, but I realized I didn't have to work with three records in the same table, I could work with a different table.  Sometime going back to the basics is the best way forward!