cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gk1d09
Frequent Visitor

2 column sorting and patch

I have 2 columns. Priority and 'Prioritisation Total'. 'Prioritisation Total' is calculated elsewhere.

 

The higher the 'Prioritisation Total' the higher Priority.

e.g. 'Prioritisation Total' = 100, Priority = 1. 'Prioritisation Total' 1, Priority = 100.

 

When the 'Prioritisation Total' is changed in 1 record, every other record will need to be updated with a new Priority.

 

I have tried achiveing it by:

Creating A hidden datatable from collection2 using these clearcollects. 

ClearCollect(AllPriors,ShowColumns(SortByColumns(Project,"PrioritisationTotal",Descending,"Priority",Ascending),"ProjectID","SiteName","Governance","PrioritisationTotal","Priority","Title"));Clear(collection2);ForAll(AllPriors,Collect(collection2,{id:CountRows(collection2)+1,'Prioritisation Total':PrioritisationTotal,Priority:Priority,Governance:Governance,Title:Title,'Project ID':ProjectID,SiteName:SiteName}))

 

datatable.png

 

 

 

 

 

 

 

 

The id column is the auto increment index, but also the priority i want because the 'Prioritisation Total' is now descending. 

 

Now i have the correct sort order for both columns, i would like to patch those columns into the list.

 

My attempt is below:

ForAll(AllPriors,Patch(Project,First(Filter(Project,ProjectID = Value(ProjectID_Column2.Text))),{'Title':Text(Title_Column15.Text),'Prioritisation Total':Value('Prioritisation Total_Column3'.Text),'SiteName':Text('SiteName_Column3'.Text),'Priority':Value(id_Column2.Text)})));

 

The Priority column is not being patched with the values from the id column.

 

I am sure there is a much better way of achieving this, i just dont know how. 

 

1 ACCEPTED SOLUTION

Accepted Solutions

You got me on the right track. Thank you very much.

 

This is the solution that ended up working.

 

ForAll(collection2,Patch(Project,First(Filter(Project,ProjectID = Value(collection2[@ProjectID]))),{Title:collection2[@Title],PrioritisationTotal:collection2[@'Prioritisation Total'],Priority:id}))

 

This forum is amazing. 

View solution in original post

3 REPLIES 3
v-yutliu-msft
Community Support
Community Support

Hi @gk1d09 ,

Do you want to use sort order based on  'Prioritisation Total' to update Priority column?

Could you tell me

1)the data type of Priority column? 

2)the fields in AllPriors collection?

Based on your formulas, I think AllPriors collection only has these fields: "ProjectID","SiteName","Governance","PrioritisationTotal","Priority","Title".

However, it seems like you use these fields to update Project "Title_Column15.Text ,'Prioritisation Total_Column3'.Text,'SiteName_Column3'.Text,id_Column2.Text".

You need to use fields in AllPriors collection to update Project.

What's more, id field is in collection2, not in AllPriors collection.

Don't you need to use  collection2 to update Project?

 

I assume that Priority column is number data type.

The please try this formula to update:

ForAll(collection2,Patch(Project,First(Filter(Project,ProjectID = collection2[@ProjectID])),{'Title':collection2[@Title]),
'Prioritisation Total':collection2[@PrioritisationTotal],
'SiteName':collection2[@SiteName],
'Priority':id
}
)
)

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hello,

Thank you for the reply.

Priority and Prioritisation Total are whole numbers.

 

I would like the id column from collection2 to be patched into the Priority column in my sharepoint list where ProjectID = ProjectID.

 

Every time a project gets saved, I want the Priority column in my sharepoint list to be updated based on their Prioritisation Total. 

 

Scenario: A user updated their Prioritisation Total and the score is now higher, the project should now be a higher priority than one with a lesser score. 

 

My line of thought it that i will need to updated every other item in that lists Priority because one projects priority has affected all the others. 

 

Below is the AllPriors collection i have changed to only include the required fields.

ClearCollect(AllPriors,ShowColumns(SortByColumns(Project,"PrioritisationTotal",Descending,"Priority",Ascending),"ProjectID","PrioritisationTotal","Priority","Title"));

 

I implemented the code you have provided and have received the following:

 

Filter(Project,ProjectID = collection2[@'Project ID'])

 

Is producing the error below

 

The requested operation is invalid.
Server Response: Project failed: The expression "ProjectID eq Project ID" is not valid. Creating query failed.

You got me on the right track. Thank you very much.

 

This is the solution that ended up working.

 

ForAll(collection2,Patch(Project,First(Filter(Project,ProjectID = Value(collection2[@ProjectID]))),{Title:collection2[@Title],PrioritisationTotal:collection2[@'Prioritisation Total'],Priority:id}))

 

This forum is amazing. 

View solution in original post

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,700)