Answered

Need help filtering or searching on a number column (ID) in my gallery

(0) Share

Report

Posted on by kennedyorr19

Hello,

I am having some trouble filtering my gallery on the ID column. I already have filters set up for Location, Title, Status and Requestor. This is what the item property looks like for my gallery currently;

Filter(
'Production System Change Request',
StartsWith(
Title,
txtTitle.Text
) && (Status.Value = Radio1.Selected.Value || Radio1.Selected.Value = Blank()) && ((chkMe.Value && Requestor.Email = User().Email) || (chkMe.Value = false && chkOther.Value = false) || (chkOther.Value && Requestor.Email = cbUser.Selected.Email)) && (chkLocation.Value = false) || (chkLocation.Value && Location.Value = LocationDRP.Selected.Value))

Would love any help with adding ID to this. I keep running into a lot of issues since it is a number column. So far vie added a txt input box labeled txtSearch. This is where I would like the user to type in the ID number to begin the filter. Any help is greatly appreciated 🙂

Categories:

Building Power Apps

I have the same question (0)

All responses (19)

Answers (1)

Sort by

Ethan_009 4,838 Moderator on at

Like (0)

Report
Copy link

Link copied!
Hi @kennedyorr19 ,

Kindly try the following code:
Filter( 'Production System Change Request', StartsWith( Title, txtTitle.Text ) && (Status.Value = Radio1.Selected.Value || Radio1.Selected.Value = Blank()) && ((chkMe.Value && Requestor.Email = User().Email) || (chkMe.Value = false && chkOther.Value = false) || (chkOther.Value && Requestor.Email = cbUser.Selected.Email)) && (chkLocation.Value = false) || (chkLocation.Value && Location.Value = LocationDRP.Selected.Value) && (IsBlank(txtSearch.Text) || Value(txtSearch.Text) in IDNumber) )

Hope this helps

Was this reply helpful? Yes No
kennedyorr19 36 on at

Like (0)

Report
Copy link

Link copied!

Hi @Ethan_R
Thank you for the reply! I am receiving the following error;

on IDNumber it says "Name isnt recognized.

Was this reply helpful? Yes No
Ethan_009 4,838 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @kennedyorr19 ,

Just replace IDNumber with the Field that represents ID in your Table.
Since I didn't know the exact name, I wrote IDNumber.

Was this reply helpful? Yes No
kennedyorr19 36 on at

Like (0)

Report
Copy link

Link copied!

Hi @Ethan_R
Ya I tried that as well and received the error message:
"We expected text at this point in the formula"

I then tried to type in ID as "ID" instead. Which left me with no errors in the formula, but when I tested it out typing in a number into the search bar did not filter the data

Was this reply helpful? Yes No
Ethan_009 4,838 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi @kennedyorr19 ,

Can you tell me what is your column name that you store ID in?
Also what is the datatype of that column.

Please make sure, you enter proper column name for it to work.

Was this reply helpful? Yes No
JohnM86 590 on at

Like (0)

Report
Copy link

Link copied!

To add the ID column to your filter, you can use the same logic as for the Title column by using the StartsWith function. Here's an updated formula that includes the ID column:

Filter( 'Production System Change Request', (StartsWith(Title, txtTitle.Text) || StartsWith(ID, txtSearch.Text)) && (Status.Value = Radio1.Selected.Value || Radio1.Selected.Value = Blank()) && ((chkMe.Value && Requestor.Email = User().Email) || (chkMe.Value = false && chkOther.Value = false) || (chkOther.Value && Requestor.Email = cbUser.Selected.Email)) && (chkLocation.Value = false || Location.Value = LocationDRP.Selected.Value) )
Notice that I added StartsWith(ID, txtSearch.Text) to the first condition to filter by the ID column. Also, I wrapped the chkLocation.Value and Location.Value conditions in parentheses to make sure they are evaluated together before the OR operator.
Make sure to update the names of the columns and controls to match your app. I hope this helps!

Was this reply helpful? Yes No
kennedyorr19 36 on at

Like (0)

Report
Copy link

Link copied!

Hi @Ethan_R
The ID column is named ID. This is the ID column that comes built into Sharepoint Lists.

Was this reply helpful? Yes No
kennedyorr19 36 on at

Like (0)

Report
Copy link

Link copied!

Hi @JohnM86
I tried that and I am not receiving errors. But when I go to test it, the gallery does not filter when I type in numbers to that box. And now my title search box is not working either.

Was this reply helpful? Yes No
JohnM86 590 on at

Like (0)

Report
Copy link

Link copied!

Does this work ?

Filter(
'Production System Change Request',
(StartsWith(Title, txtTitle.Text) || ID = Value(txtSearch.Text)) &&
(Status.Value = Radio1.Selected.Value || Radio1.Selected.Value = Blank()) &&
((chkMe.Value && Requestor.Email = User().Email) ||
(chkMe.Value = false && chkOther.Value = false) ||
(chkOther.Value && Requestor.Email = cbUser.Selected.Email)) &&
(chkLocation.Value = false || Location.Value = LocationDRP.Selected.Value)
)

Was this reply helpful? Yes No
JohnM86 590 on at

Like (0)

Report
Copy link

Link copied!

This formula uses the Value function to convert the text entered in txtSearch into a number, which can then be compared to the ID column. Note that the ID column is not a text column, so you cannot use the StartsWith function on it. Instead, you can compare the exact value of the ID to the value entered in txtSearch.
Make sure to check that the names of your controls and columns match the names used in the formula. Also, ensure that the ID column is a number column in your data source.
I hope this helps you filter your gallery based on the ID column!

Was this reply helpful? Yes No