cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
biancadevera
Helper III
Helper III

Filter by number

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.

1 ACCEPTED SOLUTION

Accepted Solutions

I found a workaround. I just added another column on my SharePoint list then set the format type to Text. I used this:

 

Filter(Tracker, StartsWith('Ticket #',(txtSearchFilterMyTickets.Text)))

View solution in original post

13 REPLIES 13
Anonymous_Hippo
Super User
Super User

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

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

@biancadevera 2021-10-06_13-41-09.gif

 

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
Helper III
Helper III

@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.

 

 

@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

@Anonymous_Hippo 

 

Please check this out

 

powerapps.png

 

StalinPonnusamy
Super User
Super User

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

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

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))

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Top Kudoed Authors
Users online (2,260)