How to find integer and text values (Filter & Search)

Question

I have my data source connected to database in SQL Server through an application in Power Apps, I am making use of Gallery to display the data and I have a text entry to search for specific data in that gallery.

I would like to perform the search for text type data and numeric type data, I leave below the data type and the columns that I use in my table:

NoEmployee int,
NameEmployee varchar,
Job varchar,
Department varchar,

I am making use of the Items property in the Gallery to perform the search for both numeric and text type data where I use the following syntax.

Search(Filter(DataSourceEmployee;NoEmployee = Value(txtSearchText.Text));;DataSourceEmployee;txtSearchText.Text;"NameEmployee";"Department";"Job")

The above syntax gives me the following error:

'Search' function has invalid arguments

Among the documentation that I have been reading the Search function does not allow me to search for integer values.

Then the idea of combining the two functions occurs to me, I don't know if it's the right thing to do.

As I mentioned, I need to search for the 4 columns that I mentioned, if I use the following Search function, it searches me without problem for the text type columns.

Search(DataSourceEmployee;txtSearchText.Text;"NameEmployee";"Department";"Job")

If I use the Filter function, it searches me without any problem for the integer type column.

Filter(DataSourceEmployee;NoEmployee = Value(txtSearchText.Text))

I would like to know if there is a way to combine these two functions in order to perform the search through the four columns or what other function I can use to search for number values without losing the search for text values.

Update 1:

Based on the last possible answer provided, I add the syntax that I have used, but without obtaining a satisfactory result, it only performs a search for the numeric data type column which corresponds to NoEmployee and not for the text type column.

IfError( Filter(DataSourceEmployee,NoEmployee=Value(txtSearchText.Text)), Filter(DataSourceEmployee,StartsWith(NameEmployee,txtSearchText.Text)))

Update 2:

Based on the last answer they give me I have performed the following function in the Items property of Gallery Control, I try to perform the search for the column of type INT as well as those of VARCHAR and in none I get results.

The function I have used is as follows:

SortByColumns(
    Filter(
        colEmployees,
        If(
            !IsBlank(txtSearchText.Text),
            Or(
                txtSearchText.Text in NoEmployee,
                txtSearchText.Text in NameEmpleado,
                txtSearchText.Text in Job,
                txtSearchText.Text in Department
            ),
            1 = 1
        )
    ),
    "NoEmployee",
    Ascending
)

Additional as the answer indicates I have added in the OnStart property of the application control the following:

ClearCollect(colEmployees,DataSourceEmployee)

user_5976033 · Answer

Use a Filter within a SortByColumn function rather than Search. You can easily search by all columns you want regardless of type. Here I show how to search by all 4 columns where EmpNumber is an INT-type column and the rest are NVARCHAR.

You can sort by any column, Ascending or Descending.

Illustration:

Code:

Place this on OnStart of the App control:
- ClearCollect(colEmployees, EMPLOYEE_DEV)
- Where EMPLOYEE_DEV is the SQL table shown below
Place this on the Items property of the Gallery control:

SortByColumns(
    Filter(colEmployees, 
        If(
            !IsBlank(txtSearch.Text),
            Or(
                txtSearch.Text in EmpNumber,
                txtSearch.Text in EmpName,
                txtSearch.Text in Job,
                txtSearch.Text in Deparment
            ),
            1=1
        )
    ),
    "EmpNumber",
    Ascending
)

Place 4 Label controls to display the data
- Set their Text properties to ThisItem.

Data: SQL Database connected to PowerApp Canvas app

-- Create a new table called 'EMPLOYEE_DEV' in schema 'dbo'
-- Drop the table if it already exists
IF OBJECT_ID('dbo.EMPLOYEE_DEV', 'U') IS NOT NULL
DROP TABLE dbo.EMPLOYEE_DEV
GO
-- Create the table in the specified schema
CREATE TABLE dbo.EMPLOYEE_DEV
(
    EmpTblID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    EmpNumber INT,
    EmpName VARCHAR(255),
    Job VARCHAR(255),
    Deparment VARCHAR(255)
);

INSERT INTO dbo.EMPLOYEE_DEV (EmpNumber, EmpName, Job, Deparment)

VALUES 
(123, 'John', 'Developer', 'IT'),
(234, 'Jane', 'Developer', 'IT'),
(345, 'Jim', 'Project Manager', 'ENG'),
(456, 'Joey', 'Manager', 'ENG')

user_8825081 · Answer

You can utilize Iferror function in such a way that if the searchbox has an alphabetical value then the gallery would be searched for text type columns but if the searchbox has a numerical value then the gallery would be searched for the numeric type column.

IfError( Filter(DataSource,NumericTypeColumn=Value(SearchBox.Text)), Filter(DataSource,StartsWith(TextTypeColumn,SearchBox.Text)) )

I prefer to use Startswith function instead of Search function.

How to find integer and text values (Filter & Search)

2 Answers