Skip to main content
Solved

"Find Records" automation & dynamic variables - only manually entered values accepted?

  • March 25, 2021
  • 5 replies
  • 144 views
L

Hi,

I’ve been using the “Find Records” aspect of automations and super helpful.

It doesn’t seem to be well documented anywhere though, and where I am very confused is that often there will a value from a previous step that I want to use (dynamic), but it is not available, only <>length. Unhelpfully rather than being greyed out, or not visible, I always end up clicking through to find it’s not available!

From what I can tell, you can not use any of the following as a dynamic variable in the Find Records automation:

  • Formula value
  • Linked record
  • Roll-up

Considering those make up a large percentage of my tables, it’s a little frustrating! Indeed it seems that the only values you can actually use dynamically, are ones that have been manually populated.

But that means either having to actually populate those fields manually, or using an automation to print the value int he field (so it looks like it has been created manually). But that seems an awful waste of a precious automation given you get a measly 25 and I’m already on the edge!

Has anyone else experienced this? Is this documented anywhere? Anyone found any good workarounds?

And Airtable, any plans to change this behaviour in the near future?

Regards,

Luke

Best answer by Justin_Barrett

Hi Justin,

Many thanks for your reply.

To give you some context, I use Airtable for project management, with multiple projects within a single base. There is one “projects overview” table though which contains lots of key information about each project (start date, end date, description etc).

I am using Automations to send emails to all the people involved in the projects, listed in a different table (we’ll call it employees table). Within these emails I commonly want to include many of the data points that are included in the projects overview table.

Given that each person is linked to a project via a linked record (only ever singular), in the past I ended up using lots of look-ups from the overview table to ensure all the info I wanted to include in the email template was within the employees table. This causes a huge amount of columns though, so when I noticed Find records I thought it’d be a great opportunity to streamline a little.

So what I’m trying to do is use the linked project in the employees table to “find” the right project in the overview table, then include the right info in the email. I have tried using the linked record itself. I then tried using a roll-up, pulling the linked record project ID from the overview table. I have also tried a formula column to “print” the linked project record ID. No joy on any of these.

Note that I also created a “record ID” column in the overview table as well and tried to use that to find the record.

I guess the main question I have is what does allow you to find records? So what is allowed and what is constrained.

Any pointers in the right direction would be hugely appreciated.


Sorry for the delayed response.

I haven’t had time to do an exhaustive test, but it sounds like calculated fields are off-limits, which includes the lookup, rollup, and formula fields that you’ve tried. I’m guessing that other calculated fields like autonumber and created time may also be unusable.

In that light, I can think of a way to get the value that you want into the “Find records” action, though it’s a bit of a hack: insert an “Update record” action that copies any calculated values into manual entry fields before the “Find records” action, then use those manual entry fields as the input for “Find records.”

For example, make a single line text field named something like {Record ID Copy}. After the automation’s trigger step, add an “Update record” action that copies the record ID from a lookup/rollup/formula field into this {Record ID Copy} single line text field. Then in “Find records”, you should be able to use the record ID in {Record ID Copy} to find the record. A similar process should work to get access to any other calculated data type. The downside is that you end up with extra fields that are just placeholders for data that you want to use in a search.

C
I
A

5 replies

Justin_Barrett
Forum|alt.badge.img+21

In all of those cases, there are various factors that may or may not make them work in specific situations.

  • Formulas can output different types of values: strings, numbers or dates.
  • Linked records are stored internally as arrays, which makes them problematic. A formula can automatically convert that array into a single string representing the primary field contents of all linked records, but that may not be what you want.
  • Rollups also operate on arrays, but the rollup field’s aggregation formula is generally designed to create a single data value as output, similar to a normal formula.

I’ve got pretty much the same question for all three: what data are you hoping to retrieve from each of these field types, and how exactly do you want to use that data in the context of a “Find records” automation action? Knowing your desired end goal will allow us to guide you in the right direction.

C
L
  • Luke_Branford1
  • Author
  • New Participant
  • March 26, 2021

Hi Justin,

Many thanks for your reply.

To give you some context, I use Airtable for project management, with multiple projects within a single base. There is one “projects overview” table though which contains lots of key information about each project (start date, end date, description etc).

I am using Automations to send emails to all the people involved in the projects, listed in a different table (we’ll call it employees table). Within these emails I commonly want to include many of the data points that are included in the projects overview table.

Given that each person is linked to a project via a linked record (only ever singular), in the past I ended up using lots of look-ups from the overview table to ensure all the info I wanted to include in the email template was within the employees table. This causes a huge amount of columns though, so when I noticed Find records I thought it’d be a great opportunity to streamline a little.

So what I’m trying to do is use the linked project in the employees table to “find” the right project in the overview table, then include the right info in the email. I have tried using the linked record itself. I then tried using a roll-up, pulling the linked record project ID from the overview table. I have also tried a formula column to “print” the linked project record ID. No joy on any of these.

Note that I also created a “record ID” column in the overview table as well and tried to use that to find the record.

I guess the main question I have is what does allow you to find records? So what is allowed and what is constrained.

Any pointers in the right direction would be hugely appreciated.

C
Justin_Barrett
Forum|alt.badge.img+21

Hi Justin,

Many thanks for your reply.

To give you some context, I use Airtable for project management, with multiple projects within a single base. There is one “projects overview” table though which contains lots of key information about each project (start date, end date, description etc).

I am using Automations to send emails to all the people involved in the projects, listed in a different table (we’ll call it employees table). Within these emails I commonly want to include many of the data points that are included in the projects overview table.

Given that each person is linked to a project via a linked record (only ever singular), in the past I ended up using lots of look-ups from the overview table to ensure all the info I wanted to include in the email template was within the employees table. This causes a huge amount of columns though, so when I noticed Find records I thought it’d be a great opportunity to streamline a little.

So what I’m trying to do is use the linked project in the employees table to “find” the right project in the overview table, then include the right info in the email. I have tried using the linked record itself. I then tried using a roll-up, pulling the linked record project ID from the overview table. I have also tried a formula column to “print” the linked project record ID. No joy on any of these.

Note that I also created a “record ID” column in the overview table as well and tried to use that to find the record.

I guess the main question I have is what does allow you to find records? So what is allowed and what is constrained.

Any pointers in the right direction would be hugely appreciated.


Sorry for the delayed response.

I haven’t had time to do an exhaustive test, but it sounds like calculated fields are off-limits, which includes the lookup, rollup, and formula fields that you’ve tried. I’m guessing that other calculated fields like autonumber and created time may also be unusable.

In that light, I can think of a way to get the value that you want into the “Find records” action, though it’s a bit of a hack: insert an “Update record” action that copies any calculated values into manual entry fields before the “Find records” action, then use those manual entry fields as the input for “Find records.”

For example, make a single line text field named something like {Record ID Copy}. After the automation’s trigger step, add an “Update record” action that copies the record ID from a lookup/rollup/formula field into this {Record ID Copy} single line text field. Then in “Find records”, you should be able to use the record ID in {Record ID Copy} to find the record. A similar process should work to get access to any other calculated data type. The downside is that you end up with extra fields that are just placeholders for data that you want to use in a search.

I
T
R
L
  • Luke_Branford1
  • Author
  • New Participant
  • April 8, 2021

Sorry for the delayed response.

I haven’t had time to do an exhaustive test, but it sounds like calculated fields are off-limits, which includes the lookup, rollup, and formula fields that you’ve tried. I’m guessing that other calculated fields like autonumber and created time may also be unusable.

In that light, I can think of a way to get the value that you want into the “Find records” action, though it’s a bit of a hack: insert an “Update record” action that copies any calculated values into manual entry fields before the “Find records” action, then use those manual entry fields as the input for “Find records.”

For example, make a single line text field named something like {Record ID Copy}. After the automation’s trigger step, add an “Update record” action that copies the record ID from a lookup/rollup/formula field into this {Record ID Copy} single line text field. Then in “Find records”, you should be able to use the record ID in {Record ID Copy} to find the record. A similar process should work to get access to any other calculated data type. The downside is that you end up with extra fields that are just placeholders for data that you want to use in a search.


Hi Justin, no problems at all on the delay, appreciate you getting back to me.

I came to the same conclusion that all calculated fields are off limits. Seems a bit strange seeing as you are referencing the record itself, and if you undertake the same function in Zapier (using find records), calculated fields are fair-game. I’m sure there is a perfectly reasonable tech explanation somewhere though.

I actually ended up doing exactly as you suggested, and although extra fields aren’t optimal, it works! Hopefully somebody else will find this helpful too.

Thanks again.

N
Justin_Barrett
Justin_Barrett
Forum|alt.badge.img+21

My pleasure! Glad to know that you got the answer you were seeking! If you would, please mark my comment (the one above, not this one) as the solution to your question. This helps others who may be searching with similar questions. Thanks!

I
T
L
Terms & ConditionsAccessibility statement