Filter by number

[biancadevera]

How can I filter by number using the textbox? I have Ticket # field on my SharePoint list and want to use my search box for this. Right now, I can only filter using the dropdown.

The Format of my textbox is TextFormat.Number.

My current formula is this:

Filter (Tracker,(Status.Value = drpStatusMyTickets.Selected.Value || drpStatusMyTickets.Selected.Value = "All") && 'Created By'.Email=User().Email)

I tried this formula but didn’t work:

Filter (Tracker,(Status.Value = drpStatusMyTickets.Selected.Value || drpStatusMyTickets.Selected.Value = "All") || (txtSearchFilterMyTickets.Text in 'Ticket #') && 'Created By'.Email=User().Email)

Been searching for the answers for days now but can't find one. Some mentioned about converting the number to text. Please help.

[Anonymous_Hippo]

Hello @biancadevera ,

This is the formula that you need

Filter (Tracker,(len(txtSearchFilterMyTickets.Text)=0 ||(txtSearchFilterMyTickets.Text = 'Ticket #')))

Explanation

We are using the Len() function to not filter anything if the textbox is blank and filter the "tracker" list based on the information inside the text box

Can you try this formula - this might not work as you expected because i'm not sure what exactly your logic is

Filter (Tracker,(Status.Value = drpStatusMyTickets.Selected.Value || drpStatusMyTickets.Selected.Value = "All") && (len(txtSearchFilterMyTickets.Text)=0 ||(txtSearchFilterMyTickets.Text = 'Ticket #')) && 'Created By'.Email=User().Email)

I made a test case and this is working

Hope this helps ! 🙂

[biancadevera]

Hi,

I tried to use the formula you provided but didn't work. Now, I have an error:

Filter (Tracker,(len(txtSearchFilterMyTickets.Text)=0 ||(txtSearchFilterMyTickets.Text = 'Ticket #')))

It says I might need to convert the value to be the same type.

I'm trying to look for the record not identifying the length of the number. Basically, I want to use my textbox to search for a record in my SharePoint list.

[Anonymous_Hippo]

@biancadevera 

Can you tell me what is the value type of Ticket # ? is it a single line field ? we are using the length (LEN) function to not filter anything if the text box is blank

[biancadevera]

@Anonymous_Hippo 

The value type of Ticket # is Number in my SharePoint list. I'm filtering it using gallery. I don't know if that makes sense.

[Anonymous_Hippo]

@biancadevera 

Can you remove all your formulas and try this and see if it is working

Filter(Tracker,len(txtSearchFilterMyTickets.Text)=0 || txtSearchFilterMyTickets.Text in 'Ticket #')

this should work , if not please share your error message

[biancadevera]

@Anonymous_Hippo 

Please check this out

[StalinPonnusamy]

Hi @biancadevera 

1. This filter Status either all or selected status
2. Filter based on ticket number (starts with)
3. and Current user

Filter(Tracker,
(
(drpStatusMyTickets.Selected.Value = "All" || Status.Value = drpStatusMyTickets.Selected.Value ) || (IsBlank(txtSearchFilterMyTickets.Text) || StartsWith('Ticket #',txtSearchFilterMyTickets.Text))
)
 && 'Created By'.Email=User().Email
)

[StalinPonnusamy]

Hi @biancadevera 

Looks like you may have a delegation issue. Use the below code, if you get the warning

With({_Item: Tracker},
Filter(_Item,
(
(drpStatusMyTickets.Selected.Value = "All" || Status.Value = drpStatusMyTickets.Selected.Value ) && (IsBlank(txtSearchFilterMyTickets.Text) || StartsWith('Ticket #',txtSearchFilterMyTickets.Text))
)
 && 'Created By'.Email=User().Email
)
)

[StalinPonnusamy]

Hi @biancadevera 

I just noticed, the ticket number is the number, so you may need to convert it to a number like Value(txtcontrol)

StartsWith('Ticket #',Value(txtSearchFilterMyTickets.Text))