How to lookup/return most recent non-blank entry

[Ben_Simai]

Hi Airtable community,

We have a business that runs multiple programs and we use Airtable forms for signups.
One person can sign up to multiple programs.

I have created 2 key tables:

1. Raw forms table: Where all the raw data from the forms is entered. This can have multiple forms per person.
2. CRM: One row per person. Each form from the forms table is linked to a single person.

Example that explains the issue:
Let’s say in a form people need to enter their ‘Bio’. In the CRM table if I do a basic lookup of bio for a particular person’s forms I will get multiple values if they have filled out multiple forms. I would like a way to just lookup their most recent ‘Bio’ entry. This can be done. However, what if in that person’s most recent form (we have all sorts of forms, not just signup forms) they did not enter a bio and the bio field is blank? So I need a way to return the most recent non-blank entry.

I have created a Loom video to demonstrate the issue.

Any help would be awesome!

[ScottWorld]

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

[Ben_Simai]

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

[ScottWorld]

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

[Ben_Simai]

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:

[ScottWorld]

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

- ScottWorld, Expert Airtable Consultant

[Ben_Simai]

@ScottWorld made another Loom video explaining

[ScottWorld]

@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

[Ben_Simai]

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!