Filters showing blank rows

[zapperrr]

Hi all,

The issue I've been stumped on is If I'm filtering, some of the columns that I'm filtering are displaying as blank which shouldn't be the case. I want to be able to filter and accurately get anything that I specified, without the blanks. Could anyone please assist with this? I'm pretty lost but would also like to ensure those blank views are being displayed without filtering.

Sort(
    Filter(
        'IT Equipment List',
        textInputSearch.Text in User || User = Blank(),
        SInput.Text in 'Serial Number' || 'Serial Number' = Blank(),
        ModelInput.Text in Model || Model = Blank(),
        IsBlank(Dropdown1.Selected.Result) || 'Equipment Type'.Value = Dropdown1.Selected.Result,
        IsBlank(Dropdown1_1.Selected.Result) || Location.Value = Dropdown1_1.Selected.Result
    ),
    User
)

[RandyHayes]

@zapperrr 

Which columns?  You are specifically allowing blank columns for User and Serial Number in your formula!

[zapperrr]

Yeah, I understand that but that's how I have it set up to display the Serial Number, Model and User column even if they're blank. Is there a better way of going around that and setting it up so I could filter properly?

[RandyHayes]

@zapperrr 

Ah, gotcha...I was interpreting this incorrectly - "I'm pretty lost but would also like to ensure those blank views are being displayed without filtering."

However, your current filter statement should be showing those records where User, Serial Number and Model are blank.  But, you have these in an AND condition...that means that in order to see one blank, they all need to be blank.

If you just want to have those records where one of them may be blank, then add it to your conditions:

Sort(
    Filter(
        'IT Equipment List',
        textInputSearch.Text in User,
        SInput.Text in 'Serial Number',
        ModelInput.Text in Model,
        IsBlank(Dropdown1.Selected.Result) || 'Equipment Type'.Value = Dropdown1.Selected.Result,
        IsBlank(Dropdown1_1.Selected.Result) || Location.Value = Dropdown1_1.Selected.Result,

        IsBlank(User) || IsBlank('Serial Number') || IsBlank(Model)
 
    ),
    User
)

Since you are adding this specific condition, it would cover the first 3 "in" conditions where you had the OR for the blank column, and so they are not needed there.

[zapperrr]

My apologies on the confusion! It's been a crazy few days. 

Inserting this code doesn't yield any rows displaying in my DataTable, I'm assuming it's something to do with my table? 

[RandyHayes]

@zapperrr 

Well...I understand that!!

So, the condition specifically looked for is going to be broken down like this:

textInputSearch.Text in User - what is in the text must be in the User field or the text is blank

SInput.Text in 'Serial Number' - what is in the text must be in the serial number, or the text is blank

ModelInput.Text in Model - what is in the text must be in the Model, or the text is blank

IsBlank(Dropdown1.Selected.Result) || 'Equipment Type'.Value = Dropdown1.Selected.Result -

The dropdown selection Result column is blank, OR the equipment type column Value is equal to the selection result column.

IsBlank(Dropdown1_1.Selected.Result) || Location.Value = Dropdown1_1.Selected.Result -

The dropdown selection Result column is blank, OR the Location column Value is equal to the selection result column.

IsBlank(User) || IsBlank('Serial Number') || IsBlank(Model) -

User is Blank or Serial Number is Blank or Model is blank

NOTE: ALL of the above conditions must result in a true in order for the record to be returned.

Perhaps I am still misunderstanding exactly what you are looking for.  So, I spelled out the above conditions and, don't forget, they ALL must be true in order for the record to be returned.  

Please correct me if I am not getting exactly what you want.

[zapperrr]

Thank you for the in-depth explanation. I definitely need to buy you a coffee. 🙂

I'll try to explain this without any confusion (fingers crossed):

At the moment, if I were to filter by my Serial Number column, rows that don't have anything in the Serial Number column show up even when filtering, I'd like for that to not happen. 

I had it filtering perfectly previously but the only issue was it wasn't displaying any rows that had Serial Number as blank when I wasn't filtering. 

Here I'm filtering for 5CDO in the serial number column. 

When filtering like this, the 5CDO[...] serial number shows up, along with columns that have Serial Number blanked out.

I hope this made more sense.