Answered

Filtering a gallery based on input

(0) Share

Report

Posted on by Joan_Zulo

Hi, I'm trying to filter a gallery based on a text entered by a user, the gallery has a connexion to excel online for business conector and the table contains more than 3000 unique values.

I've created a text input named "FilterTextBox" to filter the gallery based on the field "cedula" that has text format in the Tabla1 (the source of the gallery)

This is the formula I'm trying in the Item property of the gallery:

Filter(Tabla1; StartsWith(cedula;FilterTextBox.Text))

It is retrieving "Runtime errors" indicating "the operation required is not valid" as the photo below:

Is there a problem with the conector, the format of the field...? How could I change in my formula to filter by that field?

Thanks in advance,

Categories:

Building Power Apps

I have the same question (0)

All responses (10)

Answers (1)

StalinPonnusamy Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @Joan_Zulo

Can you try this

Search(Tabla1; TextSearchBox1.Text; "cedula")

Was this reply helpful? Yes No
StalinPonnusamy Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!
Hi @Joan_Zulo

I created excel online and tested and the below command works.

Set the items property of Gallery to
SortByColumns( Search( [@Tabla], TextSearchBox1.Text, "cedula" ), "cedula", Descending )

This search and sort by desending

Was this reply helpful? Yes No
Joan_Zulo 23 on at

Like (0)

Report
Copy link

Link copied!

Hi @StalinPonnusamy I tried your way but still retrieve the same "RunTime Error"

Was this reply helpful? Yes No
Joan_Zulo 23 on at

Like (0)

Report
Copy link

Link copied!

Still the same error, I don't know if there something related to my connection vs my functions?

Was this reply helpful? Yes No
StalinPonnusamy Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @Joan_Zulo

I suggest reconnecting and try it. Here is the test I made

Was this reply helpful? Yes No
StalinPonnusamy Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

Hi @Joan_Zulo

You should be able to see a new column (__PowerAppsId__) once connected to power apps

Was this reply helpful? Yes No
Verified answer

StalinPonnusamy Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!

Hi @Joan_Zulo

I just looked at the connector and using OneDrive. Can you verify your connector?

MS Article for reference

Was this reply helpful? Yes No
Joan_Zulo 23 on at

Like (0)

Report
Copy link

Link copied!

Hi @StalinPonnusamy thanks for your previous answers, I tried them and finally I changed the connector for onedrive for business and now is working with the Filter function, but now It is retrieving only 500 rows and the excel table contains 3000+ how could I bring all of the rows?

Was this reply helpful? Yes No
StalinPonnusamy Super User 2024 Season 1 on at

Like (1)

Report
Copy link

Link copied!
Hi @Joan_Zulo

Powerapps Data row limit is 500 by default. But we can change a maximum of 2000. You should be fine as long as your search query brings any result less than the limit of the setting.

To change Data row limit from 200 to 2000.
Click the File menu of your app
Click "Settings"
Change "Data Row Limit" under general settings to your desired number (max is 2000)

Note: Need to add proper filter/more filter to bring the data which less than the max limit

Was this reply helpful? Yes No
AmolPomaji05 6 on at

Like (0)

Report
Copy link

Link copied!

Hello @Joan_Zulo,
Here, you use the Search Function instead of Filter Function.
On the text input box, set the default property and in the formula bar set to empty. i.e Default ----> ""
Now, select the gallery
Set the Items Property and in the formula bar type the below Search Function :-
Search(DataSourceName, inputboxname.Text,"Title")
then you will get the, Filtered data on based on the user search.

Now, you will search the data based on the textinput box. it will shows the record in the gallery.

Was this reply helpful? Yes No