neilticktin Posted October 28, 2019 Posted October 28, 2019 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 Posted October 28, 2019 Posted October 28, 2019 (edited) I can think of two ways you could accomplish this: Use a custom function, perhaps similar to this one; 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
Recommended Posts
This topic is 1939 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now