Forum Discussion

ArshadKhwaja's avatar
ArshadKhwaja
Qrew Commander
8 years ago

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

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? 

       
  • MCFNeil's avatar
    MCFNeil
    Qrew Captain
    8 years ago
    what other conditions factor into making that project the 'last' project?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    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?
  • ArshadKhwaja's avatar
    ArshadKhwaja
    Qrew Commander
    8 years ago
    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.