9 Replies Latest reply on Nov 8, 2012 4:20 PM by philmodjunk

    Calculating number of records matching specific criteria

    RyanMcCoy

      Title

      Calculating number of records matching specific criteria

      Post

           Just a quick simple one for you guys this time.  Basically I am attempting to generate a report of an inventory system that allows me to calculate the total number of items that have a matching field.  For example, I want the field "totalFresnels" to count the number of times the word "fresnel" appears in the table Inventory::instrumentType.  I am assuming I define the "totalFresnels" field as a calculation field and evaluate the number of times "fresnel" appears in the Inventory::instrumentType field, I am just not sure of the context of how to use it.  Any advice?

            

           Thanks!

        • 1. Re: Calculating number of records matching specific criteria
          philmodjunk

               Does the InstrumentType field contain just the one word "Fresnel" in cases where Fresnel appears in that field or does other text appear with the word fresnel in at least a few cases?

               What version of FileMaker are you using? This is an issue that has one possible solution only possible in FileMaker 12, another possible solution only in FileMaker 11 or 12 and a third approach that works in FileMaker 7 through 12.

          • 2. Re: Calculating number of records matching specific criteria
            RyanMcCoy

                 I have FMP 11 Advanced.

                  

                 Ideally what I would like to happen would be for each unique feld in the "instrument type" field to have a total quantity.  That way if we get a new type of lighting instrument in stock, I don't have to redo the calculation field, etc.  If that would not be possible, I could just add all the "total" fields.

                  

                  

            • 3. Re: Calculating number of records matching specific criteria
              philmodjunk

                   Does the InstrumentType field contain just the one word "Fresnel" in cases where Fresnel appears in that field or does other text appear with the word fresnel in at least a few cases?

                   I don't recommend you add one new field for each type if you can avoid doing so and almost always you can. If you use a dedicated field for each, you have to make design changes to your database each time you add a new type. If you avoid this approach, adding a new type is just a case of creating some new records and entering the data needed.

              • 4. Re: Calculating number of records matching specific criteria
                RyanMcCoy

                     It would only contain the word "fresnel".  I am creating a drop down list so the same value(s) will be used multiple times.  Do you know how I would go about having a new "total" column added every time I create a new instrument type?  I would think what I am trying to do would be somehthing that is done often, and there would be an easier solution.  

                • 5. Re: Calculating number of records matching specific criteria
                  philmodjunk

                       Do you really need "columns"? that's possible but is both a limited format ( you have to keep adding columns and only so many fit on a page or screen) and requires a lot more design work to setup.

                       This format of a report is, on the other hand, very simple to set up in fileMaker:

                       Instrument Type:    Total Qty
                       Fresnel                  234
                       Another Type          300
                       Yet another Type     400

                       Grand total:          934

                  • 6. Re: Calculating number of records matching specific criteria
                    RyanMcCoy

                         I guess it does not have to be a column.  When I setup a report it asks for a summary field, however.  So here is what I still need to figure out:

                          

                         1.  How do I perform a count of each unique value present in a field of a table? I.e say I start off with a table looking something like this:

                    Inventory ID    Category    Manufacturer    Subcategory    Model    Location   Notes

                         1                        Light Ins.    ETC                      ERS                    S436     2-226         

                         2                        Light Ins.    Altman                 ERS                    SL26     Storage

                         3                       Access.       Apollo                  Gobo Rot.           MOVE-0 Storage

                         4                       Light Ins.     ETC                     ERS                     S436     2-226

                          

                         Which (CURRENTLY - Will change) consists of 2 different values in the "category" field, 3 in the "subcategory" field, 3 in the "model" field" and 2 in the location field.  I would like to be able to create a report that is able to be sorted by each of those fields (which I already know how to do with the leading and trailing subsummaries...).   I BELIEVE if I can have a sum calculated for each of the unique values in each "column" then I should be able to figure out the rest.

                    • 7. Re: Calculating number of records matching specific criteria
                      philmodjunk

                           You need only one summary field. Put it in all of the sub summary layout parts and the "break" or "when sorted by" field you specify for the sub summary layout part will determine what group of records is used by the summary field to compute the sub total.

                           It might look like this:

                           Light Ins.                300----> total for all Light Ins category
                                ERS                 300----> Total for Light Ins, ERS Subcategory
                                     S436           200---> total for model 2436 over all locations
                                           2-226     200-----> Total of Modeal 2436 in the 2-226 location
                                     SL26            100
                           Access                    20
                               Gobo Rot.            20
                                      Move-0          20
                                            Storage   20

                           The column of numbers, which need not be arranged in a column are all different instances of the same summary field.

                      • 8. Re: Calculating number of records matching specific criteria
                        RyanMcCoy

                             Okay, understood.  Thanks!  Do you have any ideas on actually performing the count to calculate the summary field?

                        • 9. Re: Calculating number of records matching specific criteria
                          philmodjunk

                               That should happen automatically. Just perform a find to pull up the records that you want or use Show All records if you want all of them and then sort them to group your records. You can even get different forms of this report by changing the sort order.