Help

Re: Create multiple records in one table after adding a new record in another

5670 1
cancel
Showing results for 
Search instead for 
Did you mean: 
Ben_Crouch
5 - Automation Enthusiast
5 - Automation Enthusiast

I have a table with services
I have a table with the tasks associated with (linked to) services
I have a table for my sales.
I have a table for my deliverables

When I add a new record in my sales table and I pick the service that was associated with that sale, I also want it to lookup the tasks associated with that service, then create a separate record for each of the tasks that need to be delivered, in my deliverables table.

Any suggestions welcome please!

16 Replies 16

Hey @Ben_Crouch!

This should actually be a pretty straightforward solution!
I would recommend that you leverage an automation to accomplish this.

You’ll configure the trigger to fire when a new record is created in your Sales table.
At that point, you’ll want to set a few conditional layers to your automation configuration.

To keep it simple, you’ll want to create a conditional run path for each possible task that can be assigned to a given new service.
Then just map whatever tasks/fields you want to update!

Hey Ben,

Here’s a start for you on how to do this in automations. You just need to add your lookup/rollup fields in combination with this method.

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable

Hi Ben - neat suggestion but I don’t think it scales. I have over 100 services and each service has a unique set of tasks associated with it. I have a table which has the service and the tasks linked to it - but I can’t work out how to reference this in automations, especially in a way that creates multiple records each time based on the tasks associated

Hi Hannah - I really appreciate the help, however I can’t make the link between the automation steps you’ve outlined and how to leverage those with my table of tasks associated with services, in order that the right new records are created each time a new service is added.

Each service can have multiple tasks associated with it. I’m missing the automation steps which effectively says to Airtable “keep creating new records, one for each task associated until you’re done”

to be clear, each service (I have about 100) has a unique set of tasks associated with completing it

Hey Ben,

With that many, it would probably be better to use scripting.

Here are some starter scripts you can look at for what you need:

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable
Andrew_MacNeill
6 - Interface Innovator
6 - Interface Innovator

Hannah, can I jump in to ask the follow up on this?

I have a record in one table which links to a series of tasks (that would specific to that record) and I want to add them all in. So this is similar to the above. The samples provided in the app are really simply linking the tasks themselves to the project but not really tracking the “completion” of the individual tasks.

With Automations, I can’t really have it add a bunch of new records from a View based on a standard list and link it to the new record. If I wanted to use Automations, I would have to do each record individually.

As a result, it would be better to use scripting.

Do I have that right? I was hoping to use Automation but got stuck.

@Andrew_MacNeill No, you can use automations to link a found set of records all at once to a new record. I discuss doing that (in the context of email sending) in this video podcast:

I understand I can link them to existing records but the scenario here is:

I have a table with a list of “standard” tasks. (Call it Task Types)
I have a table for Projects and Tasks. Tasks links to both Projects and Task Types but includes start/end dates.
I start a new project.
I want to create a new set of Tasks based on my standard tasks.

Is this the same scenario you cover?

The only way it seems I can do it in Automation would be to add a bunch of unlinked records into the Tasks table and THEN link those unlinked records to my new project.

You are correct. Since you want to create duplicate tasks across different projects, that’s the only way to do it. Otherwise, the same task would be attached to multiple projects.

You could still use my approach in the video to get what you’re looking for, but it is a workaround and adds extra clutter to your base.

If you want no additional clutter, you would want to turn to scripting, or you could use a no-code tool that doesn’t require any scripting like Make.com.

Thanks, Scott. I hadn’t played with Make.com before and may do it as I’m looking at this as a solution for someone who isn’t too technical.

But I will take the scripting approach to start with.

MUCH appreciated!

Seems Scott was able to help! Hope you’ve figured it out!

______________________________________
Hannah - On2Air.com - Automated Backups for Airtable

Hi. Did you ever work this out? I have a very similar scenario and have been going round in circles trying to get this to work. Would love to know what your solution was. Thanks.

We’re pretty sure the answer lies in scripts but haven’t got to the point of doing that yet. will keep you posted

Hi @Andrew_Fraser, I’ve put something together here that might interest you

create records

To view the setup (formulas, automations etc), 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.

The idea is to create a list of unique, comma separated task names, and use an automation to paste it into a field linked to the Deliverables table. This forces the creation of a record per unique comma separated task name; if there’s an existing task name in the Deliverables table it’ll just link instead

To create the uniqueness for the example, I used a combination of an autonumber field, a sale date and the service name, but this can be customized based on your needs

Hi @Adam_TheTimeSavingCo . thank you for sending that through. Really great to see how this works from a non scripting workflow. I can see it working really well. The only issue I am having is that I have another layer of tables to include. You can see my test base here.

In my example I also require a Project table and Project Templates table.

The Projects Templates table has Name, Video Templates (Services) (Linked).

The Project table has Name, Date, Project Templates (Linked), Videos (Sales) (Linked)

The idea is that a client would create a New Project - Enter Name, Select Date, Select Project Template - after which automation/script would create the the required number of Videos (sales) based on the Video Templates (services) within the Project Template selected. eg. “Video 1 - Video Template 1” and “Video 2 - Video Template 2”. This I have achieved thanks to using your formula and automations in the Projects table. However, I am stuck when trying to map the Video Template to the newly created Video (Sales) which would then allow for the formula/automation in the Video (Sales) table to start, filling in the Deliverables.

What do you think? It may all be a bit too convoluted?? Or is there a way. I tried this scripting solution, which worked very well, but again I couldn’t link back to the project.

Ah, I think I see what you mean

What if we try creating a new formula field in the Video (Sales) table called Extracted Video Template or some such, with the following formula:

SUBSTITUTE(
  Name,
  Project & " - ",
  ""
)

Which should give you this:

Screenshot 2022-11-23 at 12.46.47 PM

You could then have another automation that would trigger when Extracted Video Template gets updated or something, and its action would be to paste the value in that field into the Templates linked field, which should do what you’re looking for I think?