cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jbrit2020
Helper V
Helper V

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

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

RandyHayes
Super User III
Super User III

@jbrit2020 

Use the OneDrive for Business connector instead.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

View solution in original post

18 REPLIES 18
RandyHayes
Super User III
Super User III

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

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

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

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

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

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

@jbrit2020 

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

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

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

@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. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too!

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

Power Apps Community Call

Monthly Power Apps Community Call

Did you miss the call?? Check out the Power Apps Community Call here!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV STudio

Power Platform ISV Studio

ISV Studio is the go-to Power Platform destination for ISV’s to monitor & manage applications post-AppSource publish.

Top Solution Authors
Top Kudoed Authors
Users online (65,012)