web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Sort Collection In Gro...
Power Apps
Answered

Sort Collection In Groups/Tree

(0) ShareShare
ReportReport
Posted on by

I am looking to organize a list of data that link to each other through ID's. I have a table with ID, Tier & ParentID. I am wanting to create a collection with it ordered in the order in somewhat of a tree structure. Below is a simplified version of the information.

 

Table Structure

 

+----+------+----------+
| ID | Tier | ParentID |
+----+------+----------+
| 1 | 0 | 0 |
+----+------+----------+
| 2 | 0 | 0 |
+----+------+----------+
| 3 | 1 | 1 |
+----+------+----------+
| 4 | 1 | 2 |
+----+------+----------+
| 5 | 2 | 3 |
+----+------+----------+
| 6 | 2 | 4 |
+----+------+----------+

 

How I would like to order it

 

+----+------+----------+
| ID | Tier | ParentID |
+----+------+----------+
| 1 | 0 | 0 |
+----+------+----------+
| 3 | 1 | 1 |
+----+------+----------+
| 5 | 2 | 3 |
+----+------+----------+
| 2 | 0 | 0 |
+----+------+----------+
| 4 | 1 | 2 |
+----+------+----------+
| 6 | 2 | 4 |
+----+------+----------+

 

 

Thank you for any assistance ahead of time. 😃

Categories:
I have the same question (0)
  • KrishnaV Profile Picture
    5,023 on at

    Hi @Bahalzamon ,

     

    try this:

    SortByColumns(AddColumns(collSample,"addFlag",If(Mod(ID,2)=0,0,1)),"addFlag",Descending,"ID",Ascending)

     

    KrishnaV_0-1596478111186.png


    I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

    Regards,
    Krishna
    If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

  • Bahalzamon Profile Picture
    on at

    @KrishnaV Thank you for the response, but this doesn't seem to work for me, I am unsure how the ID itself using  can get the results I am looking for.

    I have attached an image of the data it is spitting out. I used a button to collect the data into a test collection

     

    ClearCollect(ctest,SortByColumns(AddColumns(StrategicGoals,"addFlag",If(Mod(ID,2)=0,0,1)),"addFlag",Descending,"ID",Ascending))

     

    The below image is the result. Unless there is some additional stuff I am supposed to implement within the If statement I am unsure how this works. =(

    exportimage.jpg

  • KrishnaV Profile Picture
    5,023 on at

    Hi @Bahalzamon ,

     

    Do this:

    1. Create a new screen
    2. On App OnStart event write as below:

     

    ClearCollect(collSample,{ID:1,Tier:0,ParentID:0});
    Collect(collSample,{ID:2,Tier:0,ParentID:0});
    Collect(collSample,{ID:3,Tier:1,ParentID:1});
    Collect(collSample,{ID:4,Tier:1,ParentID:2});
    Collect(collSample,{ID:5,Tier:2,ParentID:3});
    Collect(collSample,{ID:6,Tier:2,ParentID:4});​

     

    • Add a gallery and set the data source as collSample
    • Items to collSample
    • Now add another gallery and add the below formula on items property:

     

    SortByColumns(AddColumns(collSample,"addFlag",If(Mod(ID,2)=0,0,1)),"addFlag",Descending,"ID",Ascending)​

     

     

    Note: The issue with your solution is the way how are building the collection, try the above steps, and let me know.
    I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

    Regards,
    Krishna
    If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

  • Bahalzamon Profile Picture
    on at

    @KrishnaV  OK I am not understanding exactly what you are meaning with how I am building the collection.

    If you are referring too the original data it is stored within a SharePoint list. From your proposed resolution its just organizing them by ID based on Even/Odd using Mod.

    What I am looking for is one based on ParentID/Tier.

     

    I am trying to get things under their ParentID. So a Tier 0 with an ID of 4 I would like all the Tier 1's with a ParentID of 4 under it, then Tier 2's, 3's, 4's, etc... then the next Tier 0 with the same process. The most important thing is the ParentID honestly, the Tier is just another way of labeling it for visuals but I can use if necessary.

     

    If this is what yours does then I am just not understanding something then. Because here is the result with actual data not pre-formatted data.

     

    Raw (SP List)

    StrategicGoals

    Filter

    SortByColumns(AddColumns(StrategicGoals,"addFlag",If(Mod(ID,2)=0,0,1)),"addFlag",Descending,"ID",Ascending)

    exportimage.jpg

  • KrishnaV Profile Picture
    5,023 on at

    Hi @Bahalzamon,

     

    could you try this:

    Clear(collFinal);
    ForAll(AddColumns(Sort(Distinct(collSample,ID),Result,Ascending),"IDVal",Result),
    If(CountRows(Filter(collSample,ParentID=IDVal))>0,
    Collect(collFinal,Filter(collSample,ID=IDVal && ParentID=0));
    Collect(collFinal,Filter(collSample,ParentID=IDVal));
    )
    );


    I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

    Regards,
    Krishna
    If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

  • KrishnaV Profile Picture
    5,023 on at

    The output:

    KrishnaV_0-1596490713428.png

    Note: SharePoint is not a best place for Relational data.
    I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

    Regards,
    Krishna
    If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.

  • v-bofeng-msft Profile Picture
    Microsoft Employee on at

    Hi @Bahalzamon :

    I used ForALL's multiple nesting, please check whether the scheme meets your needs.

    I've made a test for your reference:

    1\My data source:(Table)

     

    ClearCollect(Table,{ID:1,Tier:1,Flag:1,ParentID:0},{ID:2,ParentID:0},{ID:3,ParentID:1},{ID:4,ParentID:2},{ID:5,ParentID:3},{ID:6,ParentID:4})

     

    2\Add a button and set it's OnSelect proeprty to:

     

    ClearCollect(
     TheTable1, /*TheTable1 is my custom collecion,Its role is to sort the data sources initially*/
     SortByColumns(
     Table,
     "ParentID",
     Ascending,
     "ID",
     Ascending
     )
    );
    ForAll(
     [1,2,3,4,5,6,7,8,9], /*Search records with ParentID=0, loop 9 times (if the number of eligible records may be greater than 9, please increase the number of loops)*/
     If(
     CountRows(
     Filter(
     TheTable1,
     ParentID = 0
     )
     ) >= Value,
     Collect(
     Table2,
     {
     ID: Last(
     FirstN(
     Filter(
     TheTable1,
     ParentID = 0
     ),
     Value
     )
     ).ID,
     ParentID: Last(
     FirstN(
     Filter(
     TheTable1,
     ParentID = 0
     ),
     Value
     )
     ).ParentID,
     Tier: Last(
     FirstN(
     Filter(
     TheTable1,
     ParentID = 0
     ),
     Value
     )
     ).Tier,
     Flag: Last(
     FirstN(
     Filter(
     TheTable1,
     ParentID = 0
     ),
     Value
     )
     ).Flag
     }
     );
     ForAll(
     [0,1,2,3,4,5,6,7,8,9],/*Search child records, loop 9 times (the number of child records of a record with parentID=0 may be greater than 9, please increase the number of cycles)*/
     ForAll(
     TheTable1,
     If(
     ParentID = Last(Table2).ID,
     Collect(
     Table2,
     {
     ID: ID,
     ParentID: ParentID,
     Tier: Tier,
     Flag: Flag
     }
     )
     )
     )
     )
     )
    )/*Finally, the sorted records are stored in Table2*/

     

    33.gif

    Best Regards,

    Bof

  • Verified answer
    Bahalzamon Profile Picture
    on at

    I ended up going with a solution I found online.

    https://qiita.com/h-nagao/items/73c5192c6f268383ff5e

     

    It took a bit to get through, but I made some modifications to the code and it got to what I needed.

     I can definitely clean it up a bit, but this is getting me there and is not too bad.

    ForAll(
     goalFullPull,
     Collect(
     goalList,
     {
     goalName: goalName,
     goalDescription: goalDescription,
     personResponsible: personResponsible,
     department: department,
     ID: ID,
     goalTier: goalTier,
     ParentId: parentGoalKey,
     TreePath: ""
    
     }
     )
    );
    UpdateIf(
     goalList,
     goalTier = 0,
     {TreePath: ID},
     goalTier = 1,
     {TreePath: ParentId&">"&ID},
     goalTier = 2,
     {TreePath: 
     LookUp(goalFullPull,ID = ParentId,parentGoalKey)&">"&ParentId&">"&ID
     },
     goalTier = 3,
     {TreePath:
     LookUp(goalFullPull,
     ID = LookUp(goalFullPull,ID = ParentId,parentGoalKey),
     parentGoalKey)&
     ">"&LookUp(goalFullPull,ID = ParentId,parentGoalKey)&">"&ParentId&">"&ID
     }
    )

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Leaderboard > Power Apps

#1
WarrenBelz Profile Picture

WarrenBelz 542 Most Valuable Professional

#2
Haque Profile Picture

Haque 206

#3
Kalathiya Profile Picture

Kalathiya 201 Super User 2026 Season 1

Last 30 days Overall leaderboard