cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

How do I make it so I only do a filter on fields depending on whether or not certain input fields have value?

Hello everyone. I'm trying to build a search function, and here are the input fields. The only ones that matter in this issue I'm facing now are Seat ID, Audit ID, Location, Build Event and Incomplete Audits Only (boolean).

 

mycriteria.png

What I'm trying to do is set up a filter so it only does filters on these input fields that have value. For example, if Seat ID and Audit ID are the only ones with value then I want to do a filter using only them. So if somebody only has Build Event filled out, then I only want to apply a filter on that field and forget the rest. So essentially, filter this entity where build event is equal to what was put into the build event combo box. All the other fields are not filtered on.

 

This was my attempt. I tried to store my entity in a variable and then filter than variable in sequence depending on which input fields had value.


myattempt.png

 The issue with this was my lookup fields would not longer work or display in the gallery I'm going to be applying this filter to so I decided I couldn't use a variable.

 

Does anyone know a way to set this up so I don't have to essentially create a ton of if statements? Thank you very much.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: How do I make it so I only do a filter on fields depending on whether or not certain input fields have value?

Hi @Mgodby ,

Do you want to filter gallery items based on five fields that you listed and if you not enter any data in that field, you will not use that field as filter condition?

Could you tell me:

1)'Seat ID'field data type,'Aduit Number'field data type,'Build Event' field data type,'Audit Status' field data type

2)the Items of two combo boxes: SearchBuildEventComboBox, SearchLocationComboBox

3)the relationship between these two combo boxes, I notice that they both filter based on the same field 'Build Event'

 

Just based on the formula that you listed, I made a little change, try this:

Filter(AuditHeaderVariable,
       If(IsBlank(SearchSeatId.Text),true,'Seat ID'=SearchSeatId.Text),
       If(IsBlank(SearchAuditId.Text),true,'Audit Number'=SearchAuditId.Text),
       If(IsEmpty(SearchBuildEventComboBox.SelectedItems),true,'Build Event'.Name=SearchBuildEventComboBox.Selected.Name),
       If(IsEmpty(SearchLocationComboBox.SelectedItems),true,'Build Event'.Name=SearchLocationComboBox.Selected.Name),
      If(IncompletdAuditsInput.Value,Text('Audit Status')<>"Completed")
      )

 You just need to use If statements to justify whether you enter data, If you not enter any data, you could set the condition as "true".

So just need to use formula like this:

Filter(table,
         If(IsBlank(...),true,condition1),
         If(IsBlank(...),true,condition2),
        ....)

What's more, I think the 'Audit Status' field should be choice type. If so, you should try this:

Filter(AuditHeaderVariable,
       If(IsBlank(SearchSeatId.Text),true,'Seat ID'=SearchSeatId.Text),
       If(IsBlank(SearchAuditId.Text),true,'Audit Number'=SearchAuditId.Text),
       If(IsEmpty(SearchBuildEventComboBox.SelectedItems),true,'Build Event'.Name=SearchBuildEventComboBox.Selected.Name),
       If(IsEmpty(SearchLocationComboBox.SelectedItems),true,'Build Event'.Name=SearchLocationComboBox.Selected.Name),
      If(IncompletdAuditsInput.Value,'Audit Status'.Value<>"Completed")
      )

 

 Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

Re: How do I make it so I only do a filter on fields depending on whether or not certain input fields have value?

Have you checked the warning signs on the dropdown. What does the error say..
Highlighted
Community Support
Community Support

Re: How do I make it so I only do a filter on fields depending on whether or not certain input fields have value?

Hi @Mgodby ,

Do you want to filter gallery items based on five fields that you listed and if you not enter any data in that field, you will not use that field as filter condition?

Could you tell me:

1)'Seat ID'field data type,'Aduit Number'field data type,'Build Event' field data type,'Audit Status' field data type

2)the Items of two combo boxes: SearchBuildEventComboBox, SearchLocationComboBox

3)the relationship between these two combo boxes, I notice that they both filter based on the same field 'Build Event'

 

Just based on the formula that you listed, I made a little change, try this:

Filter(AuditHeaderVariable,
       If(IsBlank(SearchSeatId.Text),true,'Seat ID'=SearchSeatId.Text),
       If(IsBlank(SearchAuditId.Text),true,'Audit Number'=SearchAuditId.Text),
       If(IsEmpty(SearchBuildEventComboBox.SelectedItems),true,'Build Event'.Name=SearchBuildEventComboBox.Selected.Name),
       If(IsEmpty(SearchLocationComboBox.SelectedItems),true,'Build Event'.Name=SearchLocationComboBox.Selected.Name),
      If(IncompletdAuditsInput.Value,Text('Audit Status')<>"Completed")
      )

 You just need to use If statements to justify whether you enter data, If you not enter any data, you could set the condition as "true".

So just need to use formula like this:

Filter(table,
         If(IsBlank(...),true,condition1),
         If(IsBlank(...),true,condition2),
        ....)

What's more, I think the 'Audit Status' field should be choice type. If so, you should try this:

Filter(AuditHeaderVariable,
       If(IsBlank(SearchSeatId.Text),true,'Seat ID'=SearchSeatId.Text),
       If(IsBlank(SearchAuditId.Text),true,'Audit Number'=SearchAuditId.Text),
       If(IsEmpty(SearchBuildEventComboBox.SelectedItems),true,'Build Event'.Name=SearchBuildEventComboBox.Selected.Name),
       If(IsEmpty(SearchLocationComboBox.SelectedItems),true,'Build Event'.Name=SearchLocationComboBox.Selected.Name),
      If(IncompletdAuditsInput.Value,'Audit Status'.Value<>"Completed")
      )

 

 Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (10,808)