Discussions

Expand all | Collapse all

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.