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

[Ben_Crouch]

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!

[Andrew_MacNeill]

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!

[Hannah_Wiginton]

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

[Andrew_Fraser]

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.

[Ben_Crouch]

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

[TheTimeSavingCo]

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

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

[Andrew_Fraser]

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.

[TheTimeSavingCo]

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:

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?