Rank and Sort List with Child Items


We have a list within our Data Hub that needs to be sorted in a very specific order. We import this list into several spoke models on a recurring basis and in the past we've had to manually reorder the lists in every spoke model when new items are added (because new items are always added at the bottom of the list).


To take advantage of the new order list action, I want to assign a ranking to each list item in my data hub and then use that ranking in my spoke models to sort the list. This list has the potential to be large so I want to avoid manually assigning a ranking to each item. I want the system to automatically rank/number the items in the order in which I have them sorted in my data hub list.


In my Data Hub, I created a module and gave each list member the same number. I then used the RANK function with the SEQUENTIAL equal value behavior option to rank/number the items how they appear in the list. However, I noticed that the rank function produces different answers for the child list items depending on when they are added to the list. 


List 1: The 6 products were manually added first and then the 3 children of Apples were manually added after. The result is that the children are at the bottom of the ranking.





List 2: The 6 products and the 3 children of Apples were all added at the same time via an import action. The result is that the children are listed in order with the rest of the items.





I need the result of the rank function to always look like List 2's ranking because of how the order list action works. I discovered that if the ranking looks like List 1, the order list action will move Apples and its children to the bottom of the list in my spoke models, which is not what we want.


Because I can't always ensure child list items will be added at the same time as their parent, I'm hoping someone has a workaround or knows of another function that can be used to rank/number these list items automatically.


  • @taylor_rogers 

    Can I ask if that list is a composite or balanced hierarchy?

    It looks like it might be a simple list - or ragged list


    If so, can you set it up as a composite list?

    That way, the child list could be sorted independently



  • @DavidSmith  turning this into a composite list could be an option but we aren't sure it's ideal, due to the large number of 1x1 relationships. Most of the items in our list only have 1 child list item (example: parent of banana would only have 1 child also named banana) and adding an extra layer is something we'd rather avoid.