Changing the SortOrder on a gallery depending on a variable

[mjohnson17]

I have a gallery of items that is ordinarily filtered on a date value, 'Date Loaned'. This is the date an item was loaned out. 

I have a toggle button that sets the variable MultiReturn to True. This then changes the view of the gallery to allow us to quickly return multiple loans at once. 

I want the sort order of the gallery to change when MultiReturn is True to sort instead on the loan item number. For example, we may have items with the name Loan 3, Loan 14, Loan Adaptor 2, etc. The code I have (part of a much larger filtering code, I can provide it if necessary) is as follows:

Switch(MultiReturn, false, 'Date Loaned', true, Right(Loan.Value, 2))

 In the above code, the gallery successfully filters on 'Date Loaned', but when you activate MultiReturn, it says 'The value 21 cannot be converted to a date or time value' and the gallery goes blank.

What could be causing this?

[WarrenBelz]

Hi @mjohnson17 ,

I am assuming this code is in the Sort criteria of your filter - what is on the end of Loan.Value and can you please post the full code in Text.

[mjohnson17]

Some examples of Loan.Value are Loan 34, Loan Adaptor 2, Loan Device 13, etc. The thinking behind Right(Loan.Value, 2) is that it will get the number regardless of the full value.

The full filter code, under the gallery's Items value, is as follows. There are buttons on the page to filter the gallery in specific ways. 

Sort(
        Filter (
            Loans,
            IsBlank('Date Returned') && If(
                ExcludeStaffBox,
                'Full Name: Year Level'.Value <> "Staff",
'Full Name: Year Level'.Value = "Year 7" || 'Full Name: Year Level'.Value = "Year 8" || 'Full Name: Year Level'.Value = "Year 9" || 'Full Name: Year Level'.Value = "Year 10" || 'Full Name: Year Level'.Value = "Year 11" || 'Full Name: Year Level'.Value = "Year 12" || 'Full Name: Year Level'.Value = "Staff"
    ),
            If(
                NotCheckbox,
                Not(SearchLoanBox.Text in 'Full Name'.Value || SearchLoanBox.Text in Loan.Value || SearchLoanBox.Text in Notes || SearchLoanBox.Text in Reason.Value || SearchLoanBox.Text in SendReminder),
                SearchLoanBox.Text in 'Full Name'.Value || SearchLoanBox.Text in Loan.Value || SearchLoanBox.Text in Notes || SearchLoanBox.Text in Reason.Value || SearchLoanBox.Text in SendReminder
            )
        ),
        Switch(MultiReturn, false, Right(Loan.Value, 2), true, 'Date Loaned'),
        If (
            TimeFilterButton.Selected.Value = "↓",
            SortOrder.Descending,
            SortOrder.Ascending
        )
    )

[WarrenBelz]

Hi @mjohnson17 

Note that I have only addressed the Sort issue here and simply copied the rest of your code

Sort(
   Filter(
      AddColumns(
         Loans,
         "LoanSort",
         Right(Loan.Value, 2)
      ),
      IsBlank('Date Returned') && 
      If(
         ExcludeStaffBox,
         'Full Name: Year Level'.Value <> "Staff",
         'Full Name: Year Level'.Value = "Year 7" || 
         'Full Name: Year Level'.Value = "Year 8" || 
         'Full Name: Year Level'.Value = "Year 9" || 
         'Full Name: Year Level'.Value = "Year 10" || 
         'Full Name: Year Level'.Value = "Year 11" || 
         'Full Name: Year Level'.Value = "Year 12" || 
         'Full Name: Year Level'.Value = "Staff"
      ),
      If(
         NotCheckbox,
         Not(
            SearchLoanBox.Text in 'Full Name'.Value || 
            SearchLoanBox.Text in Loan.Value || 
            SearchLoanBox.Text in Notes || 
            SearchLoanBox.Text in Reason.Value || 
            SearchLoanBox.Text in SendReminder
         ),
         SearchLoanBox.Text in 'Full Name'.Value || 
         SearchLoanBox.Text in Loan.Value || 
         SearchLoanBox.Text in Notes || 
         SearchLoanBox.Text in Reason.Value || 
         SearchLoanBox.Text in SendReminder
      )
   ),
   If(
      MultiReturn,
      'Date Loaned', 
      LoanSort
   ),
   If(
      TimeFilterButton.Selected.Value = "↓",
      SortOrder.Descending,
      SortOrder.Ascending
   )
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[mjohnson17]

Thanks @WarrenBelz, this sort of works. Weirdly though, while it used to sort on 'Date Loaned' perfectly, when we have this code, it sorts only on the day of the DateTime value instead of the entire value, so 01/11/2022 comes before 31/10/2021.

'Date Loaned' is a Date and time value in Sharepoint, so I'm not sure why changing this code converts it to text.

It also sorts on LoanOrder in a way that isn't numerical. It goes 1, 10, 11, 2, 23, 3, 34 (for example). I had hoped Loan.Value would convert it to a number, but it doesn't seem to.

[WarrenBelz]

Hi @mjohnson17 ,

The only thing I have done is added a column to the sort with the last two characters of Loan (what type of field is this?) - I have not changed your 'Date Loaned' field in any way.

[mjohnson17]

'Loan' is a lookup field that returns the Asset number of an item being loaned from another list. 

It's bizarre and seems like a bug - the code works fine until I start messing with If statements at the end of Sort. This had happened before I posted on the forum, definitely not your fault!

[WarrenBelz]

@mjohnson17 ,

The code

Right(Loan.Value, 2))

should add the column you want to sort (I assume this is a Text value). If 'Date Loaned' is a Date/Time column in SharePoint, there is no reason it should sort any other way than chronologically. As an experiment, do the two sort functions separately and see if they work in isolation (still use the AddColumns on the Loan.Value)

[mjohnson17]

I've modified the code as requested and sorting by 'Date Loaned' works on its own, and sorting by LoanSort works on its own. When I put them together under an If statement, they stop working. 

Very strange. 

[WarrenBelz]

@mjohnson17 ,

If that is the case, there is only one thing left, which unfortunately does not help you - I have seen numerous instances where code in the Items of a gallery gets too complex and the control simply does not render it properly and this may be the case here. To test the theory, make a collection with the various options and use that as the Items of the. gallery