Skip to main content
Question

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

  • November 11, 2017
  • 0 replies
  • 38 views
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 :(
    This topic has been closed for replies.

    I have a solution if there are a reasonable number of days per parent. What is the reasonable maximum number of days per Parent?
    • JonathanHeuer
    • Author
    • Quickbase Alumni
    • November 11, 2017
    Hmm, how about 10 days max.
    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]))
    • JonathanHeuer
    • Author
    • Quickbase Alumni
    • November 11, 2017
    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!
    Right! There was no need for the not IsNull at all.
    Terms & ConditionsAccessibility statement