New Level within Hierachy


I have an established hierarchy and need to add a new level within the middle of it.  Currently, the hierarchy looks like M1>M2>M3.  I will be adding a new level between M1 and M2.  The current M2 and M3 will be children of the new M2.  My concern is around the formulas already associated with the levels in their current positions.  I have multiple modules/dashboards already built using my current hierarchy. 


Are there any concerns or issues I should expect?  Has anyone done this before and can offer some guidance?


Best Answer

  • rob_marshall

    @mEEhan011 ,


    Yea, this might not be fun, but if you have used SYS Modules, it might be a bit easier.  Essentially, everywhere you have a formula that is pointing to the current M1 using parent(M2), you have to remove that formula and change the format and then re-add the formula when you are done.  What I would suggest:

    • make a copy of the model
    • go ahead and create the new list (I will call it M1.5) with the appropriate members and have the parent list be M1.  After this is done, go ahead and create a SYS Module for 1.5 and create a line item M1 list where the formula is parent(item(M1.5))
    • Create a SYS Module (if you don't already have one) for M2 and do the same thing (create a line item for M1.5, format it to M1.5, but don't put a formula in it yet.
    • Within the model, anywhere you are using M2 to get to M1,  you will now need it to go to M1.5, but you can't just point the line item to your SYS M2 Module.  You will need to remove the formula.  Before doing this, I would recommend exporting the blueprint of each module that needs updated.
    • Once the formula's are removed, now change M2 to rollup to M1.5 on the list (parent list).  If it doesn't work, that means you missed a formula.
    • Now that M2 is a child of M1.5, go to the SYS M2 Module and put the formula in for M1.5 (parent(item('M2'))
    • you can go back to the modules and change the format from M1 to M1.5 where it is using M2 in the Applies To and update the formula to use the SYS M2 Module M1.5 line item.

    Hopefully that makes sense, but if not, feel free to DM and we can setup a meeting to talk it out.




  • Hi @rob_marshall


    I gave that a try yesterday and it worked!  Thanks!

  • @mEEhan011 


    Well done, glad it worked out for you.

  • Hi @rob_marshall,
    I appreciate the methodical approach outlined here. Do you have any additional guidelines if the hierarchy change needs to happen across several models with imports/exports utilizing this hierarchy across the models?

  • @zbunting 


    I guess I am not sure exactly what you mean.  If you are having to add a new level into a composite hierarchy in multiple models, you would have to prepare or do the above in every model.


    With that said, in your scenario,  you would definitely want to build the views in a central place, like a data hub and then have the models use those centralized views to build the hierarchy.


    Or am I misunderstanding your question?



  • I think you understood correctly. Basically, I should do the first step in all of the affected models, then move to the second, etc. So by the time I'm actually making (as in the original example) changing the parent of M2 from M1 to M1.5, I am doing that step at the same time across all models.

  • @zbunting 


    Exactly, because some models might use certain levels more than other models.


  • HarunT

    Hi @rob_marshall, I am having a similar problem, but instead of adding a new layer between M1 and M2, I need to add a new "M1_new", and current M1 and M2 would be M2 and M3. I would really appreciate if you could clarify whether I should follow the same steps or need to tweak a little bit? Thanks

  • @HarunT

    i just saw the above from your other post…Do you still have questions on this?

  • HarunT

    Thanks @rob_marshall. No further help needed, I was able to change the top level without any problem.

  • @HarunT

    Perfect! Sorry, I am not sure how I missed your post.