# Discussions

View Only

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

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

Posted 11-10-2017 22:03
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 :(

• #### 2.  RE: Create a summary field totaling the number of separate days represented by child records with date fields

Posted 11-11-2017 02:29
I have a solution if there are a reasonable number of days per parent. What is the reasonable maximum number of days per Parent?

• #### 3.  RE: Create a summary field totaling the number of separate days represented by child records with date fields

Posted 11-11-2017 03:37
Hmm, how about 10 days max.

• #### 4.  RE: Create a summary field totaling the number of separate days represented by child records with date fields

Posted 11-11-2017 15:31
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]))

• #### 5.  RE: Create a summary field totaling the number of separate days represented by child records with date fields

Posted 11-11-2017 18:37
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!

• #### 6.  RE: Create a summary field totaling the number of separate days represented by child records with date fields

Posted 11-11-2017 18:55
Right! There was no need for the not IsNull at all.