Sorting Form Choices Based on Another Column in a Sharepoint List

[RupertCoyb]

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:

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!

[Nogueira1306]

Hey! 

In the combobox you need a "Sort" function

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

[RupertCoyb]

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 · Accepted solution]

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.