Skip to main content
Question

Create a report displaying only one record per day with earliest appointment

  • August 9, 2018
  • 0 replies
  • 101 views
I have a SERVICES table with many APPOINTMENTS. I'd like to create an embedded table of Appointments on each Service page. There can be many appointments on a given day, and/or on different days. In my embedded table, I'd like to only display the earliest appointment on a given day, and only display one appointment per day, even when there are more than one.

So, instead of...
  • Jimmy     Thursday, 11am    Wash windows
  • Jake        Thursday, noon    Strip floors
  • Susan     Friday, 8am         Clean grout
I'd just have
  • Jimmy     Thursday, 11am    Wash windows
  • Susan     Friday, 8am         Clean grout
Any suggestions on how to tackle this?
    This topic has been closed for replies.

    • RobIV
    • Quickbase Alumni
    • August 9, 2018
    I would tackle this with code. Iļæ½m not sure about a native solution. I can give you some ideas with a code page leveraging JavaScript if you are interested, but that would be a really technical solution and not one you may be looking for.

    Just let me know if that sounds like something you want to do.

    Thanks,

    ~Rob
    I would use excel to load up a table with all the dates for the next 10 years.  Set the Key field to be the date field.

    Make a relationship to the appointments table based on the [Appointment  date].

    Summarize Minimum Appointment Time and call it [First Appointment Time].
      
    Lookup [First Appointment Time] down to Appointments.

    Since there may be more than one appoint at the same time we know need to loop again to identify just one of the [Record ID#]'s 

    New Summary of minimum record ID# subject to the filter that [Appointment timer] is equal to [First Appointment Time].

    Call that [Record ID# of first appointment]

    Lookup down to Appointments.


    Finally, make your report subject to a filter that [Record ID#] = [Record ID# of first appointment]
    • JasonJohnson
    • Quickbase Alumni
    • August 9, 2018
    If you were trying to only display the first appointment of only one day and not multiple days it wouldn't be hard to do. Given your example of multiple days you would need to use Mark's native solution or go with scripting.

    Terms & ConditionsAccessibility statement