cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Advocate I
Advocate I

Sort Collection In Groups/Tree

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Advocate I
Advocate I

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

View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

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.


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.

Proud to be a Super User!

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
Highlighted

@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

Highlighted

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.


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.

Proud to be a Super User!

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
Highlighted

@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

Highlighted

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.


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.

Proud to be a Super User!

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
Highlighted

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.


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.

Proud to be a Super User!

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
Highlighted

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

Highlighted
Advocate I
Advocate I

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

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (11,492)