cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbrit2020
Continued Contributor
Continued Contributor

PowerApps search function on Excel Online

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:

jbrit2020_0-1610648806844.png

 

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

 

Regards

2 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@jbrit2020 

Okay then, what you are doing works perfectly, so there must be something initially going on.

You mention Table2 in your original formula - is Table2 from your Excel file?  How are you getting that connected to your data?

 

Normally, you would be using the OneDrive connector to connect to your Excel file.  At that point, when you add the connector, it will ask for the Excel file and the table.  Once you select, those tables are available in your app.  

My assumption so far has been that this was the process you used.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

RandyHayes
Super User
Super User

@jbrit2020 

Use the OneDrive for Business connector instead.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

19 REPLIES 19
RandyHayes
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
jbrit2020
Continued Contributor
Continued Contributor

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
Super User
Super User

@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.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
jbrit2020
Continued Contributor
Continued Contributor

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
Super User
Super User

@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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
jbrit2020
Continued Contributor
Continued Contributor

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
Super User
Super User

@jbrit2020 

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
jbrit2020
Continued Contributor
Continued Contributor

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
Super User
Super User

@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.  

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (3,583)