Create new record for each record returned by “find records"

I have one table filled with dorms. The dorms have other fields like “Building” and “Floor.”

I have another table with assignments to clean specific dorms. If I create a cleaning assignment but instead of a specific dorm number, I enter just a building, I want a “Find Records” automation to find all the dorms assigned to that building (that part I know how to do) and then I want the automation to turn around and create a separate cleaning assignment record in Table B for each Dorm record found in Table A.

How can I do this? I bangin` my head against the wall trying to figure it out. I think I would prefer a script but I don’t even know how to begin with that.

Hey Jeff, as you’ve found out, automations doesn’t have an in built way for you to loop through results and create new records, so we’re left with weird workarounds

I’ve put something together for you here. You can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button. You’ll then be able to see all the formulas, how the fields are set up, automations, etc

The workaround involves using the automation to paste unique comma separated values into a link field, thereby creating however many records you need immediately.

The Dorms are all linked to a Building field, and so we can get all the related dorms with a rollup field.

We then use a formula field to create the unique comma separated values, in this case in the format:
[Assignment name] [Dorm name]

And finally we use the automation to paste this text value into the linked field to Table B in the Assignments table.

You’re going to have to make some tweaks as I imagine you’d probably want to create assignments for single dorms as well, but this should be enough to get you going!

Let me know if you have any issues!

1 Like

This seems to be the #1 most frequently asked question in this forum!

I feel like we’ve gotten this question at least 15 times in the last month, and it’s all due to Airtable’s terrible implementation of the “Find Records” action.

Clearly, this is a huge pain point for Airtable customers.

First and foremost, I would highly recommend that everybody send an email to support@airtable.com to ask them to improve how the “Find Records” action works. They have an absolutely horrendous track record of listening to customers, which is why our only hope is to get MANY PEOPLE to email them to ask them to improve this. If we don’t speak up en masse, all hope is lost.

In the meantime, there are only 3 solutions to this problem:

(1) Some variation of the automation workarounds listed above, which keeps you in Airtable but requires adding extra clunkiness into your base. Extra tables, extra fields, and extra automations.

(2) Scripting, which keeps you in Airtable but requires you to know JavaScript.

(3) My personal favorite solution, which takes you outside of Airtable, but in my personal opinion is the cleanest & easiest & simplest to implement. And it’s 100% free too, as long as you stay under 1,000 operations per month.

Make.com ALWAYS loops through the results of a search, so you can very easily perform a find and then immediately create new records for each record returned by the search.

This is how dead simple the setup for this would be in Make.com:

That’s it! Two simple steps. It searches the records, and creates a new record for each record found in the search.

2 Likes

Lol. We all have our personal favorites. Scripting is my personal favorite.

  • My #1 reason is that all of the logic is retained in one place in the base, from trigger all the way through the completion of the automation. This reason dwarfs all of my other reasons. With a third party automation service, the logic is automatically split into at least two places—the base and the third party service.

  • Transferring an automation script between bases is mostly a matter of copy/paste and setting up a few input variables. This makes using separate development and production bases easier. It also makes restoring from a snapshot easier.

  • You can find out about the existence of the automation right in the base itself. Not so with a third party service.

  • Thee are no extra fields or tables cluttering the base.

  • There is only one automation run no matter how many records are processed. A well written automation script can create/update/delete thousands of records. In Make, every time a module in a scenario performs an action, it counts as an operation, so updating a thousand records would take a thousand operations.

  • It is fast — a few seconds for most well written scripts. A free Make account has a 15 minute interval between scheduled scenarios. Paid plans still have a minute interval.

  • There are no additional fees or services (assuming you already have a Pro or Enterprise plan)

There are some down sides to scripting automations.

  • You must know JavaScript or have someone who knows JavaScript. I believe this is the main limiting factor for most people.
  • You must hardcode most table and field names.
  • Logic that reaches outside the base (into other bases or 3rd party services) is more complex and has security issues.
  • If you exceed your monthly allotment of automation runs, you cannot buy more.
  • You are limited to 50 named automations per base
4 Likes

@kuovonne

This is a fantastic list of pros & cons! :cowboy_hat_face: :clap:

Yes, for scheduled scenarios, the most frequent timing that you can get is once per minute. I actually don’t have any clients who need scheduled scenarios firing this rapidly. Most of my clients need hourly or daily or weekly scenarios.

The most commonly-desired request that I receive is to fire scenarios on demand. And the beauty of Make is that you can always instantaneously fire a scenario on demand by triggering a webhook for that scenario.

This can be done in 2 ways that I can think of:

(1) By clicking on a link in Airtable (via URL field, text field, or Button field)

or

(2) By triggering the URL through a script in Airtable’s Automations. Yes, this requires scripting, but the script would be as simple as the screenshot below:

Great list of cons, and yes, this one is the biggest con for me. If I came to Airtable with a Javascript background, I might feel differently about this.

I feel like this is a big con for my clients too, since I love teaching them how to fish so they don’t always need to rely on other people for their Airtable needs.

I can train someone in 5 minutes on how to use the basics of Make, and a mere mortal can look at that Make screenshot above and immediately know what it’s going to do. And with a few clicks of their mouse, they can figure out how to customize it further, even without any training from me.

But yes, there are definitely pros & cons to ALL approaches, no matter which road somebody decides to go down! :smiley: :red_car: :motorway:

2 Likes

Lol. When a user wants to run logic on demand, my favorite method is to use a button script. There are unlimited runs of button scripts, even in free workspaces. Button scripts also can have better error handling/reporting and guard clauses against accidental clicks. The main limitation is that the user clicking the button must have edit access, and some people don’t like the aesthetics of the right-sidebar opening. The result is still faster than either a automation script or running a webhook Make scenario.

In some ways this is the best of both worlds, but it is also the worst of both worlds. The logic for triggering the automation is in Airtable, but the logic for what actually happens is in Make.

I find that people who usually do not use code are quite willing to use code when there is no alternative and the code is simple enough for them to read. The issue is not code itself—the issue is using a system that they are not fluent with.

The learning curve for Make is definitely gentler than the learning curve for JavaScript.

Same here, except for me “fishing” is writing code versus creating a Make scenario. There seems to be an unlimited supply of people interested in learning both.

We are in complete agreement here!

1 Like

Since there were a number of such questions on a forum last days, and I tried to explain how to make this through 2 automations, but found that it’s easier to write script, I’m decided to share mine

it needs prepared data to write:
start with record IDs.

6601f75f774afa9f9bb2ec722f2cbcae288edfa0

then required values

image
image

set some divider between them, that must be not comma, but any symbol or group of them, that is not present in field values
The word ‘divider’ might be divider too, with or without brackets. It just must match exactly to written in code, line 14
image

Annotation 2022-05-23 211040

In code, change lines 1-3 and set divider (or let it be <divider>)
Name of fields in your table where records created, may not match fields in the table, where ‘Find records’ searches. Just follow their order. In my example,
[record ID, ‘Owner’, Number of flats’, ‘Building’] written to [‘Link to’, ‘Owner Notes’,‘Room Number’,‘Building’].
field types to write - text or multitext, anything where you can just put value
if you have some input/output data incompatibility, read API documentation about field formats and try to play with wrapID function.

//This is your input data 
const CREATE = base.getTable('Example_Table');
const LINK = 'Link to'
const FLDS=['Owner Notes','Room Number','Building']
const data = input.config().DATA // you should insert it on left side

//functions to process. For links, instead of 'Link to': id, used {'id':id} 
const allFlds=[LINK,...FLDS]
const wrapID=({[LINK]:id,...others})=>({[LINK]:([{'id':id}]),...others})
const transpose=x=>x[0].map((v,ix)=>(x.map((row,f)=>[allFlds[f],row[ix]])))
const create=row=>({fields:wrapID(Object.fromEntries(row))}) // ignore linter

//now process data and write
const vals=data.split('<divider>').map(v=>(v.split(', ')))
const crt=transpose(vals).map(create)
while (crt.length) await CREATE.createRecordsAsync(crt.splice(0,50))
2 Likes

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.