Next lowest within list

[neilticktin]

Hi,

I may be in the wrong subforum for this ... but it's related to relationships, so we'll give it a shot.

If I have a list of people, and each of those people has one or more related records with a date field , any thoughts on the best way to find the next lowest date field compared to a given date.

Example, Bob has related records with the dates Jan 1, 2017, July 15, 2017, and Jan 31, 2018 -- I want to find the date that is the date thats' closest to, but before, Dec 1, 2017.  And, if Bob had not had any dates before Dec 1, 2017, I get effectively a null.

Thoughts on best way to do this?  Happy for it to be some sort of relationship, calculation, or custom function.  Would prefer any of those over a script.

Thanks!

Neil

 

 

[comment]

I can think of two ways you could accomplish this:

1. Use a custom function, perhaps similar to this one;
2. Define a relationship to another occurrence of the events table as:
   People::PersonID = Events 2::PersonID
   AND
   People::GivenDate > Events 2::Date
   Sort the related records by date and get the latest one (either first or last, depending on the sort order).

--
P.S. Please update your profile to reflect your version and OS, so that we know what you can use (for example, using SortValues() and While() could make this task much simpler than the custom function mentioned above). 

 

Edited October 30, 2019 by comment