Sort Collection In Groups/Tree

[Bahalzamon]

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. 😃

[KrishnaV]

Hi @Bahalzamon ,

try this:

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

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]

@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. =(

[KrishnaV]

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]

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

[KrishnaV]

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]

The output:

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]

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*/

Best Regards,

Bof

[Bahalzamon · Accepted solution]

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