Can A Script Filter Records In A Base?

Development and integrations Scripting extension
#1

Hi Airtable!

I thought I would repost this again, since filters did not do the job.

Maybe a script might work? I am not sure if this will cost something.

I am trying to filter, only records that has one or more fields that are empty.

I have created a share link to a copy of the database, if anyone wants to take a look.

(https://airtable.com/shrIVQ1d9ckYOpEuV)

1200×630 21.7 KB

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 Kay

Formula issue: duration field
#2

Could a script do this? Yes.

But it can also be done without a script. Create a formula field that that checks all of the records and then filter on the formula field.

The formula field would look something like this …


IF({field1} = BLANK(), 1, 0) +

IF({field2} = BLANK(), 1, 0) +

IF({field3} = BLANK(), 1, 0) +

IF({field4} = BLANK(), 1, 0) +

IF({field5} = BLANK(), 1, 0)

The number in the formula field would be the number of blank fields. if the value of the formula field is > 1, then there is at least one blank field.

I often do something like this in a base if I want a field to help me quickly spot records that might have incomplete information.

#3

UPDATE
I got the formula to work. Now, what if I want Records that has all blank fields, except for the SCOTT #?

Hi @kuovonne

Thank you for your help.

I was trying to set up the filter, but I keep getting an error. I also, wasn’t sure if I have to set up another field/fields. I am also not sure if I just need to include some of the fields in the record or all the fields in the record.

This is how I set up the filter:

IF({SCOTT #} = BLANK(), 1, 0) +

IF({SETS} = BLANK(), 1, 0) +

IF({STAMP DESCRIPTION} = BLANK(), 1, 0) +

IF({SUBJECT} = BLANK(), 1, 0) +

IF({DATE ISSUED} = BLANK(), 1, 0) +

IF({STAMP FORMAT} = BLANK(), 1, 0) +

IF({FACE VALUE} = BLANK(), 1, 0) +

IF({KEYWORDS} = BLANK(), 1, 0)

Thank you,
Mary Kay

1 Like
#4

Put the formula in a new formula field. Then filter on the value of the formula field.

#5

@M_k Perhaps I’m missing part of your question here, but I think this could also be done even more simply with the built-in Filters that Airtable provides.

Screen Shot 2020-03-11 at 8.05.14 AM
Screen Shot 2020-03-11 at 8.05.14 AM769×528 67.1 KB

This screen shot only shows some of the fields listed, but I think this gets at what you want. I think this also makes it more flexible if you add more fields later on. You can also change the “Or” to “And” and then it will display records with only blank cells.

2 Likes
#6

Yes, this is much simpler.

I tend to fall back on solutions that I use, and I use a slightly more robust form of the method I suggested here (creating a formula field and filtering on that field) because I personally have a base where the built-in filters do not suffice (the built-in filters cannot handle both “and” and “or” conditions), and I like more information that summarizes what is missing.

#7

Hi @kuovonne

Yes. I agree. I did try the filters, only, as suggested by @Matthew_Thomas, but it was not catching all the records. That’s why I was hoping there was another way to “catch” the records.

The formula that you suggested seems to have done the job.

Thank you for your help. I really appreciate it.

Mary K

1 Like