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.
Also, I would like to use the values in a portal where Open, Pending and Closed will be displayed as its' own field.
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
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 ANDStatusTypes::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.
Such amazing support you give to folks, Phil.
FMI must put you on the payroll!!
Happy New Year.
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?
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?
the above discussion is for FMP11
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.
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 ...
Retrieving data ...