cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bahalzamon
Advocate II
Advocate II

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
Bahalzamon
Advocate II
Advocate II

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

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.

@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

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.

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.

@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

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.

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.

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.

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.

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

Bahalzamon
Advocate II
Advocate II

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (77,121)