cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mgodby
Post Patron
Post Patron

Doing a Filter using multiple inputs, but need it to be so one of the inputs is blank you don't filter on a certain field

Hello. 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.

1 ACCEPTED SOLUTION

Accepted Solutions
wyotim
Resident Rockstar
Resident Rockstar

@Mgodby there are ways to filter by multiple items using only what you select/enter. Looking at the code you provided, here is an attempt to do so:

Filter(
    *DataSource*,
    'Seat ID' = SearchSeatID.Text,
    'Audit Number' = SearchAuditID.Text,
    'Build Event'.Name = SearchBuildEventComboBox.Selected.Name || SearchBuildEventComboBox.Selected.Name = Blank(),
    'Build Event'.Name = SearchLocationComboBox.Selected.Name || SearchLocationComboBox.Selected.Name = Blank(),
    If(
        IncompleteAuditsInput.Value,
        Text('Audit Status') <> "COMPLETE",
        true
    )
)

I'm not too certain that it will work as written, but it hopefully gives some pointers as to how to make it happen. For instance, when using combo boxes in a filter, there needs to be some way to differentiate between blank and selected items, so an or statement works well there. Also, returning the boolean value of true can be a nice trick for bypassing a section in a filter if a condition is not met. 

 

Feel free to reply back if I can help with this further. I'd be happy to help out if needed! You also might also check out this thread, where I was having a bit of the same issue a while back.

View solution in original post

1 REPLY 1
wyotim
Resident Rockstar
Resident Rockstar

@Mgodby there are ways to filter by multiple items using only what you select/enter. Looking at the code you provided, here is an attempt to do so:

Filter(
    *DataSource*,
    'Seat ID' = SearchSeatID.Text,
    'Audit Number' = SearchAuditID.Text,
    'Build Event'.Name = SearchBuildEventComboBox.Selected.Name || SearchBuildEventComboBox.Selected.Name = Blank(),
    'Build Event'.Name = SearchLocationComboBox.Selected.Name || SearchLocationComboBox.Selected.Name = Blank(),
    If(
        IncompleteAuditsInput.Value,
        Text('Audit Status') <> "COMPLETE",
        true
    )
)

I'm not too certain that it will work as written, but it hopefully gives some pointers as to how to make it happen. For instance, when using combo boxes in a filter, there needs to be some way to differentiate between blank and selected items, so an or statement works well there. Also, returning the boolean value of true can be a nice trick for bypassing a section in a filter if a condition is not met. 

 

Feel free to reply back if I can help with this further. I'd be happy to help out if needed! You also might also check out this thread, where I was having a bit of the same issue a while back.

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (58,997)