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

Sorting Form Choices Based on Another Column in a Sharepoint List

Hello, 

 

I have a SharePoint list called: 'EventWeeks', that has three columns as follows: 

 

Title = Concatenation of SystemWeek and SelectionWeek

SystemWeek (Single line Text) = Number representing a week in the year

SelectionWeek (Single line Text) = Corresponding user-friendly version of the SystemWeek

 

These are constant and cannot be changed by the user.

 

In a separate SP list: 'Events', I lookup up the SystemWeek and the corresponding SelectionWeek, so that they can be displays as Choices in a form.

 

Within that form I have a field that asks the user which weeks they want the event to run in. It is multi-select and needs to display SelectionWeek (the user-friendly version), but needs to be sorted according to the order of SystemWeek within the list: EventWeeks.

 

In the SharePoint list, it is ordered the way I'd like it to be shown, but the PowerApp combobox is in a seemingly random order:

 

RupertCoyb_0-1629292750746.png

 

Is there a way that I can order the options above to the ascending order of another column i.e. SystemWeeks?

 

Many thanks in advance for any help you can offer!

 

1 ACCEPTED SOLUTION

Accepted Solutions
RupertCoyb
Frequent Visitor

For anyone who stumbles onto this post with the same issue, I managed to solve this by changing my 'Weeks' field to use the SharePoint List that the lookup is performed on instead of using the Choices options. 

 

This was sorted in the SharePoint list in the correct order, so I didn't have to sort on the field.

 

Then I had to patch the field to my SharePoint list, but because the column is a LookUp, there were some additional steps necessary: 

 

Patch('Timetabling-Events', Defaults('Timetabling-Events'),
{
Title: "RecordTitle",

WeeksSelection: ForAll(WeeksVal.SelectedItems,{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: ThisRecord.ID,
Value: ThisRecord.WeekSelection
}),}
)
);

 

This cycles through all of the selected items in the combobox, and adds them to the SP LookUp list as LookUp items.

 

View solution in original post

3 REPLIES 3
Nogueira1306
Super User
Super User

Hey! 

In the combobox you need a "Sort" function

Documentation:
https://docs.microsoft.com/pt-pt/powerapps/maker/canvas-apps/functions/function-sort 

Hey Nogueira, 

 

Thanks very much for your reply. I've had a look through the documentation, but I'm not quite there with it - still receiving errors. I think I need to do something like this: 

 

SortByColumns(

    AddColumns(

        Choices([@'Timetabling-Events'].WeeksSelection),

        "SITSWeeks",

        ThisItem.'WeeksSelection:SITSWeek'),

"SITSWeeks",

Ascending)

 

So I'm adding a new column called "SITSWeeks" which is the system week (1, 2, 3, etc.) and then wrapping a sortby column function around that. But it doesn't seem to want to sort on the newly created "SITSWeek" column, it seems to see it as a Text type, rather than a column to sort on. 

 

Any help would be greatly appreciated! 🙂

RupertCoyb
Frequent Visitor

For anyone who stumbles onto this post with the same issue, I managed to solve this by changing my 'Weeks' field to use the SharePoint List that the lookup is performed on instead of using the Choices options. 

 

This was sorted in the SharePoint list in the correct order, so I didn't have to sort on the field.

 

Then I had to patch the field to my SharePoint list, but because the column is a LookUp, there were some additional steps necessary: 

 

Patch('Timetabling-Events', Defaults('Timetabling-Events'),
{
Title: "RecordTitle",

WeeksSelection: ForAll(WeeksVal.SelectedItems,{
'@odata.type':"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
Id: ThisRecord.ID,
Value: ThisRecord.WeekSelection
}),}
)
);

 

This cycles through all of the selected items in the combobox, and adds them to the SP LookUp list as LookUp items.

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,634)