Search bar to search multi column

[hankycheng0621]

Hi All

I want to edit the search bar to search multi-column records but fail, it's wrong coding as below? Thanks for help.

SortByColumns(Filter([SiteLog], StartsWith(TicketNumber, TextSearchBox1.Text)), "Title", "Name", If(SortDescending1, Descending, Ascending))

[poweractivate]

@hankycheng0621 

You must use this formula instead:

SortByColumns(Filter(SiteLog, StartsWith(TicketNumber, TextSearchBox1.Text)), "Title", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending), "Name")

The following would also be valid:

SortByColumns(Filter(SiteLog, StartsWith(TicketNumber, TextSearchBox1.Text)), "Title", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending), "Name", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))

However, the formula you gave results in an error.

According to 

Sort Syntax - Power Apps - docs.microsoft.com

SortByColumns( Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, ... ] )

Table - Required. Table to sort.

ColumnName(s) - Required. The column names to sort on, as strings.

SortOrder(s) - Optional. SortOrder.Ascending or SortOrder.Descending. SortOrder.Ascending is the default. If multiple ColumnNames are supplied, all but the last column must include a SortOrder.

---

The key is that

"If multiple ColumnNames are supplied, all but the last column must include a SortOrder."

In your case, you did the opposite. You gave the last column a SortOrder, but nothing else.

The formula bar in my case gave an error when using your formula of:

SortByColumns(Filter(SiteLog, StartsWith(TicketNumber, TextSearchBox1.Text)), "Title", "Name", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))

The above formula, which is similar to the one you gave in your post, gave me this error:

The specified column 'ascending' does not exist or is an invalid sort column type

I am not sure if this is the error you got.

However, try using this formula instead:

SortByColumns(Filter(SiteLog, StartsWith(TicketNumber, TextSearchBox1.Text)), "Title", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending), "Name")

The following would also be valid:

SortByColumns(Filter(SiteLog, StartsWith(TicketNumber, TextSearchBox1.Text)), "Title", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending), "Name", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending))

Working sample msapp 

I have attached a working sample msapp file app22.msapp if you would like to import a full, working example yourself for your convenience. The example contains a Collection to simulate a data source.

To use the sample msapp attached, follow these steps:

1) Download the msapp file attached to this post to Desktop or a folder of your choice, by clicking on it from this post (the attachment is at the very bottom of this post.). 

2) Create a new, blank Power App Canvas App

3) Go to File -> Open -> Browse

4) Navigate to location of .msapp file from Step 1, select it, and press the "Open" button.

5) The working example msapp file should load

6) You can try the app and check the working formula and setup as well in the app and see if it gives you an idea what might be wrong in your app.

7) Click on Screen2 and back to Screen1 initially once or twice for best results, this is so OnVisible can be triggered so the simulated Collection can be initialized and so you can see the Gallery contents right away.

Check if it helps @hankycheng0621 

[poweractivate]

@hankycheng0621 

Let me explain you in more detail why your formula doesn't work in case it helps.

First,

According to 

Sort Syntax - Power Apps - docs.microsoft.com

SortByColumns( Table, ColumnName1 [, SortOrder1, ColumnName2, SortOrder2, ... ] )

Table - Required. Table to sort.

ColumnName(s) - Required. The column names to sort on, as strings.

SortOrder(s) - Optional. SortOrder.Ascending or SortOrder.Descending. SortOrder.Ascending is the default. If multiple ColumnNames are supplied, all but the last column must include a SortOrder.

So why does "all but the last column" have to include a SortOrder anyway?

Because SortByColumns looks for

SortByColumns(source, column, order, [ column2, order2, column3, order3])

If you provide SortByColumns(source, column, column, order)

(which is what you did)

SortByColumns(
    Filter(SiteLog,StartsWith(TicketNumber, TextSearchBox1.Text))
    ,"Title"    
    ,"Name"
    ,If(SortDescending1, SortOrder.Descending, SortOrder.Ascending)
)

it will fail

because It will interpret "Name" as an "order'

Since "Name" is not an "order" that's why you get the error.

Whereas, 

If you provide SortByColumns(source, column, order, column)

The last column not being followed by an order, that would be fine - that's why "all but the last column" requires an order if multiple columns are provided.

Makes sense?

The only thing that you might be wondering, is:

what's with that seemingly bizarre error?

The specified column 'ascending' does not exist or is an invalid sort column type

Actually, the error...makes sense!

A similar error might also show up instead of:

The specified column 'descending' does not exist or is an invalid sort column type

Here's why:

If you provide SortByColumns(source, column, column, order)

(which is what you did)

SortByColumns(
    Filter(SiteLog,StartsWith(TicketNumber, TextSearchBox1.Text))
    ,"Title"    
    ,"Name"
    ,If(SortDescending1, SortOrder.Descending, SortOrder.Ascending)
)

It's expecting you to provide SortByColumns(source, column, order, column)

but if you instead provide    SortByColumns(source, column, column, order)

Notice carefully how not only is the 3rd argument given a column when it should be an order,

but more importantly, the 4th argument given is an 'order' when it should be a 'column'

So that means:

(now I'll use an example that will give the 'descending' version of the error rather than 'ascending', by just slight changing the 4th argument a little bit)

SortByColumns(
Filter(SiteLog,StartsWith(TicketNumber, TextSearchBox1.Text))
,"Title" 
,"Name"
,If(SortDescending1, SortOrder.Ascending, SortOrder.Descending)
)

See how 'descending' is attempted to be interpreted as a 'column'

So of course, "the specified column 'descending' does not exist" - which makes sense now, right?

The specified column 'descending' does not exist or is an invalid sort column type

So why does the error say

"the specified column 'descending' does not exist or is an invalid sort column type"?

I think the choice of words has an interesting effect here, because of the fact that the function SortByColumns happens to be dealing with columns and also "sort order", so it might be difficult to quickly glance at this error and even understand what is going on, and it might also easily be confounded with the sort order and give the impression that the error is complaining about the sort order, when interestingly enough, the error is actually complaining about the column (i.e. the 4th argument being a sort order where there should be a column)!

Likely, invalid sort column type here might simply mean that if the column happened to exist, Power Apps suspects that the column might not be a valid type of column that could be used for sorting with SortByColumns.

In other words, Power Apps does not know for sure whether the column really does not exist, or whether it does exist but is just not a valid kind of column that could be used in SortByColumns - so as a result, maybe that is why this particular error is worded in this way.

For your case, the first part of the error is most important:

the specified column 'descending' does not exist or is an invalid sort column type

The error is ultimately caused, by 'ascending' (or 'descending') being fed into a column argument!

That's why it says 

the specified column 'descending' does not exist

or 

the specified column 'ascending' does not exist

So since "the specified column does not exist", then the first thing you should do is check if you have put a SortOrder where you should have instead put the name of a column (which is what went wrong here).

Make sure that if you provide more than one column or multiple columns to SortByColumns, that every column is followed by a sort order, so column, order, column, order, etc.... After that, you can only optionally remove only the very last sort order for the very last column (i.e. the very last argument) - but you can remove nothing else. Then it should work correctly.

You can also apply the exact same rule even for just one column. You can either provide the column and the order, or just the column, and the one and only column will be that last column.

So just make sure to always specify a sort order after every single column, and after you've done that, you can optionally decide if you want to take the very last sort order out, or leave it in. You can not take out anything else. If you make sure to do this, you would be less likely to face this specific kind of issue again.

Check if this helps as well @hankycheng0621 

[hankycheng0621]

Thanks for your detailed help @poweractivate 
After try your 2 methods it's successful to search TicketNumber but fail if Name on the same searchBox. Is it a problem with my code? Thanks

SortByColumns(Filter(SiteLog, StartsWith(TicketNumber, TextSearchBox1.Text)), "Title", If(SortDescending1, SortOrder.Descending, SortOrder.Ascending), "field_9")

[poweractivate]

@hankycheng0621 

What is the error shown in the formula bar when you hover over it?

I think it is because the column is called something else, not "Name" in your case.

Try to find out what the column is really called that has the name of the ticket, and use that instead of "Name".

[hankycheng0621]

@poweractivate  it haven't error, just nothing happen if search "Name"

[poweractivate]

Filter(SiteLog, StartsWith(TicketNumber, TextSearchBox1.Text))

The Filter  is the only part of the formula that performs the search

You are only filtering on TicketNumber.

Everything else in the formula is from SortByColumns and only determines the order of how the returned results are displayed in the Gallery(the Gallery Items)

I'll see if I can tell you shortly how you should change the formula.

[poweractivate · Accepted solution]

@hankycheng0621 

Try something like this instead:

SortByColumns(
    Filter(SiteLog,StartsWith(TicketNumber, TextSearchBox1.Text) || StartsWith(Name, TextSearchBox1.Text))
    ,"Title"    
    ,If(SortDescending1, SortOrder.Ascending, SortOrder.Descending)
    ,"Name"
    ,If(SortDescending1, SortOrder.Ascending, SortOrder.Descending)
    
)

Check new version of sample app app22b.msapp attached to this post as well in case it helps, with the new formula above in the app.

For more info on the Filter function, check on this guide:

Filter - docs.microsoft.com

Check if it helps @hankycheng0621 

[hankycheng0621]

@poweractivate  Thanks for help and give me more knowledge about the search and filter functions. Now I success create a search box with multi-column search now. thanks for your help again.