Identify Last Record in a Child Table For Look Up in Parent

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
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? 

       
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
what other conditions factor into making that project the 'last' project?
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Thanks. Following could be used
FY
Completion date
Category as PNP
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Arshad,
You will need to define the logic sequence to use to determine the most recent project.  Can you express it in words.

You seem to be saying to only count a project if it was a PNP Project, and then you want to find the project with the highest Completion date.  But what if that date is blank?  Then do you want to take the project with the highest Record ID which has a blank completion date, else use the one with the highest completion date of none are blank?
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,132 Points 3k badge 2x thumb
Thanks. For  now I am using following: 

If([Category] = "PNP"  and [FY] > "FY 10", true, false
)

But it is not overarching, How do deal with blank highest record? Is this correct approach.