Discussions

Expand all | Collapse all

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

  • 1.  Identify Last Record in a Child Table For Look Up in Parent

    Silver
    Contributor
    Posted 05-04-2017 22:42
    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? 

           


  • 2.  RE: Identify Last Record in a Child Table For Look Up in Parent

    Top
    Contributor
    Posted 05-04-2017 22:48
    what other conditions factor into making that project the 'last' project?


  • 3.  RE: Identify Last Record in a Child Table For Look Up in Parent

    Silver
    Contributor
    Posted 05-04-2017 23:50
    Thanks. Following could be used
    FY
    Completion date
    Category as PNP


  • 4.  RE: Identify Last Record in a Child Table For Look Up in Parent

    Posted 05-05-2017 01:29
    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?


  • 5.  RE: Identify Last Record in a Child Table For Look Up in Parent

    Silver
    Contributor
    Posted 05-05-2017 11:51
    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.