9 Replies Latest reply on Jan 4, 2017 12:57 PM by erolst

    Count Records based on Field Name


      I have a field called Ticket::Status that I would like to count the records of.  The field result names are Open, Pending and Closed.  As records are created and/or modified I would like the value to change based on the name of this field.

        • 1. Re: Count Records based on Field Name

          Also, I would like to use the values in a portal where Open, Pending and Closed will be displayed as its' own field.

          • 2. Re: Count Records based on Field Name

            If you mean an overall count, create a self-join and do the COUNT on that.


            If you mean you want individual count breakouts for Open, Pending, and Closed, that's easy with SQL and a GROUP BY. You would do somenthing like this:


            Select count(Status) from <TABLE_NAME> group by Status


            To wit:


            • 3. Re: Count Records based on Field Name

              For your original question, you can simply sort your records by status and use a "count of" summary field placed in a sub summary (when sorted by status) layout part to show the totals. You can remove the body layout part to get a report with one row for each status value.


              To use a portal, you'd need an additional table with these relationships:




              StatusTypes::Status = Tickets::Status


              You'd create three records in status types, one with each different value of status. A calculation field defined in StatusTypes: Count ( Tickets::Status ) can give you the number of Tickets records with a given status and this is the field you'd put into your portal.


              This example gives you a total of all tickets of each status. You probably want to filter this to show all tickets meeting additional criteria such as all tickets for a specific event. This is also possible by making modifications to the relationships used such as:


              StatusTypes::gEventID = Tickets::_fkEventID AND
              StatusTypes::Status = Tickets::Status


              gEventID would be a field with global storage specified that you'd set by script or value list to limit the totals to just those for a specific event. If your layout is based on a table of such events, the OnRecordLoad trigger can perform a script to update this global field to the current record's __pkEventID value.


              Please note that this specifically answers your question. You could also use ExecuteSQL to get these totals and not need to use any portal though the appearance on your layout while in Browse mode would be much the same.

              • 4. Re: Count Records based on Field Name

                Such amazing support you give to folks, Phil.


                FMI must put you on the payroll!!


                Happy New Year.

                • 5. Re: Count Records based on Field Name

                  Thanks phil the sub summary worked.


                  I have FMP11,  is there a quick calculation I can use if I want to find out how many tickets a user has assigned without adding it to the sub summary?

                  • 6. Re: Count Records based on Field Name

                    Please tell people in your first post that you are using FMP 11. Someone might spend quite a bit of time putting together an answer or a demo file that you can't use due to assuming that you are using the latest release of FileMaker.


                    I can't answer a question like that without a lot more information.


                    How is a user "assigned to a ticket"?

                    What are the tables/relationships involved, if any?

                    • 7. Re: Count Records based on Field Name

                      the above discussion is for FMP11

                      • 8. Re: Count Records based on Field Name

                        User is assigned the ticket by:

                        when a Service::TicketID is generated there is a field named Service::Contact-fk where a list of names (value list) pops up for me to select a name.



                        The table relationship between the two are Service::Contact-fk = Contact::ContactID.  I was able to follow the sub summary example you gave earlier but would like to know of a way to do a calculation also for future iterations.  Like selecting a name and having every type of ticket calculated.

                        • 9. Re: Count Records based on Field Name

                          You can use exactly the same scheme that PMJ suggested before:


                          Contact x Status = Ticket


                          where the second relationship is based on


                          Status::status = Ticket::Status

                          Status::gContactID = Ticket::fk_contact


                          so that you need to set gContactID to the value of the contact whose record you're entering.


                          If you create a summary field CountOf: primary key in the Ticket table, you can set up a portal on the Contact layout to show records from the Status TO, with the fields Status::name and Ticket::sCount.


                          For the generic names Status and Ticket, substitute the names of the new TOs you will (have to) create to set up that relationship chain.


                          As for future Iterations: if you create a new Status type, you must enlarge the portal ...