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
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.0
@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?0
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):0
Yeah, I got it. Thanks for the follow-up though! much appreciated!0