PowerApps search function on Excel Online

[jbrit2020]

Hello all,

For the first time since starting to use powerapps I will have to create an app using Excel Online and I'm facing the first issues.

I can't seem to Search/Filter/Sort or anything similar with my Excel table.

Here's the piece of code I applied on the Gallery: Search(Table2,TextInput1.Text,"Closed Date","Accept into Risk Register")

The exact same formula but in the SQL table works perfectly.

Error below:

Is this an Excel Online connector limitation? Is there any other way around it?

Regards

[RandyHayes]

@jbrit2020 

Please consider changing your Formula to the following:

    Search(Table2,TextInput1.Text,'Closed Date', 'Accept into Risk Register')

Note: Columns that have spaces in them should be surrounded by SINGLE quotes, not double quotes like you had.

I hope this is helpful for you.

[jbrit2020]

Thanks for replying. Still doesn't work. The double quotes is the automatic format that powerapps suggests me to use when I start typing. When I use your recommendation it says there are invalid arguments.

[RandyHayes]

@jbrit2020 

<Palm plant to head.>  Sorry - Totally missed the Search.  Yes, Search will need its fields double quoted.

Is your Date column formatted as a date?  Or in other words, does PowerApps see it as a date?  If so, you will not be able to use that in a Search function.  Search will only search text columns.

[jbrit2020]

It is formatted as date, but I just tried removing that column and using a normal text column instead of date, and the result is still the same. could it be because im using Excel Online as the connector?

[RandyHayes]

@jbrit2020 

If you are actually getting data from the excel connector, then there is no issue with that.

However, if you did get this error as you displayed, it is most likely "stuck".  Meaning that you can fix your formula, but it will still show.

First try to fix your formula to only search text columns and then save and exit out of the designer.  Then go back in and see if the error is cleared.

[jbrit2020]

ok, to make sure I will create a brand new canvas app from scratch and connect to the file and recreate the formula just for text columns.

[RandyHayes]

@jbrit2020 

Yep, you can try that as well....hopefully that will work fine for you.

[jbrit2020]

Ok, so here's what I've tried:

1 - Remove connector and reconnect and create search formula with a single Text column.

2 - Create new canvas app from scratch create connection to excel file and create search formula with a single Text column.

3 - Create a new table with just two columns with dummy data in a new excel file, create new canvas app, connect to data and create search formula with a single Text column.

4 - Try on a different laptop connected to a different business enterprise DCN space altogether (different companies) , and repeated all of the previous tests. The result is always the same, "The request operation is invalid. Server Response: Table1 failed: ')' or ',' expected at position 16 in 'contains....."

I'm going to guess this really is a limitation of a Excel Online connection? or perhaps a problem that has gone unnoticed? 

[RandyHayes]

@jbrit2020 

So it works in your own tenant then.  Your problem is going across tenant.  I am sure then it is completely based on the security and permissions across the two.