Sort function is showing 0 last

[CarlHRVA]

Hello PowerApps community! It's been awhile since I posted. I'm having some trouble with the sort function and wondering if I could get some assistance. I am trying to sort a gallery in ascending order by the "file name with extension" value of the items and it's placing files that start with 0 at the end of the list. I am also seeing unexpected behavior with a particular item with a capital letter as the second value. If the items name starts with 2, 2a, 2b, 2c etc, they show up as expected but if there are items named 9, 9a, 9B, and 9c, the 9B item shows up much further down the gallery. Any help is greatly appreciated. @wyotim I know it's been quite a few months but if you're still around, your brilliance is always welcomed!

Thanks,

Carl

[zmorek]

Can you send us screenshots of the gallery, its Items() property, information on the DataSource/its columns? This would help greatly. I wonder if it's a true lexicographical sort issue, or a mismatch of columns... There's lots that could be going on.

[CarlHRVA]

Hi @zmorek , 

Sorry for the delay. 

The gallery is a nested gallery. The Data Source is a library in SharePoint and the formula I have in place now that is causing the issue is: 

Sort(
    Filter(
        IDIQ,
        'Folder path' = fullPath And ('Mod No.' = 0 And "CONSULTANT" in ThisItem.'File name with extension' And 'Top Level' = false)
    ),
    Ascending
)

I've tried using SortByColumns but then what happens with larger data sets is the it goes 3, 30, 31, etc. Please let me know if you need any more info.

Thanks,

Carl

[CarlHRVA]

The text value for the items I'm trying to sort is ThisItem.'File name with extension'.

This is how it is sorting them, with the zero value at the bottom.

[CarlHRVA · Accepted solution]

I was able to get this mostly working. My formula looks like the below: 

SortByColumns(
    AddColumns(
        Filter(
            IDIQ,
            'Folder path' = fullPath And ('Mod No.' = 0 And "CONSULTANT" in ThisItem.'File name with extension' And 'Top Level' = false)
        ),
        "SortOrder",
        Value(
            Left(
                'File name with extension',
                Find(
                    ".",
                    'File name with extension'
                )
            )
        )
    ),
    "SortOrder"
)

 This doesn't account for files labeled 2A, 2B, 2C etc but we will need to enforce a naming convention that does not use these characters in the future. If anyone has any idea how I can sort on those characters or just the numbers, that would be welcomed, otherwise I will mark this reply as the solution.

Thanks,

Carl