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

[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 🙂 

[JohnM86]

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!

[kennedyorr19]

@JohnM86 

Ugh so close, the title is now working, but it is still not filtering on the ID!

[Ethan_R]

Ohh @kennedyorr19 ,

Can you try,

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 <SharePoint List>[@ID])
)

Please replace <SharePoint List> with your List name and just after that type '[' and see list of fields you see from PowerApps.
Find ID or search ID and then close the bracket. 

Its should look like 'Production System Change Request'[@ID] or 'Production System Change Request'[@'ID']

Hope this helps

[kennedyorr19]

@Ethan 

It feels like this is so close to working, I tried ID typed both ways and still error. This time it just underlines ID.

[Ethan_R]

Hi @kennedyorr19 ,

It certainly looks like column name is not correct. 

Can you please check (not in SP but within App) the name of column? 

Can you see list of columns that App suggests when you type 'Production System Change Request'[@ ?
App will show you list of columns after @ symbol. 

Once you get the actual name, do replace in the main code.

Hope this works!

[kennedyorr19]

Hi @Ethan_R 

Here you go

[JohnM86 · Accepted solution]

'm sorry to hear that the previous expression didn't work for you. Let's try another approach. Instead of using the StartsWith function, we can use the Contains function to check if the ID column in the SharePoint list contains the text entered in the txtSearch box. Here's the updated formula:

Filter('Production System Change Request', (StartsWith(Title, txtTitle.Text) || StartsWith(Requestor.DisplayName, 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) && (txtSearch.Text in Text(ID)) )

This should filter the gallery based on the text entered in the txtSearch box and display only the items whose ID column contains that text. Please give this a try and let me know if it works for you.

[Ethan_R]

@kennedyorr19 ,
It does seem like 'Production System Change Request'[@ID] is the column you need. 

Now if you still see error, then when you hover on it. What's the error?

I feel there might me syntax error or something. But do share the error when you hover on red wavey lines.

[kennedyorr19]

This is what it shows! @Ethan_R 

[kennedyorr19]

Yay this worked thank you so much!!