How to lookup/return most recent non-blank entry

That’s a little tricky to accomplish in Airtable. I explain how to do it on this Airtable podcast episode.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

Hey @ScottWorld ,
Thanks for replying :slightly_smiling_face:
I just watched your video - I should watch these more often!
I have actually already set up that system and it works well, however, I need an extra step where it doesn’t just return the most recent entry but the most recent NON-BLANK entry, the reason is explained in my Loom video.
Fingers crossed there is a solution

Just set the conditionals on the rollup field to only pull from records where the fields you specify are not empty.

(Or create a formula field that determines whether a record is empty or not, and have your rollup field’s conditional evaluate that formula field.)

- ScottWorld, Expert Airtable Consultant

Hey @ScottWorld ,

That makes sense.
The major downside is that I would need to do that for every field I want to look up. So, I need to create an extra 3 columns for each field which doesn’t seem very practical/sustainable :confused:

Why? As far as I can tell, you just need to modify your rollup field by adding conditions there.

- ScottWorld, Expert Airtable Consultant

@ScottWorld made another Loom video explaining

@Ben_Simai

Here’s the solution:

Since each form is considered blank for a different reason & your records are each tied to a different form submission, I would just create 3 automations to tag each record with the name of the form that they came from. (Alternatively, you could tag each record by using a prefilled hidden field on the form.)

Then, create one master formula that compares the name of the form to its one particular field that causes it to be a blank submission.

For example:

IF(
AND({Form Name}="Form A",{Field that makes Form A Blank}=""),"Blank",
IF(
AND({Form Name}="Form B",{Field that makes Form B Blank}=""),"Blank",
IF(
AND({Form Name}="Form C",{Field that makes Form C Blank}=""),"Blank",
"Filled In"
)))

Then you can create a single conditional for your rollup field that just looks at this formula.

Hope this helps! If you’d like to hire an expert Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

IF(
AND({Form Name}="Form A",{Field that makes Form A Blank}=""),"Blank",
IF(
AND({Form Name}="Form B",{Field that makes Form B Blank}=""),"Blank",
IF(
AND({Form Name}="Form C",{Field that makes Form C Blank}=""),"Blank",
"Filled In"
)))

Hey @ScottWorld,

Thanks so much for your time & solutions!

This solution would work if there were only a couple of forms and only one field per form that renders the form ‘blank’ but unfortunately in our case we have many forms and many fields.

But that’s ok, we will be able to work around this issue for now (perhaps until a “most recent non-blank” formula is created) using the combination of rollup/lookup fields as described earlier as well as manually editing raw data.

Thanks again!