7 Replies Latest reply on Sep 21, 2012 9:23 AM by philmodjunk

    Multiple Find Request on One Report of Layout

    springhouse99

      Title

      Multiple Find Request on One Report of Layout

      Post

           I created a find request in table format that works great I was able to do greater than and equal to and less than and equal to operators on a field just like I want.  My only problem is I want to display the same fields only sorted by the different criteria for the operators on the same report or layout.  The table view looks good but if I have to layout each field individually that would be OK as well.

        • 1. Re: Multiple Find Request on One Report of Layout
          philmodjunk
               

                    My only problem is I want to display the same fields only sorted by the different criteria for the operators on the same report or layout.

               Can you post an example of what you want? I can't quite picture what you have in mind.

          • 2. Re: Multiple Find Request on One Report of Layout
            springhouse99

                 Sure: I want to display records that match field A's (one o six in radio button form) an criteria from field B's eg greater than 5 and less than 10.  There are 8 different changes in the criteria that I would like to display on one "page" or report.  Thanks for looking at this.

            • 3. Re: Multiple Find Request on One Report of Layout
              philmodjunk

                   Yes but how will this "look" on your report? Can you "gin up" an outline of how this data will be arranged in it?

                   Of particular interest is whether or not a record needs to be listed more than once in the same report.

                   Also, what version of FileMaker do you have? (ExecuteSQL, which is new in FMP 12 may be useful for your report.)

              • 4. Re: Multiple Find Request on One Report of Layout
                springhouse99

                     First records will only be shown once, I have FileMaker Pro 12.

                     The report would look like this:

                     Quarter, Customer, Service Purchased (this is the six radio buttons), Number of Minutes (this is the field with the operators), there are a few other fields ie did they re book etc.

                • 5. Re: Multiple Find Request on One Report of Layout
                  philmodjunk

                       Is this what you have in mind?

                       Quarter (number and year)
                           Customer ( Name, CustomerID, and other data specific to one customer)
                                 Service Purchased (up to 6 for a customer, once for each different radio button value selected)
                                           Number of Minutes

                       Rows 1-3 three can be produced with sub summary layout parts where you group your records by sorting them, first by Quarter, then by CustomerID, then by Service Purchased.

                       Number of Minutes is still unclear. I understand that you are using comparison operators such as =, <, and >, but it is not clear what values are being compared here using those operators and this is a very key detail. Also, will each of these "operator" based groups produce a single value from all records meeting that criteria such as a total or average, do you want to list individual recorsd in each group or both?

                  • 6. Re: Multiple Find Request on One Report of Layout
                    springhouse99

                         I sorted the the records as you suggested and that works great.  I then put in my operator for the first criteria and that of course gives me what I am after for that set of records, just a visual view of tendencies.  I don't want or need totals sub-totals or averages.  Just a visual look at the tendencies to better market to my existing clients.  So I think I could alter my layout so I could view it how I want in the list view if I could show the multiple operators  in the report/layout.

                    • 7. Re: Multiple Find Request on One Report of Layout
                      philmodjunk

                           I read that as you need to list individual records--not display summary values.

                           But you still haven't explained how you use these "operators" in sufficient detail for me to describe a way to get groups of records for each "operator" and be sure that what I suggest works for you. So keep in mind that this is based on a guess on my part.

                           I'm guessing that you want to specify a value such as 50 and then want to see three groups of records, group 1 would be all records where the groups of records have a value in a specific field that is less than 50, In group 2, the value equals 50 and in group three the value is greater than 50. On another occaison, you might pull up the same report but specify a different value than 50 for grouping your records.

                           If I have that right, then define a global number field and put in on a layout somewhere where you can enter this value. I'll call this global field, gGroupingValue.

                           Then add this calculation field, cOperatorGroup with number selected as the return type and this calculation:

                           Case ( YourTable::valueField < Globals::gGroupingValue ; - 1 ;
                                      YourTable::valueField = Globals::gGroupingValue ; 0 ;
                                       1
                                     )

                           Set up one more sub summary layout part with cOperatorGroup as the "sorted by" field and include cOperatorGroup as the final field in your sort order.