Search multiple columns which are text and number

[iwonder]

Thank you for taking the time to read my question.

I want to use the Search() function to limit values in my Gallery. The Gallery Items is set to a Filter. I want the user to be able to search on 3 different fields CustomerName, ID, AlphaID. Name and AlphaID are alphanumeric whereas ID is numeric.

Search(Filter(mytable,email = User().email),TextInput.Text,"CustomerName","ID","AlphaID")

If I do the above, the "ID" is red underlined because it's a number field and I'm comparing text to it. Is there an easy solution to this, or do I have to use If or Switch statements to check what is entered and then compare to the field?

Thanks

[RandyHayes · Accepted solution]

@iwonder 

You'll need to convert your numeric to text.

Please consider changing your Formula to the following:

Search(
    AddColumns(
        Filter(mytable, email = User().email),
        "txtID", Text(ID)
    ),
    TextInput.Text,
    "CustomerName", "txtID", "AlphaID"
)

I hope this is helpful for you.

[iwonder]

Thank you @RandyHayes ... I was so stuck on how to convert the Text Input.... I didn't see that I just needed to convert the numeric field.... just one of those days... 🙂

[RandyHayes]

@iwonder 

Those things happen!  Tomorrow is another day!

[iwonder]

Hi @RandyHayes 

I'm trying to use this again but a bit differently and am stuck.

I have a SharePoint list with over 2000 rows. Search is not delegable so I can't use that to limit my Gallery. The only criteria I have to filter the gallery is what the user types into the Text Input.

This is not returning any records and I'm not sure what I'm doing wrong.

Filter(AddColumns(Customers,"txtM3Num",Text(Title)),Or(txtCustomerFilter_1.Text = txtM3Num,txtCustomerFilter_1.Text = AddressName))

I'm trying to replace this old code with the above:

//If(
//    IsBlank(txtCustomerFilter_1),
//    Sort(
//        Customers,
//        AddressName
//    ),
//    If(
//        IsNumeric(txtCustomerFilter_1.Text),
//        Filter(
//            Customers,
//            StartsWith(
//                Title,
//                txtCustomerFilter_1.Text
//            )
//        ),
//        Filter(
//            Customers,
//            StartsWith(
//                AddressName,
//                txtCustomerFilter_1.Text
//            )
//        )
//    )
//)

[RandyHayes]

@iwonder 

Actually, as I read your formula, it is delegable but you are trying to convert a Text column (Title) to Text - which is not delegable, BUT not needed...your Title column is already text.

So your formula should be:

Filter(Customers,
    Title = txtCustomerFilter_1.Text ||
    AddressName = txtCustomerFilter_1.Text
)

[iwonder]

Thanks Randy.

When I paste in that formula I get no data returned... at all, even before I type something in the Text Input

When I revert back to my old code I get data.

In my Title column I have numbers, you're saying they're stored as Text?

Thanks!

[RandyHayes]

@iwonder 

Well, your formula as actually very specific.  It says that you will get no results unless the text entered in the text input matches the Title value or the Address value.

As for the column type - yes, if it is the Title column, then it is always text.  It doesn't make a difference that they are numbers that you put in...they are still text because of the column type.

So, let's take a look at your old Formula.

Change it to the following:

With({_numVal: Text(Value(txtCustomerFilter_1.Text))}, 
    Filter(Customers,
        (IsBlank(txtCustomerFilter_1.Text) || AddressName = txtCustomerFilter_1.Text) &&
        (_numVal = "0" || Title=_numVal)
    )
)    

This is a delegable filter.  It is equivalent to your prior formula.

[iwonder]

Thanks @RandyHayes 

No errors in the code, but also still no data...

If I understand it: With turns the input text to TEXT(), then runs the FILTER which uses my Customers SharePoint list with criteria, text input is blank, OR text input = AddressName AND _numVal = "0" OR text input = _numVal

I don't understand the part AND _numVal = "0"...

Thanks again for your help

[RandyHayes]

@iwonder 

Change to:

With({_numVal: Text(If(IsNumeric(txtCustomerFilter_1.Text), Value(txtCustomerFilter_1.Text), 0))}, 
    Filter(Customers,
        (IsBlank(txtCustomerFilter_1.Text) || AddressName = txtCustomerFilter_1.Text) &&
        (_numVal = "0" || Title=_numVal)
    )
)    

This is all a Boolean statement (not good to use If's in your criteria if you can avoid them).

There is one scoped with variable called _numVal.  It is based on the Value (conversion from text to number) of the text in the input control.  I have added a statement to account for numeric or not.  Value *used* to return 0 if the text was blank or non-numeric, but, seems they've moved my cheese again.

So, _numVal will be 0 if nothing is entered or it is not numeric.

Into the criteria,  The criteria says that two things must be true in order to get results.

1) the TextInput must be blank or the AddressName field must match the TextInput

AND

2) the _numVal must be "0" or the Title must equal _numVal