How Can I Automatically Identify and Delete Childless Hierarchy Items?

red11

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.

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

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

@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)

 

 

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

 

 

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

 

 

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

 

 

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

 

 

Let me know if you have questions.

 

Rob