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

[Mgodby]

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

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.

 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.

[wyotim · Accepted solution]

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

[cidreno]

Hi @wyotim, it's been awhile since this post, but would you mind explaining to me how/why this works. I'm dealing with the same issue. When I plugged in your solution into my Gallery.Items code, it did not ignore the blank input field.

In my case, I have four input fields:

• inpProjectID
• cmbTown
• cmbEmployer
• inpName

The code I used was 

Filter(
    'dbo.Project',
    ProjectID = Value(inpProjectID.Text) || Value(inpProjectID.Text) = Blank(),
    PuebloID = Value(First(cmbTown.SelectedItems).TownID) || Value(First(cmbTown.SelectedItems).TownID) = Blank() 
    EmployerID = Value(First(cmbEmployer.SelectedItems).EmployerID) || Value(First(cmbEmployer.SelectedItems).EmployerID) = Blank(),
    inpName.Text in Name|| inpName.Text = Blank()
)

Any help would be appreciated.

I read the thread that you referred to. I just didn't read the blog post that Ericonline pointed you to, because my computer didn't trust it, so I dared not.

[wyotim]

Hi @cidreno and thanks for reaching out. You might try the following and see if it works for you:

Filter(
    'dbo.Project',
    ProjectID = Value(inpProjectID.Text) || inpProjectID.Text = Blank(),
    PuebloID = cmbTown.Selected.TownID || cmbTown.Selected.TownID = Blank() 
    EmployerID = cmbEmployer.Selected.EmployerID || cmbEmployer.Selected.EmployerID = Blank(),
    inpName.Text in Name || inpName.Text = Blank()
)

The basic way that it works is by either providing a value to search (for instance PuebloID = cmbTown.Selected.TownID, which checks for that ID) or a response of true, which tells the filter that the condition is met (for instance cmbTown.Selected.TownID = Blank(), which would indicate that no item was selected). 

It's a little strange (to me at least) but filtering is, in a way, looking for boolean values (it found the thing or it didn't). By making a result return true it 'fools' the filter into thinking that it found what it was looking for. So leaving any of those filter items blank, it treats it like it found what it was after without doing any filtering for that item.

In terms of the code you posted, my suggestion just simplifies things a bit and might help your code be a bit easier to read. Hopefully, it works as intended but let me know if it doesn't!

Please let me know if that doesn't explain things adequately; I'm happy to follow up on any further questions you may have or try to explain it better if needed.

Cheers!

[cidreno]

tricky tricky tricky.

the strange thing is that it works perfectly without including the ProjectID line. 

    ProjectID = Value(inpProjectID.Text) || inpProjectID.Text = Blank()

When I include that line (which is the Identity column in SQL, fwiw), the gallery remained empty unless the inpProjectID input control was not blank. Any ideas why this is happening?

Also, thanks for cleaning up my code! I didn't know about ComboBox.Selected. So much easier!!!

[wyotim]

So it looks like I led you astray there. After a quick test, that should be either

ProjectID = Value(inpProjectID.Text) || inpProjectID.Text = ""

or

ProjectID = Value(inpProjectID.Text) || Value(inpProjectID.Text) = Blank()

My guess as to why that would be is that it must be in how it is handling the text vs. a blank number. Blank text must not resolve to an unknown or blank value but instead an empty string (which makes sense), whereas an empty number value is unknown. I was just assuming it would treat them the same. Always learning, right?

And glad that little rewrite was helpful! Using .Selected is pretty nice as you can add a field at the end or use it to reference the whole selected record. 👍 

Let me know if I can be of any further help with that and happy Power Apping!

[cidreno]

yup. this worked. fantastic. thanks so much. and for detailed explanations, too!

[cidreno]

just made an alteration. i added one Text Input control called inpSearchMap in which the user can type in anything and it will get filtered by all relevant columns (like google maps).

my code looks like this

Filter(
    'dbo.Project',
    inpSearchMap.Text in Town|| 
    inpSearchMap.Text in Employer || 
    inpSearchMap.Text in Name|| 
    inpSearchMap.Text = Blank()
)

So clean and elegant!!! (I jettisoned the ProjectID b/c people don't really know that anyway)

[wyotim]

Very nice! Well done! 👍

[cidreno]

😁