Gallery view SortByColumn with 2 inputs to sort

[Chwilkinson]

Hello,

I have a gallery that I am wanting to add a default SortByColumn function to.

I want to sort the column by interval which is made up of 2 values in the datacard

Those values are a text input box so that the user can input a number value

And a drop down box with cycle(s), use, Minute(s), Hour(s), Day(s), Month(s), Year(s)

When the form is submitted they are combined to give a value of, as an example, 1 use or 6 month(s)

Update = TextInput1.Text & " " & Dropdown1.Selected.Value

With a sort by ascending it orders the columns by the number only.

So how can I sort the gallery to first sort by the unit in the order of cycle(s), use, Minute(s), Hour(s), Day(s), Month(s), Year(s) and then the number value so that I get , as an example, the 3 month(s) gallery entries before the 6 month(s)

Any help would be really appreciated,

Thanks

Chris

[NandiniBhagya20]

Hi @Chwilkinson ,
You can use something like below - 

Sort(SortByColumns(AddColumns(SPList,"sortIntervalCol",Last(Split(Interval, " ")).Value, "sortDayCol",Value(First(Split(Interval, " ")).Value)),"sortIntervalCol",["Minute(s)", "Hour(s)", "Day(s)", "Month(s)", "Year(s)"]),sortDayCol,SortOrder.Ascending)

[Chwilkinson]

Hi @NandiniBhagya20, 

Thanks for the reply

I've updated the item code but that doesn't seem to work.

Could you maybe explain how it works so I can see what needs to be tweaked?

Thanks

Chris

[NandiniBhagya20]

Hi @Chwilkinson ,

The code splits "Interval" column as it has text in the format 6 Months(s) with a space in between. The number 6 and Month(s) text is extracted from it and used for sorting purpose. 
All the logic is based on the assumption that Interval column has a string separated by space with number and text in it.

[Chwilkinson]

Thanks @NandiniBhagya20 

I see, but its still sorting by only the number value once split up into the collections

so I need to sort by the sortIntervalCol in the order i want to specify first and then by sortDayCol in ascending order?

Can that be done?

Thanks

Chris

[Chwilkinson · Accepted solution]

I have managed to get the code working with some small modifications

I had to use the SortByColumns function twice & strangely put the sortDayCol before the sortInterval even through the interval was the first column to be ordered.  It works though, even though I'm not 100% sure why!! 

If anyone know and can explain 😀!!

SortByColumns(
    SortByColumns(
        AddColumns(
            Filter(
            'Lab Maintenance Schedule',
             varAssetID = Title),
                 "sortIntervalCol",Last(Split(Interval, " ")).Value, 
                 "sortDayCol",Value(First(Split(Interval, " ")).Value)),
"sortDayCol",SortOrder.Ascending),
"sortIntervalCol",["Cycle", "Use", "Minute(s)", "Hour(s)", "Day(s)","Week(s)", "Month(s)", "Year(s)"]
)