How Can I Automatically Identify and Delete Childless Hierarchy Items?

red11
red11
in Integrations and extensibility

Hello,

I have a constantly shifting hierarchy in place which consists of 8 levels, level 1 (top) through level 8 (bottom).  Within this hierarchy it's necessary to delete members of L7 when they have no L8 members which roll up to them.  This scenario plays out regularly as L8 members are often realigned to roll up under different parents.

 

My Question:  Is there some way to automate the identification and deletion of L7 members which don't have children?  If I could run an action to clean the hierarchy it'd save me a meaningful amount of time pruning my hierarchy.

Tagged:

Answers

  • JaredDolich
    JaredDolich

    @red11 

    You can try adding a line item to your Level 8 system module that has a "1" in the formula. Also make sure there is a line item formatted as list for your level 7.

    In your Level 7 system module you create a sum function to add up the 1's. If the value is greater than 0 then FALSE, else TRUE. 

    Use the TRUE value boolean in the delete list action.

    Addendum:

    • Always create an audit saved view that will show you what is going to be deleted before you run the action.
    • Good practice to test your process before deleting anything.
    • If you make a mistake, you'll have to roll back the model by using the history section
  • nikolay_denisov
    nikolay_denisov

    Hello 

    I have an Idea how to do it.

    1) Create a SYS module (SYS01 | L8 calculation) for L8 hierarchy as a dimension. Create here Line item Count with formula = 1.

    This lineitem will count your L8 items and will rollup summaries for L7

    2) Create SYS module (SYS02 | L7 delete) for L7 Hierarchy as a dimension.

    Create lineitem To be Deleted? as a boolean and place a formula = SYS01 | L8 calculation.Count =0

    3) Create new action "Delete from a list using selection..." with Name "Clean L7"

     

    Hope it will help.

    Regards, Nikolay

  • rob_marshall
    rob_marshall

    @red11 

     

    My solution is very similar to what @nikolay_denisov suggested, and this works very well.

     

    My hierarchy has 9 levels, so just subtract one for yours

     

    Create a SYS module on your L8 list (again, mine is L9)

     

    2020-12-18_13-04-58.png

     

    Create views, for each level, using these booleans as filters:

    2020-12-18_13-07-36.png

     

     

    Create a Process that builds the hierarchy and then deletes the members no longer needed:

    2020-12-18_13-10-03.png

     

     

    Now, if you want to automate the entire process, then you will also have to delete parent members that are now detail members.

     

    2020-12-18_13-17-13.png

     

    Put all of these into a Process and it will be automatic.

     

    2020-12-18_13-18-26.png

     

    Let me know if you have questions.

     

    Rob

     

Categories