cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
k_cain
Helper II
Helper II

Filter Gallery by Lookup, Number, and Text Columns

I have a gallery that I want to filter using a text box that searches multiple columns in a SharePoint list. The problem is that one column has numbers (ID), two columns have a lookup (FHANumber_x003a_FHA_x0020_Number and FHANumber_x003a_Property_x0020_N), and the rest of the columns are text (RequestType and RequestNumber). How can I do this? Below is a picture of what I have so far.  

k_cain_0-1611960323540.png

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @k_cain ,

The below is free-typed, so watch brackets and commas etc, but you should get an idea of the syntax if there are any errors.

With(
   {
      wSearch:'FHA Number or Property Name Search_Input',
      wList:
      AddColumns(
         'Construction Analyst Workload Management',
         "FHANo",
         FHANumber_x003a_FHA_x0020_Number.Value,
         "FHAProp",
         FHANumber_x003a_Property_x0020_N.Value,
         wID,
         Text(ID)
      )
   },	  
   SortByColumns(
      Filter(
         wList,	  
         IsBlank(wSearch) || 
         (
            wSearch =wID ||
            wSearch=FHANo ||
            wSearch=FHAProp ||
            StartsWith(RequestType,wSearch ||
            StartsWith(RequestNumber,wSearch)
         )
      ),
      "ID",
      If(
         SortDescending1,
         Ascending,
         Descending
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

5 REPLIES 5
WarrenBelz
Super User
Super User

Hi @k_cain ,

Can you please post that code in Text.

Warren, here's the code I've been using:

 

SortByColumns(If(IsBlank('FHA Number or Property Name Search_Input'.Text),'Construction Analyst Workload Management',Filter('Construction Analyst Workload Management',Value('FHA Number or Property Name   Search_Input'.Text)=ID||'FHA Number or Property Name Search_Input'.Text=FHANumber_x003a_FHA_x0020_Number.Value||'FHA Number or Property Name Search_Input'.Text=FHANumber_x003a_Property_x0020_N.Value||StartsWith('FHA Number or Property Name Search_Input'.Text,RequestType||StartsWith('FHA Number or Property Name Search_Input'.Text,RequestNumber)),"ID",If(SortDescending1,Ascending,Descending))

Hi @k_cain ,

The below is free-typed, so watch brackets and commas etc, but you should get an idea of the syntax if there are any errors.

With(
   {
      wSearch:'FHA Number or Property Name Search_Input',
      wList:
      AddColumns(
         'Construction Analyst Workload Management',
         "FHANo",
         FHANumber_x003a_FHA_x0020_Number.Value,
         "FHAProp",
         FHANumber_x003a_Property_x0020_N.Value,
         wID,
         Text(ID)
      )
   },	  
   SortByColumns(
      Filter(
         wList,	  
         IsBlank(wSearch) || 
         (
            wSearch =wID ||
            wSearch=FHANo ||
            wSearch=FHAProp ||
            StartsWith(RequestType,wSearch ||
            StartsWith(RequestNumber,wSearch)
         )
      ),
      "ID",
      If(
         SortDescending1,
         Ascending,
         Descending
      )
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

Warren, I keep getting errors. Any ideas?

k_cain_0-1612194222808.png

 

@k_cain ,

That does not really help - what is the error? I was also assuming the values you posted were valid - have you checked them?

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Helpful resources

Announcements
User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

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