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

• 0
• Question
• Updated 2 years ago
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 :(
• 400 Points

Posted 2 years ago

• 0
• 72,226 Points
I have a solution if there are a reasonable number of days per parent. What is the reasonable maximum number of days per Parent?
• 400 Points
Hmm, how about 10 days max.
• 72,226 Points
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)
• 400 Points
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!
• 72,226 Points
Right! There was no need for the not IsNull at all.