Flagging different values at children for same parent


I am looking for an efficient solution for the following:

  • I have a module that is multiple levels, compound list - think of it as L1 and L2
  • A line item within that list is an ID for each L2
  • I would like to flag all L1 members that have multiple IDs for their respective children (L2).
  • In the screenshot, there are two different LEAD_ID that exist within the hierarchy 


Thank you!


Best Answer

  • M.Kierepka


    Easiest solution I can think of (based on your screenshot, I assume your LEAD_ID line item has summary:Last Non-Blank enabled, if not, enabe it), I also assume that your L2 list has SYS module with line item "Parent", which for each L2 item gives their L1 parent (if not, make such):
    Add new line item, format: Boolean, summary: Any, formula: ISNOTBLANK(LEAD_ID) AND LEAD_ID <> LEAD_ID[LOOKUP:SYS.Parent]

    Because summary is Any, it will give you TRUE at L1 level if there are at least 2 different IDs.


  • @M.Kierepka - Thank you for the reply. A couple of clarifying questions and we can use the real dimension names


    • Child = Unit
    • Parent = Building
    • Module 1. 'Applies to' is Unit 
    • SYS Module is 'SYS Unit.' It does have a line item for Parent which yields the Building for each unit

    In which module am I adding the new line item?

  • So in "SYS Unit" just add "Parent Building" line item, with format of Building and formula as "PARENT(ITEM(Unit))".
    In Module 1 add new line item (let's call it "Multiple?"), format: Boolean, summary: Any, formula: ISNOTBLANK(LEAD_ID) AND LEAD_ID <> LEAD_ID[LOOKUP:'SYS Unit'.'Parent Building'].

    Example (I placed Parent in the same module for simplicity and clarity):



    Blueprint (notice that LEAD_ID is text, but can have format of any list and this should still work):




  • Yeah, I got it. Thanks for the follow-up though! much appreciated!