I have a parent table (stores) and a child table (projects). We have multiple projects in the the life of a store. We would like to see the details of the last project in the store table. But using max of record ID from projects cannot work as I migrated my data in several batches and it is not necessary that my last project is always associated with the latest record ID.
With some help from support desk, I created a checkbox field in the projects table to establish the latest project and use this field as the filter when looking up. But I am struggling to cover all scenarios in the formula as projects could have been created in the same year. So the question is how to identify the latest project?
Any insights?