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)

SeaDude · Answer 1 · 2022-05-30T14:06:26.513

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

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

Try to do what you indicated in your question but it does not show me results at the time of searching, add an update based on your answer applied to my code, if I have an additional error please indicate me to correct it. — , Jun 01 '22 at 13:30
Is there an error message? Any red lines under the code? Does `colEmployees` have data (to check, add a Label control with the `Text` property set to `CountRows(colEmployees`)? Did you name the search box `txtSearchText`? — SeaDude, Jun 01 '22 at 19:28
No error is generated when completing the function, the search box which corresponds to a text entry is called `txtSearchText`, adding a text tag as it indicates, and assigning it to the Text `CountRows(colEmployees)` property returns me the total of records, some other modification that I must make ? — , Jun 01 '22 at 19:48
There must be some other error. I created a SQL table as shown, connected it to the PowerApp as shown with no issues. You should click the "App Checker" icon in the top right. Investigate any errors shown. — SeaDude, Jun 01 '22 at 20:01

score 0 · Answer 2 · answered May 25 '22 at 16:02

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 Answers2