GroupBy, Filter & Sort Not Sorting Numerically

[Lander]

Hi all,

Hoping for a little help - I'm building an app for my company which helps track checks done on buildings and rooms, but I'm a little stuck on the sorting element of a gallery. The backend is a SharePoint Online List and a form is feeding into that with no problems. There can be multiple checks done of a day, and the requirement is that only one of these be shown at any one time as an 'overview' of the checks completed for that building & room within the last 24 hours.

So, I've got a separate screen feeding back from the source list and managed to get it to show with a mix of SortByColumns, GroupBy & Filter with the gallery in question's Item code as below:

SortByColumns(
	GroupBy(
		Filter(BuildRmCheckList,’Room and Building’.Value = RoomBuildDD.Selected.Value,Created > DateAdd(Now(),-24,Hours) or Modified > DateAdd(Now(),-24,Hours)),
“Room”,
“GroupedCol”),”Room”,Ascending)

 To give some context:

• The first filter is off a DropDown field in SPO which is a list of all the buildings and their locations in the company.
• RoomBuildDD is a DropDown for the user to choose which building and location to look at and for the gallery to refresh specifically to that location
• The "Room" column on SharePoint is a Text column because some buildings only have numbered rooms, others will have lettered & numbered rooms.

All of this filters the gallery correctly, but for some reason the gallery filters like this:

1	11	2	21
3	31	G1	G2

What I'm trying to achieve is to have them sort in proper numerical order, not just all 1's then all 2's etc. Does anyone know how best to go about this? I've been scratching my head for ages trying to sort it but can't seem to nail it. If there's another option such as a nested gallery with the top-level gallery being the buildings and locations and child gallery being rooms, I'd love to hear about it, as I am open to a lot of ideas trying to make this work!

Thank you in advance 🙂

[BrianS]

I would guess it is because of how the Text is treated. It is not sorting by numbers, but text. If you want them to sort as numbers you will need to add leading zeros - "1" becomes "01" or "001" depending on how may digits are in the largest room number. Then they will sort correctly. 

[Lander]

Hi Brian,

Thank you, this sounds like an idea to try - can you advise how or is there a link I can go be nosy at?

Thanks

[BrianS]

You just need to add the leading zeros in the "Room" Sharepoint data. There may be a programatic way to do it, by counting the length of the string, but unless you have a lot of data it is probably easier to just go to the list and change it by hand.

[Lander]

I've been tinkering away since your message, and unfortunately even adding in 0's doesn't make it work - I used a calculated column to add the 0 and it didn't sort off that either. I duplicated the data source and copied the screen that the gallery was on already and changed the settings in the duplicate, and only when it was a number format did it properly show in the gallery.

Is there no other way to do it? I can't find anything that would programatically convert the text string to a number string (even if just for the sort) and let it run that way to do what I need.

Thank you for your help so far though!

[BrianS]

Not sure how you are doing the sort. I created a collection with text values, here are my results:

[Lander]

I've duplicated my original source list and then sorting without any special stuff, like so:

Sort(BuildRmCheckListDuplicate,Value('Room Number'))

It does exactly what I need but this gives me a delegation warning, and as there will be more than 500 items in the list this is a no-go, unless the additional filtering such as when it was last created/modified will bypass this (there won't be 500 at any one time in that instance). Doing it as 'Room Number'.Value was not firing either.

The source SPO column was changed before I realised that this would cause issues in PowerApps, hence the difference in names.

[BrianS]

I don't think you need the Value - especially if some rooms have letters.

Sort(BuildRmCheckListDuplicate, "Room Number" )

Should work and should be deligable

[Lander]

Indeed it does, but I can't make it do that in the gallery when mixed in with GroupBy, that's where my problem is at the moment 😀

[BrianS]

I must be a little confused - I thought you had the data that you wanted showing, it was just in the wrong order. If you use the original formula with the new data (padded with zeros) does it no longer show in the gallery?