Skip to main content
Question

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

  • May 5, 2017
  • 0 replies
  • 57 views
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? 

       
      This topic has been closed for replies.

      • MCFNeil
      • Registered
      • May 5, 2017
      what other conditions factor into making that project the 'last' project?
      • ArshadKhwaja
      • Author
      • Registered
      • May 5, 2017
      Thanks. Following could be used
      FY
      Completion date
      Category as PNP
      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
      • Author
      • Registered
      • May 5, 2017
      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. 
      Terms & ConditionsAccessibility statement