cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sudosaurus
Post Prodigy
Post Prodigy

Trying to filter on choice column in gallery

Hello,

 

I am trying to filter on a SharePoint choice column named "FormStatus" in my gallery however I am facing issues with the formula!

sudosaurus_0-1647950818810.png

 

Sort(
Filter('Colleague Data',
    (IsBlank(Search_textinput.Text) || Search_textinput.Text in 'Name' & 'Employee ID' & 'FormStatus'
     ) &&
    (FormStatus_dd.Selected.Result = "Form Status" || FormStatus = FormStatus_dd.Selected.Result)
),
Name,
Descending
)  

 I am not sure what I'm doing wrong here so any help would be appreciated.

 

Thanks! 🙂

1 ACCEPTED SOLUTION

Accepted Solutions
Mike2500
Super User
Super User

Ok, so checking a few things:

 

I assume there are fewer than 500 rows in the source table? The "in" statement should be throwing a delegation warning, which means that the filter will only apply to the first 500 rows in the source, so if a row matches the condition, but is after row 500, it won't be displayed. (that 500 is configurable, but not by too much).

 

Second, did you test this one condition at a time? For example, if you have just: Search_textinput.Text in 'Name' & 'Employee ID' & 'FormStatus'.Value, does it work? 

 

For the dropdown, add a label and set its value to: FormStatus_dd.Selected.Result to confirm it has an appropriate value.

 

View solution in original post

6 REPLIES 6
Mike2500
Super User
Super User

It looks like you are trying to set up a filter where rows are displayed if the text the user types into the search box matches the values in either the name, employee id, or formstatus fields?

 

Since it's a choice field, you need to reference the value:

 

Search_textinput.Text in 'Name' & 'Employee ID' & 'FormStatus'.Value

 

Same issue for the red lines under the equal sign:

FormStatus.Value = FormStatus_dd.Selected.Result

 

 

 

sudosaurus
Post Prodigy
Post Prodigy

@Mike2500 
What you assume is correct.

I have updated my formula but still not quite right..

sudosaurus_0-1647952307950.png

 

Sort(
Filter('Colleague Data',
    (IsBlank(Search_textinput.Text) || Search_textinput.Text in 'Name' & 'Employee ID' & 'FormStatus'.Value
     ) &&
    (FormStatus_dd.Selected.Result = "Form Status" || FormStatus.Value = FormStatus_dd.Selected.Result)
),
Name,
Descending

 

Perhaps I need more coffee, but I don't see any error indicators in your screen shot. Is it generating an error or is it technically working but not doing what you want it to do?

sudosaurus
Post Prodigy
Post Prodigy

@Mike2500, it should be showing data in my gallery but it's not.

I also have a drop-down item in my navigation with this formula:

sudosaurus_0-1647953117769.png

 

Maybe both arent aligning correctly?

 

Mike2500
Super User
Super User

Ok, so checking a few things:

 

I assume there are fewer than 500 rows in the source table? The "in" statement should be throwing a delegation warning, which means that the filter will only apply to the first 500 rows in the source, so if a row matches the condition, but is after row 500, it won't be displayed. (that 500 is configurable, but not by too much).

 

Second, did you test this one condition at a time? For example, if you have just: Search_textinput.Text in 'Name' & 'Employee ID' & 'FormStatus'.Value, does it work? 

 

For the dropdown, add a label and set its value to: FormStatus_dd.Selected.Result to confirm it has an appropriate value.

 

sudosaurus
Post Prodigy
Post Prodigy

Hi @Mike2500 

 

I went back to the basics as per your advise and then got it working using this formula! 🙂

 

Sort(
Filter('Colleague Data',
    (IsBlank(Search_textinput.Text) || Search_textinput.Text in Name & 'Employee ID' & Manager & 'Exec Director' & FormStatus.Value
     ) &&
    (FormStatus_dd.Selected.Result = "Form Status" || FormStatus.Value = FormStatus_dd.Selected.Result) &&
    (FormStatus_dd.Selected.Result = "Form Status" || FormStatus.Value = FormStatus_dd.Selected.Result
     )
),
ID,
Descending
)  

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (1,996)