How do I create a report that filters on a particular date field and returns any records equaling that highest date value within the field?

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

How do I create a report that filters on a particular date field and returns any records equaling  that highest date value within the field?  

So if the date field has 5 records with 10/1/2016, 8 other records with 10/10/2016, 10 other records with 10/25/16 ... I want the report to only show the the 10 records with 10/25/2016.    I was thinking some type of MAX function.

Photo of Derick

Derick

  • 70 Points

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 62,994 Points 50k badge 2x thumb
You would need to have all thes e records connected to a runny parent record. For example, make a table with 1 record in it. It will be record ID number 1.

On your dares table make a formula field Ruth a formula of 1.

Make a relationship based in that field.

Summarize max date.

Lookup max date Dian to dates table.

Filter where max dates = [my date field]
Photo of Yoel Kuliasko

Yoel Kuliasko

  • 520 Points 500 badge 2x thumb
Hi. I'm not quite following what is meant by "On your dares table make a formula field Ruth a formula of 1."? Which kind of formula field would that need to be?
Thanks.
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
I think Mark might have been using his iPad while he was skiing.

What he wanted to type was;
On your date table you will need to make a formula relationship field to connect ALL records to the new 'Parent' table.

This is what will drive your relationship to drive the summary field, and subsequent lookup field.

Can you describe the current relationships to your table in questions.  We might be able to do this without adding a new table and relationship to accomplish one goal.
Photo of Yoel Kuliasko

Yoel Kuliasko

  • 520 Points 500 badge 2x thumb
"I think Mark might have been using his iPad while he was skiing." I figured something like that :-)))

The app is for planning activities with customer care specialists. 
The activities table is related to the specialists table (each specialist can have many activities)
The activities is also related to an attendees table for enabling multiple specialists selection for group activities. (each activity can have many attendees)
This attendees table is also related to the specialists table. (each specialist can be many attendees).

I would like to be able to show in a report the last activity that was conducted with a specialist, and the date of this last activity (Maximum start date).

To begin try to accomplish this, I edited the relationship as such:



But I'm not sure if this is the right start; where to put the formula, and what is a formula of 1, are unclear to me.

Thanks again for all your help!
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
With the new clarity this will be a piece of cake.

With your [Activity - Maximum state date] field, you just need to add a condition to the summary.

"Only summarize records where [start date] is before 'today'

Keep it looking for the max date.

This will give you the most recent Date that has passed.
Photo of Yoel Kuliasko

Yoel Kuliasko

  • 520 Points 500 badge 2x thumb
It worked indeed with the settings I had (I had overlooked something else which prevented the status to change to "Conducted/done")
What I didn't figure out yet, is how to display the activity belonging to that maximum start date. As first reaction, I'd be tempted to create a summary field for "Activity", but this doesn't seem to fit with the possible calculations for a summary field.
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
Now that you have made the summary [Max Start Date] and then passed it as a lookup [Activity - Max Start Date],
Create another summary field to find the Record ID# of that particular activity.

Do that by making a summary field that summarizes the Maximum Record ID# with the conditions;
Where Start Date = [Activity - Max Start Date]

That should give you the Record ID.

Then you can use that down the road for reporting, or other relationships needed.
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
Do you want to show the most recent start date per specialist only?  Also, to clarify, you want dates that have past?  For example, yesterday would be the most recent date.
Photo of Yoel Kuliasko

Yoel Kuliasko

  • 520 Points 500 badge 2x thumb
Yes, per specialist only, not on team level.
Yes, only dates that have past, so that team leaders can at a glance see which activity was last conducted with a certain specialist, and when that was. 
So the two columns I'd like to create don't need any data from future activities. Now that you ask this question, I realize this indeed adds a challenge; because if an activity with a specialist is planned somewhere in the future, I'd like it to not show up in those columns.
Photo of Yoel Kuliasko

Yoel Kuliasko

  • 520 Points 500 badge 2x thumb
However thinking more of it, these columns are intended to trigger an "Oops, it's been long since this specialist had an activity"-effect.
So if those columns don't exclude an upcoming activity, it's for the team leader a clear token that something has been planned with that specialist if they see an upcoming activity, and no more need for the oops-reaction.
So if there's a big difference in the feasibility of the two options, it's not worth the hassle of enabling reporting of only past dates...