Filter Question

[M_k]

Hi

I have a filter question. I am trying to only filter records that has one or more fields that are empty, I am also connecting the individual fields with OR condition. That filter worked, but the filter is also including records that has all empty fields, except for the key field, which I don’t want to show. I don’t understand why.

I have also created a separate filter, in the same database, to show all records that has empty fields, except the key field and the fields in the filter are connected by AND option. This filter is showing only records with empty fields, except the key field, which is how I want the filter to show, so this filter is working.

I don’t understand why the two filters in the same base are showing different results.

I have created a share link to a copy of the database, if anyone wants to take a look. The two filters are:

1. ONE OR MORE EMPTY FIELDS (UPDATE) **This is the view that I am having issues with.
2. EMPTY FIELDS (NEW)

Airtable

COPY 1 CSV FACE VALUE 1. GENERAL STAMPS CE1 TO 1708aii GOOD 1 copy copy -...

Explore the "COPY 1 CSV FACE VALUE 1. GENERAL STAMPS CE1 TO 1708aii GOOD 1 copy copy" base on Airtable.

Thank you,
Mary K

[Nathalie_Collin]

Good morning @M_k! Can you share the link?

[M_k]

Hi @Nathalie_Collins

I had the link, then I accidentally deleted it, but it is now added to post #1.

Thank you,
Mary. MK

[W_Vann_Hall]

Mary -

I’m not exactly sure what you want the ONE OR MORE EMPTY FIELDS (UPDATE) filter to show. Should it only display fields where

1. {Scott} is not empty
2. At least one of the other fields is not empty
3. At least one of the other fields is empty

If so, I’m not sure you can do it entirely using filters, as you can’t nest Boolean tests in filters; however, you can create a more-complex formula field in the base and filter against it.

Let me know what you want to achieve, and I’ll see if I can help find a way.

[M_k]

Hi @W_Vann_Hall

I hope all is well with you.

I was wondering if just using filters was going to work.

What I am trying to do with the filters is eliminate all the records that has all of the fields that are empty, except the key field (SCOTT#.) I wondered if that can be done, if not, I will just have to work around the issue.

I will just use the filters to filter out the records that has at lest one of the fields empty, except for the key field (SCOTT #,), but not records with all empty fields.

Thank you,
Mary

[W_Vann_Hall]

What I would do is define an additional field called something like {ALL BLANK} and then filter on

Where    {SCOTT} is not empty
And      {ALL BLANK} = 1

That would reveal everything where, well, {SCOTT#} isn’t empty but your other targeted fields are. You could use this as your formula for {ALL BLANK}:

NOT(
    OR(
        {SUBJECT},
        {DATE ISSUED},
        {STAMP FORMAT},
        {FACE VALUE},
        {KEYWORDS},
        {MATCHED STAMPS},
        {STAMPS CHECKED},
        {Dupe Check}
        )
    )

If you want to test for at least one of the fields being blank, use this formula for {AT LEAST 1 BLANK}:

OR(
    NOT({SUBJECT}),
    NOT({DATE ISSUED}),
    NOT({STAMP FORMAT}),
    NOT({FACE VALUE}),
    NOT({KEYWORDS}),
    NOT({MATCHED STAMPS}),
    NOT({STAMPS CHECKED}),
    NOT({Dupe Check})
    )

One of the advantages of using a designated filter field such as {ALL BLANK} or {AT LEAST 1 BLANK} is that you can use it in multiple filters rather than having to define variations on the filter if you want, say, a view that filters all blank including {SCOTT#} and another that filters all blank excluding {SCOTT#}. Also, if you later add a field that you want to include in the filter, you can do so by adding it once to the filter field rather than having potentially to update multiple views.

I should mention in your example base, your filter is testing for {MATCHED STAMPS} and {STAMPS CHECKED} not being empty. If that’s what you want, you’ll need to modify the formula code by wrapping those fields in NOT() in the first example and removing NOT() from the second…

[M_k]

Hi @W_Vann_Hall

Thank you so much for your help!

I went ahead and set up the new fields and formulas, I changed all filter parameters to EMPTY, from NOT EMPTY in the filters, for those two fields that you mentioned in your post.

I created the new field, formula and subsequent filter for records with all blank fields, except for SCOTT#, and that worked.

I seem to be stuck with creating a filter for more than one blank field. I believe that I set up the correct filter and field, but I am still getting records with all blank fields mixed in with with records with more than one blank field, excluding SCOTT# (that should be filled.)

I basicly need the filter to capture all records that has at least one blank field, so that I can update the record using Integromat. The criteria for records that needs to be updated in Integromat, has to have at least one empty field. I hope that this will help clarify the issue more, if not, just let me know.

Thank you,
Mary

[M_k]

Hi @W_Vann_Hall

Did you have a chance to take a look at my latest reply?

I am hoping to resolve this, so I can use it in my base.

Thank you,
Mary

[Justin_Barrett]

Looking at the logic of your filter, it’s only operating based on 9 fields. The way that logic works, if any of those 9 fields are empty, it will match the filter and include the record, regardless of whether or not other fields are also blank. If you don’t want it to return records where other fields are blank as well, that means the filter is incomplete. The way I understand it, you want the filter to work roughly like this:

IF {SCOTT #} is not blank
AND at least one of [these 9 fields] is blank
AND at least one of [these remaining fields] is not blank
include the record

For example, the record for SCOTT #275 (record 14) should not be included because everything is blank except for {SCOTT #}. Is that correct?

If that’s the case, this can’t be done purely with the filter feature because filters can’t mix OR and AND operators. You need to enlist the help of a formula field to determine which records to include, and then use a filter on that field’s results, which is what @W_Vann_Hall suggested. Using his example as a starting point, I came up with this formula which I think matches the logic you want:

AND(
    {SCOTT #},
    OR(
        NOT({SUBJECT}),
        NOT({DATE ISSUED}),
        NOT({STAMP FORMAT}),
        NOT({FACE VALUE}),
        NOT({KEYWORDS}),
        NOT({MATCHED STAMPS}),
        NOT({STAMPS CHECKED}),
        NOT({Dupe Check})
    ),
    OR(
        {STAMP DESCRIPTION},
        {LOCATION},
        {WEBSITES},
        {STAMP IMAGE}
    )
)

With that formula in place, the filter only needs to target that field and show anything with a result of 1.

Does that work?

[M_k]

Hi @Justin_Barrett

Thank you for your reply.

I used the updated formula, for at least one blank field, that you were kind to provide, but I am still getting records with all blank fields, mixed in, when I set up the filter.

I tried different condition combinations for filters, but I am unable to have records with just one blank field. There seems to be records overlapping between the two types of filters, which is what you mentioned, but, of course, I am trying to separate them.

The record that you mentioned, that has all blank fields, was an example of what I want to separate from the other records, with at least one blank field. I am able to get the filter, to separate all blank fields, to work.

I should also mention that the main fields that need to have at least one field blank are listed below:

STAMP DESCRIPTION
SUBJECT
DATE ISSUED
STAMP FORMAT
FACE VALUE
KEYWORDS

I don’t know if this information might help, but I thought I would mention this.

Thank you,
Mary