Sorting Columns with an AddColumns and Filter

Like (0) Share

Report

Posted on 2 May 2023 14:27:08 by AlexRay

I'm looking to do a filter on a list based on the value of a text box (txtCaseTrackerSearchBox)

Code I'm currently using in the formula section of the gallery is as follows:

SortByColumns(
AddColumns(
Filter([@'RWB Case Tracker'],
StartsWith(ClientID.Value, txtCaseTrackerSearchBox.Text) ||
StartsWith(ClientFirstName.Value, txtCaseTrackerSearchBox.Text) ||
StartsWith(ClientSurname.Value, txtCaseTrackerSearchBox.Text) ||
StartsWith(Product.Value, txtCaseTrackerSearchBox.Text)

)
,
"ClientID", 'Client ID'.Value,
"ClientFirstName", 'Client ID: Client First Name(s) (1)'.Value,
"ClientSurname", 'Client ID: Client Surname'.Value
)
,
ClientSurname
,
If(SortDescending1, SortOrder.Descending, SortOrder.Ascending)
)

I've had to do the add columns method as the columns involved in the SharePoint list are complex values for Client ID, Client ID: Client First Name(s) (1) and Client ID: Client Surname.

Any assistance would be great, if you need more info just ask.

Thanks in advance!

Categories:

Using Apps Using Formulas

All responses (4)

Answers (2)

Verified answer

AlexRay 23 on 03 May 2023 at 09:48:09

Like (0)

Report
Re: Sorting Columns with an AddColumns and Filter

Thanks for the assistance Bof, swapping to sort meant I could avoid my complex value problem, had to make a few minor tweaks to what you posted (as the columns were no longer being defined I couldn't use the column names in double quotes)
Sort( Filter('RWB Case Tracker', StartsWith('Client ID'.Value, txtCaseTrackerSearchBox.Text) || StartsWith('Client ID: Client First Name(s) (1)'.Value, txtCaseTrackerSearchBox.Text) || StartsWith('Client ID: Client Surname'.Value, txtCaseTrackerSearchBox.Text) || StartsWith(Product.Value, txtCaseTrackerSearchBox.Text) ), 'Client ID: Client Surname'.Value, If(SortDescending1, SortOrder.Descending, SortOrder.Ascending) )

Thanks again Bof!
Verified answer

v-bofeng-msft on 03 May 2023 at 01:37:21

Like (1)

Report
Re: Sorting Columns with an AddColumns and Filter

Hi @AlexRay ,

Please try:

Sort( Filter('RWB Case Tracker', StartsWith('Client ID'.Value, txtCaseTrackerSearchBox.Text) || StartsWith("ClientFirstName", txtCaseTrackerSearchBox.Text) || StartsWith("ClientSurname", txtCaseTrackerSearchBox.Text) || StartsWith(Product.Value, txtCaseTrackerSearchBox.Text) ), 'Client ID: Client Surname'.Value, If(SortDescending1, SortOrder.Descending, SortOrder.Ascending) )

Best Regards,

Bof
AlexRay 23 on 02 May 2023 at 16:56:33

Like (0)

Report

Re: Sorting Columns with an AddColumns and Filter

Having done some searching around, found another slightly different formula where the add columns is split out with a with to make reading the code easier.

Even with this method it's still not searching on the new columns of ClientFirstName and ClientSurname.

With(
{_items:AddColumns(
'RWB Case Tracker',
"ClientFirstName", 'Client ID: Client First Name(s) (1)'.Value,
"ClientSurname", 'Client ID: Client Surname'.Value
)
},

SortByColumns(
Filter(
_items,
StartsWith('Client ID'.Value, txtCaseTrackerSearchBox.Text) ||
StartsWith("ClientFirstName", txtCaseTrackerSearchBox.Text) ||
StartsWith("ClientSurname", txtCaseTrackerSearchBox.Text) ||
StartsWith(Product.Value, txtCaseTrackerSearchBox.Text)
),

"ClientSurname"
,
If(SortDescending1, SortOrder.Descending, SortOrder.Ascending)
)
)

For some additional context, when looking at the record in the gallery, it does successfully use the complex column values created in the gallery formula
AlexRay 23 on 02 May 2023 at 16:31:42

Like (0)

Report

Re: Sorting Columns with an AddColumns and Filter

Managed to get the gallery working partially, the complex values still aren't working though for search or sort purposes.

Latest version of formula:

SortByColumns(
AddColumns(
Filter('RWB Case Tracker',
StartsWith('Client ID'.Value, txtCaseTrackerSearchBox.Text) ||
StartsWith("ClientFirstName", txtCaseTrackerSearchBox.Text) ||
StartsWith("ClientSurname", txtCaseTrackerSearchBox.Text) ||
StartsWith(Product.Value, txtCaseTrackerSearchBox.Text)
)
,
"ClientID", 'Client ID'.Value,
"ClientFirstName", 'Client ID: Client First Name(s) (1)'.Value,
"ClientSurname", 'Client ID: Client Surname'.Value
)
,
"ClientSurname"
,
If(SortDescending1, SortOrder.Descending, SortOrder.Ascending)
)