Filter Question

Good morning @M_k! Can you share the link?

Hi @Nathalie_Collins

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

Thank you,
Mary. MK

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.

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

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…

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

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

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

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

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

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?

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

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

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

Wait a minute…“just 1 blank field”? Up until now you’ve been asking for a way to isolate records where there’s at least one blank, not just one blank. Big difference. Which do you want?

Based on the list of fields you want to check for blanks, I updated the formula. Here’s the revision.

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

As for the filter settings, there should only be one filter in the view:

Just be aware that this will show records with your original request of at least one blank field out of that list. If you want instead want records with just one blank, that will require a completely different formula to drive the filter.

Hi @Justin_Barrett

Sorry. My miss-type. I do want records with at least one blank field. Will your reply work for these records?

I figured out how to filter records with all blank fields.

Thank you,
Mary

It works in my tests, so I’m hoping it will meet your needs.