Create a summary field totaling the number of separate days represented by child records with date fields

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I'm trying to create a summary field which is stumping me. I have a Services table which is loving parent to Time Entries table. Each time entry has a date field. I want to know, for a given service, how many separate dates there are child entries for. So, if a service has these three time entries:
  1. Jimmy, 3 hrs on 10/24/2017
  2. Frank, 2 hrs on 10/24/2017
  3. Jimmy, 5 hrs on 10/27/2017
...for these, my wished-for summary field would yield 2, because there were records with a total of 2 dates on them. I tried simply creating a summary field for the date field, and Quickbase tells me I can't do that :(
Photo of Jonathan Heuer

Jonathan Heuer

  • 350 Points 250 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
I have a solution if there are a reasonable number of days per parent. What is the reasonable maximum number of days per Parent?
Photo of Jonathan Heuer

Jonathan Heuer

  • 350 Points 250 badge 2x thumb
Hmm, how about 10 days max.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
Ok, here we go. This will work, it’s native and you can set it up in 15 minutes.


One Service has Many Time Entries.

Create a Summary field of the Minimum Date. Call it [Service Date 1]

Lookup [Service Date 1] down to Time Entries.


Because we are lazy, Duplicate the Summary field but this time add a filter [Date] > [Service Date 1], and call it [Service Date 2]


Lookup [Service Date 2] down to Time Entries.


Because we are lazy, Duplicate the [Service Date 2] Summary field to similarly make [Service Date 3]


So let’s say you now have 10 of them. Some will be null if there’s less than 10 unique dates.


So the final answer is the number of non null dates.


Count(

not isnull([Service Date 1]),

not isnull([Service Date 2]),

not isnull([Service Date 3]),

Etc

not isnull([Service Date 10]))
(Edited)
Photo of Jonathan Heuer

Jonathan Heuer

  • 350 Points 250 badge 2x thumb
Oh that is elegant Mark. One minor change I made to the final summary field using Count:

Count( [Service Date 1], 
[Service Date 2], 
[Service Date 3], 
...
[Service Date 10]
)

Works great!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,320 Points 50k badge 2x thumb
Right! There was no need for the not IsNull at all.