Forum Discussion

MarkShnier__You's avatar
MarkShnier__You
Icon for Qrew Legend rankQrew Legend
4 years ago

Re: Summing data for Calendar Report

This 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
------------------------------

2 Replies

Sort By
  • DeborahBecker's avatar
    DeborahBecker
    Qrew Member
    4 years ago
    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
    ------------------------------
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      4 years ago
      One 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
      ------------------------------