Skip to main content

Pull next upcoming date from a roll up of dates

  • November 21, 2017
  • 3 replies
  • 57 views
K

Hello,
I have a table of courses linked to a table of course runs. The course run table has many records connected to one course. The course runs table has multiple course run dates linked to a single course. I would like to calculate the next upcoming course run for a course in the courses table. I have read through the forums and found the ways that people have found the nearest date for events in the past using the max(values) function, but I can’t seem to find information how to compare all the course run dates linked to a course, to today(), and display the date nearest to today() in the future. Does anyone have advice on how to approach this problem?
Thanks,
Kevin

O

3 replies

K
  • Kevin_Brunswick
  • Author
  • New Participant
  • November 22, 2017

Update:

I have made some progress on the problem described above. First, I created this formula in a field called {After Today} in the course runs table. IF((IS_AFTER({Date},TODAY())),{Date}). That gives me a column that shows either a blank or the date of the course run, if the course run date is after today. Next, I made a rollup field in my courses table. This rollup does an ARRAYUNIQUE(VALUES) to the {After Today} field and shows me all course runs dates for a course after today. Finally, I created another field in the courses table to determine the min value of the dates in the rollup I just mentioned. The problem is it only shows the result zero. It shows zero if there are no dates, one dates, or multiple dates in the rollup field. Could this we a date formatting error? The dates in the rollup field appear in this format. (, 2018-03-06T14:00:00.000Z, 2018-05-29T13:00:00.000Z). Any suggestions about what to try next would be super helpful.
Thank you!!!

J
Forum|alt.badge.img+4

Hi @Kevin_Brunswick

You’re nearly there - you just need to use a Min(values) in the roll up field instead of the ArrayUnique and you should get the earliest future date right there (as least I did when I tried just now).

J
K
  • Kevin_Brunswick
  • Author
  • New Participant
  • November 22, 2017

@Julian_Kirkness Julian, it worked! It makes sense that I don’t need to isolate unique values if all I care about is the min value. It looks like I am losing the time in the date time in this rollup. Any suggestions? I’ll try to format the date to see if that helps.

Thank you, Julian!

Terms & ConditionsAccessibility statement