Summing data for Calendar Report
Building a Calendar Report - Heat Map. I want to summarize all the data by day to show the "number of events each day." For example - Summarize damages by day of the week. Field name is "Date of ...
Forum Discussion
MarkShnier__You
Qrew Legend
Qrew LegendThis used to be almost impossible or complex. Now it's easy with Formula Queries except for learning the Syntax, which I don't claim to be an expert.
Maybe this will work
Size(
GetFieldValues(
GetRecords("{6.EX." & [Date] & "}"),3))
You would replace the 6 with the field ID# of the date of the appointment and replace the [Date] with the field name that holds the date of the appointment.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Maybe this will work
Size(
GetFieldValues(
GetRecords("{6.EX." & [Date] & "}"),3))
You would replace the 6 with the field ID# of the date of the appointment and replace the [Date] with the field name that holds the date of the appointment.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
Oooh So Close! It is listing the total of the day - but for each record. How do I get just one record showing the total for the day?
Size(
GetFieldValues(
GetRecords("{6.EX." & [Date of Damage] & "}"),3))
------------------------------
Deborah Becker
------------------------------
Size(
GetFieldValues(
GetRecords("{6.EX." & [Date of Damage] & "}"),3))
------------------------------
Deborah Becker
------------------------------
- MarkShnier__YouOne way is to use excel to load up a dates table with all the dates for the next 5 years. Then use this syntax.
Size(
GetFieldValues(
GetRecords("{6.EX." & [Date] & "}","bxxxxxg8"),3))
where the "bxxxxxg8" is the table ID for your appointments table.
The other way without an extra table is to write yet another formula of the query so that you can identify the appointment for each day which has the Minimum Record ID.
You would do that in another field to flag record which are the first appointment of the day.
Here is some syntax for that. Field 1 would be a formula multi select text field called [Record ID#s for this Day]
GetFieldValues(
GetRecords("{6.EX." & [Date] & "}"),3)
Then another field to flag if it's the first one of the day.
ToNumber(Trim(Left([[Record ID#s for this Day] ],";"))) = [Record ID#]
//The Formula Query returns a list of all of the Record IDs for this day. The first one on the list (at the left) will be the minimum.
I know this could be done in one field but this way you get to see the steps in the formula query.
------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------