cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emfuentes27
Advocate III
Advocate III

Apply multiple filters including to gallery

I created a gallery using SharePoint data. I filter this gallery based on several conditions. One of the columns I need to filter has the State plus it State Code (e.g. 08 - Colorado). This part of the filter/search works just fine:

 

Sort(
    Search(
        AddColumns(
            Filter(
                'Consultations',
  Or(  IsBlank(cy_filter_1.Selected.Value),
                    IsEmpty(cy_filter_1.Selected.Value),
                    'Office'.Value = RO 
                    && Crop_Year = Text(cy_filter_1.Selected.Value) 
                    ) 
 ), 
              
            "ChoiceValue",
            State.Value
        ),
        TextSearchBox1_1.Text,
        "Tracking_Number",
        "ChoiceValue"
    ),
    ID,
    If(
        ascendingOrder = true,
        Ascending,
        Descending
    )
)

 Because the State column has the info in the Code - State format (e.g. 08 - Colorado), I try to add one more step using a Last/Split function, but it doesn't return anything, and the gallery is empty:

 

Sort(
    Search(
        AddColumns(
            Filter(
                'Consultations',
               
                  Or(  IsBlank(cy_filter_1.Selected.Value),
                    IsEmpty(cy_filter_1.Selected.Value),
                    'Office'.Value = RO 
                    && Crop_Year = Text(cy_filter_1.Selected.Value) 
                 && Last(Split('State'.Value, " - " )).Result = myStateMatch
                   ) 
          
                ), 
              
            "ChoiceValue",
            State.Value
        ),
        TextSearchBox1_1.Text,
        "Tracking_Number",
        "ChoiceValue"
    ),
    ID,
    If(
        ascendingOrder = true,
        Ascending,
        Descending
    )
)

For the sake of testing, I used another filter, using just the State column, and it works just fine:

 

Sort(
    Search(
        AddColumns(
            Filter(
                'Consultations',
               
               Last(Split('State'.Value, " - " )).Result = myStateMatch
          
                ), 
              
            "ChoiceValue",
            State.Value
        ),
        TextSearchBox1_1.Text,
        "Tracking_Number",
        "ChoiceValue"
    ),
    ID,
    If(
        ascendingOrder = true,
        Ascending,
        Descending
    )
)

 What am I missing here?

Thanks in advance.

2 ACCEPTED SOLUTIONS

Accepted Solutions
WarrenBelz
Super User
Super User

Hi @emfuentes27 ,

Your code is valid as far as the syntax should return the result you want. It is however not Delegable (so how big is your list?) Your other syntax however is a bit confusing in the and/or bracketing. For the sake of testing another way of dong it, try this

Sort(
   Search(
      AddColumns(
         Filter(
            'Consultations',
            Len(cy_filter_1.Selected.Value) = 0 &&
            'Office'.Value = RO && 
            Crop_Year = cy_filter_1.Selected.Value && 
            Right(
                State.Value,
                Len(State.Value) - 
                Find(
                   "-",
                   State.Value
                ) - 1
            ) = myStateMatch
         ), 
         "ChoiceValue",
         State.Value
      ),
      TextSearchBox1_1.Text,
      "Tracking_Number",
      "ChoiceValue"
   ),
   ID,
   If(
      ascendingOrder = true,
      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

@emfuentes27 ,

If it is a number, then cy_filter_1Selected.Value = 0 

 

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

3 REPLIES 3
WarrenBelz
Super User
Super User

Hi @emfuentes27 ,

Your code is valid as far as the syntax should return the result you want. It is however not Delegable (so how big is your list?) Your other syntax however is a bit confusing in the and/or bracketing. For the sake of testing another way of dong it, try this

Sort(
   Search(
      AddColumns(
         Filter(
            'Consultations',
            Len(cy_filter_1.Selected.Value) = 0 &&
            'Office'.Value = RO && 
            Crop_Year = cy_filter_1.Selected.Value && 
            Right(
                State.Value,
                Len(State.Value) - 
                Find(
                   "-",
                   State.Value
                ) - 1
            ) = myStateMatch
         ), 
         "ChoiceValue",
         State.Value
      ),
      TextSearchBox1_1.Text,
      "Tracking_Number",
      "ChoiceValue"
   ),
   ID,
   If(
      ascendingOrder = true,
      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

emfuentes27
Advocate III
Advocate III

@WarrenBelz 

Thanks for the help. I'm getting an error in your syntax. The portion shown below says: "Invalid argument type (Number). Expecting a Text value instead". 

 

 

...
Len(cy_filter_1.Selected.Value) = 0
...

 

 

The cy_filter_1 dropdown items look like this, if this helps:

 

 

[Blank(), Year(Now())-3,Year(Now())-2,Year(Now())-1,Year(Now()),Year(Now())+1,Year(Now())+2]

 

I tried to save the cy_filter_1 selected value into a variable (cyValue), and then wrap it with a Text() function. The error goes away, but still nothing is being returned to the gallery items:

 

Len(Text(cyValue)) = 0

 

I do get the Delegation warnings all over the place, but this is a small list for now. I'll definitely have to deal with that issue in a year or two, but if you have any suggestion, it'll be appreciated.

@emfuentes27 ,

If it is a number, then cy_filter_1Selected.Value = 0 

 

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,591)