May 26, 2022 09:12 AM
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!
May 26, 2022 09:52 AM
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!
May 26, 2022 05:09 PM
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.
May 30, 2022 07:46 AM
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
May 30, 2022 07:51 AM
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
May 31, 2022 10:24 PM
With that many, it would probably be better to use scripting.
Here are some starter scripts you can look at for what you need:
Jun 10, 2022 06:39 AM
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.
Jun 10, 2022 06:48 AM
@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:
Jun 10, 2022 06:50 AM
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.
Jun 10, 2022 07:18 AM
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.
Jun 10, 2022 07:20 AM
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.
Jun 13, 2022 11:58 AM
Seems Scott was able to help! Hope you’ve figured it out!
Nov 21, 2022 02:41 AM
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.
Nov 21, 2022 02:59 AM
We’re pretty sure the answer lies in scripts but haven’t got to the point of doing that yet. will keep you posted
Nov 21, 2022 05:54 AM
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
Nov 22, 2022 05:23 PM
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.
Nov 22, 2022 08:48 PM
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?