Sort by List ID number

[jbrines]

Hi there,

I am trying to sort the app by ID number.

I found this solution on here but it doesn't work for my app - Solved: Sort by date Sharepoint list - Power Platform Community (microsoft.com)

I am using - SortByColumns(Filter(Client Issue; StartsWith(ID; TextSearchBox1.Text)); "ID";
If(SortDescending1; Descending; Ascending))

[jbrines]

@RandyHayes both formula come back with blank records.

Your point about 2000 records leads on to the next thing I need to investigate, I was hoping to configure it so only didn't display Closed or Completed records but that is for another time 😄

Where are you from, I am in Scotland spending my evening doing this to the delight of my wife 🤣

J

[RandyHayes]

@jbrines 

Okay, then if there were no records from either, then there is a discrepancy with the Display Name as well.

Roll back to the label in your screen and in your gallery.  Change the Text property of the screen label to User().FullName and the label in the Gallery text property to ThisItem.'Created By'.DisplayName 

See what the difference is on that.

Greetings to Scotland!  Getting late in the evening for you.  I'm East coast US.  Coming to the end of the work day, but not the whole day yet 😉 

I'm sure your wife is elated that you are trudging away at PowerApp on a late Friday evening 😂

[jbrines]

@RandyHayes I worked for 15 years with a company based in Elkhart Indiana, spend several times a year there and in New Hampshire. Wife is happy she is listening to her fav radio station and has a glass of Red. just past 9pm here 😉

Label outside Gallery - John Brines

Label Inside Gallery - blank

Could these issue be down to my setup on Office 365?

J

[RandyHayes]

@jbrines 

Glad you could make it over the pond for a while!  I have to make my way over there sometime.  Good plan, pour some more red and let her enjoy 😁

That is odd.  You should have a display name there.  Something doesn't seem right with the setup.  Definitely check through your O365 settings and see if there is something wrong there.  Also go to the SharePoint admin center and do a profile search and see if there is something odd with the profile.

I've got to step away for a couple hours, but will check back in later and over the weekend.

[jbrines]

@RandyHayes 

Look at O365 and Azure AD and my User Principal Name is all in lower case, only a few of or users ae like that. I think it is because when we started our Teams trial I imported all our users before creating the AD sync last month. Just need to figure out how to change my User Principal Name

J

[RandyHayes]

@jbrines 

There are ways to work around it.  However, you should have a display name for your records.

I would take a look into the SharePoint admin center (through your O365 admin center - "...Show all -> SharePoint" from the left menu.  If it's using the new SharePoint admin look, then click on More features on the left menu.  Then go into User Profiles.  This will bring up the standard User Profiles center.  Under People, choose Manage User Profiles.  Then in the Find Profiles, enter part of your name and click Find.  You should see your account name come up.  Then click on the little dropdown menu - you have to hover over the Account name and you'll see the little arrow appear to the right of the claim name (Account Name). Then choose Edit My Profile and look through all the information that brings up to see if you spot anything unusual. 

Misleading as it is for many, SharePoint maintains its own profile information.  It gets this initially from Office 365 if it is not entered directly.  Once the profile (claim) is made, it only gets updated every once and a while automatically from Office 365.  If there are only case differences but the underlying value is the same, it will not bother to update.

Anyway, just take a look over that stuff and see if there is anything interesting.

If there is nothing to change and that part of it can't be worked around, then we can pull out some other tricks to make this happen.  #1 trick - write the lowercase email address of the user to a column in the list just for that.  Then it will always be there in a lower case format and can be filtered without any delegation issues very easily.

Let me know what you discover.  And I hope you got to enjoy some red too before the evening came to a close.

[jbrines]

@RandyHayes The SIP address was in lower case, I have changed it to John.Brines@Domain.com

[RandyHayes]

@jbrines 

Not sure if that will have any significant impact, but the more troubling thing would be if it does...that would mean changes to all the accounts that would access the app.

So...I believe the best work around is to utilize the StartsWith function.  This is a delegable function and is case insensitive.

Let's throw caution to the wind and put this formula in:

With({usrEmail: User().Email},
    SortByColumns(
        Filter('Client Issue', 
            StartsWith('Created By'.Email, usrEmail) &&
            StartsWith(Title, TextSearchBox1.Text)
        ),
        "ID",
        If(SortDescending1, Descending, Ascending)
    )
)

That should do some magic.  Let's see if that moves the goal posts. 

[jbrines]

@RandyHayes 

Working but still shows the delegate message.

[RandyHayes · Accepted solution]

@jbrines 

Yes, you will see a warning on it.  I am not sold yet that it is a real warning as I've not vetted it on large datasets yet and technically it is equivalent to what I will mention next...that will remove the warning.

Typically, the User information is captured ahead of time in a global variable.  This is because User() is an expensive (performance) function.  Also because you cannot use that function in a filter and avoid delegation.  

SO...the trick is to set a global variable in your OnStart of your App.  There you can put the following formula:

    Set(glbUser, User())

This will set a global variable called glbUser and that variable will then have all the information you need for later.  By the way, you will see many people separate this out into lots of variables (ex. one for email, one for name, one for job title, etc...) it is not needed.  All the information you need is in one easy to maintain variable.

NOW, with that in place, your formula changes to the following:

SortByColumns(
    Filter('Client Issue', 
        StartsWith('Created By'.Email, glbUser.Email) &&
        StartsWith(Title, TextSearchBox1.Text)
    ),
    "ID",
    If(SortDescending1, Descending, Ascending)
)

This will give you results without the delegation warning.

On close observation you will see my skepticism on the initial warning.  This formula is equivalent to the first formula.  In the first formula we assign a variable just the same, except that we did it in a scoped variable (the With - meaning that the variable only is available within the parenthesis of the with statement).  So, what is used in the filter is equivalent.  Like I say...skeptic if it is a real issue.

But, for the sake of your entire app, you will usually reference the current user often in the app, so, a good design practice is to capture the results of the User() function ONCE and not over and over.  This is usually all done in the OnStart of the app.

If you are not familiar yet with the OnStart, you will find it if you click on the App in the left tree pane of the designer.  From there you will change the OnStart action formula.

Once you have that DON'T FORGET, you need to run the OnStart manually when you are in the designer.  This is done by clicking on the blue circled menu in the above picture and choosing "Run OnStart". Once you do that, your glbUser variable will be populated with the User information and can be used properly in the Items formula of the Gallery.