Search Gallery using text and number fields

Like (1) Share

Report

Posted on 26 Aug 2024 16:16:06 by CU24081716-3

I am trying to set up a gallery search using text fields (Name & Age) and number field (ID which is a system column from a Sharepoint List). Age is a calculated field and has text content like this: "11 yrs 8 mos"

I have 2 search boxes, one for text (PTNameSearch) and one for number (PTIDSearch).

Here's my search logic: if PTNameSearch box is not blank, then search by Name & Age value else if PTIDSearch box is not blank then search by ID, else return all records.

If(!IsBlank(PTNameSearch.Value),
    Search(SortByColumns('EMR PT Demographics',"ID"), PTNameSearch.Value,'Patient Name', Age),
    If(!IsBlank(PTIDSearch.Value),
       Filter(SortByColumns('EMR PT Demographics',"ID"),Value(PTIDSearch.Value),ID),
    SortByColumns('EMR PT Demographics',"ID")))

The text search works fine, but the number search does not return a result.

What is wrong with my number search?

Thank you.

Categories:

Using Apps Using Formulas

All responses (5)

Answers (0)

CU24081716-3 on 27 Aug 2024 at 02:15:38

Like (1)

Report

Search Gallery using text and number fields

Brilliant to use AddColumns, Warren! Your solution works! The only change I made is to replace the operator "in" in the Filter with "=". With the "in" operator, if I search for ID of 8, I would get records for ID = 8, 18, 28, etc. But with the "=" operator, the search is exact.

Thank you so much!
WarrenBelz 146,792 Most Valuable Professional on 27 Aug 2024 at 00:30:15

Like (2)

Report
Search Gallery using text and number fields

Hi CU24081716-3

I thought I would throw in this idea
Search( SortByColumns( Filter( AddColumns( 'EMR PT Demographics', IDTxt, Text(ID) ), Len(PTIDSearch.Value) = 0 || PTIDSearch.Value in IDTxt ), "ID" ), PTNameSearch.Value, 'Patient Name', Age )
CU24081716-3 on 26 Aug 2024 at 20:38:54

Like (1)

Report

Search Gallery using text and number fields

I also removed the name search and use just the filter:

If(!IsBlank(PTIDSearch.Value),

Filter(SortByColumns('EMR PT Demographics',"ID"),Value(PTIDSearch.Value),ID),

SortByColumns('EMR PT Demographics',"ID"))

Still does not work! :-(
CU24081716-3 on 26 Aug 2024 at 20:14:37

Like (1)

Report

Search Gallery using text and number fields

Thank you, Michael, for jumping in. The Age field here is Text. It is a calculated field from DOB field. Its content has this format "11 yrs 8 mos".

As shown below, searching by Age works just fine:

The problem I'm experiencing is the filter by ID: Filter(SortByColumns('EMR PT Demographics',"ID"),Value(PTIDSearch.Value),ID)

This Filter() statement isn't returning anything.
Suggested answer

MichaelFP 1,831 Super User 2025 Season 1 on 26 Aug 2024 at 19:40:43

Like (1)

Report

Search Gallery using text and number fields

The Search() is to find a string, that could be in any place of columns of type text. For numbers will not work. you need for that case to use Filter() to apply by Age.

Filter, Search, and LookUp functions - Power Platform | Microsoft Learn