Sort a Lookup Field

[golfnutt82]

Hello,
I would have thought this would be easy but for me I guess its not.

I have a field that is a look up to the Models list and I am trying to filter the lookup descending.
The reason is that when a new item is created in Device type the Model No field would have two choices.

1. Add New
2. Select - Add New

I want the Default value to display Select - Add New

But as you can see below, I am getting an error when attempting to sort the lookup.

The Model No field Items property is filtered depending on the Device Type field.

Filter(Model,'Device Type'= DataCardValue33.Selected.Result)

I have a workflow that runs when a NEW Device Type is added and creates two entries in the Device Type list with "Add New" and "Select - Add New"
In the On Change property/event for Model No I navigate to a new screen where a user can enter a new Model when the user selects "Add New" but if the control default is already set to "Add New" my On Change only works if I deselect "Add New", change it to "Select - Add New" and then chose "Add New".
Not acceptable.

Any direction is appreciated.

[Drrickryp]

@golfnutt82 

Welcome to PowerApps h3ll.  See https://powerusers.microsoft.com/t5/Power-Apps-Community-Blog/The-Ten-Commandments-of-SharePoint-in-...  for why you are having difficulties. 

[Akser]

For the love of god, could you please try this formula? 🙂

SortByColumn(AddColumns(Model, "SortColumn", 'Model No'.Value), "SortColumn", Descending)

And please don't replace 'SortColumn' with 'Title' you like did before.

[golfnutt82]

Wow, Many of those I do but some no.
Now I have to rethink many things.
Normally, I have been using text fields for everything that is look up related and doing the work in the Power App.
But in this situation its a bit different due to the cascading dropdown field "Model".
I will need to rethink this.
Thank you for the link.

[Drrickryp]

@golfnutt82 

SharePoint was developed long before PowerApps and choice, lookup and calculated column types are very helpful for SharePoint users but create a tangled mess for PowerApps where all of these can be replaced with PowerApps functions that work much better in that environment.  In some cases, the PowerApps team has developed work arounds for dealing with them but I have found that avoiding them altogether, in the long run, works better and more efficiently.  I just use SharePoint slot, number and dates exclusively and have not had to deal with many of the issues raised in this forum regarding SharePoint as a data source. 

[golfnutt82]

[Akser]

Ok I give up. You say that you renamed the 'Title' column to 'Model No' but then you are saying that this is a Lookup Column. You can rename the default Title column of course but you cannot change the column type, so how you managed to both rename the Title column and made the same column a LookUp column is beyond me.

[golfnutt82]

So how would you handle my situation where my Model No field on the form is a cascading lookup to the Model List where it is related to the Device Type field DataCardValue34?

DataCardValue33 holds the Model No choices based on DataCardValue34 the Device Type choices

DataCardValue33 - Items Property is: Filter(Model,'Device Type'= DataCardValue33.Selected.Result)
DataCardValue34 - Items Property is: Sort(Distinct('Device Type','Device Type'),Result)

All I want to do is sort DataCardValue33 so that the default choice is "Select - Add New"

I have been getting assistance from Akser but no luck as of yet.
Very frustrating.

[golfnutt82]

I really appreciate your help and I know this is frustrating.
SharePoint and Power Apps should work well together but I am finding that's not the case.
More and more I am finding that I can and need to give the Power to PowerApps as stated by Drrickryp.

"SharePoint was developed long before PowerApps and choice, lookup and calculated column types are very helpful for SharePoint users but create a tangled mess for PowerApps where all of these can be replaced with PowerApps functions that work much better in that environment." 

I am doing the lookup from within Power apps not SharePoint.
All my fields are SLT fields in my "Lookup Lists".
Lists are Device Type and Model
I did rename my "Title" fields in those lists so I am not sure if that wasn't something I should do.
I also need to go back into those lists and not make any fields mandatory and let power apps do that.
My Device Type field Items property is: Sort(Distinct('Device Type','Device Type'),Result)
My Model No field Items property is: Filter(Model,'Device Type'= DataCardValue33.Selected.Result)

[golfnutt82]

I updated my workflow that adds the values to the Model list to create the "Select - Add New" choice as first value shown in the drop down and "Add New" as the second value that the user must select in order for my code to work.
Once again thanks for your help.

[golfnutt82]

Hi Drrickryp,
I created a work around using my workflow.
I updated my workflow that adds the values to the Model list so that the "Select - Add New" choice is the first value shown in the drop down and "Add New" as the second value that the user must select in order for my code to work.
Once again thanks for your help.