Aug 01, 2022 03:25 PM
Hey,
I’m working on a project management base were each ‘project’ has a series of associated ‘tasks’ that are populated upon the creation of each project. We’re a civil engineering company so each ‘project’ has 178 associated tasks. I’ve created a standalone record for each step, and want to automate 178 new records to a third table and then link the newly made project to each of those records. I think scripts are the only way to do this. Does anyone have some advice? Thanks!
Aug 01, 2022 04:03 PM
Welcome to the community, @Samantha_Hebert!
Yeah, I can’t think of a way to do that natively with Airtable’s native automation actions, but maybe someone else knows of a way.
It seems like your 2 options would be:
(1) writing a JavaScript
or
(2) If you don’t want to write any code at all, you can use an external automation tool such as Make.com.
Aug 01, 2022 06:08 PM
Hi Samantha, I’ve put something together here that should do what you’re looking for with automations alone.
(I don’t recommend a script for this as it’ll be difficult to maintain if you don’t already know some JavaScript, but we can go down that road if you want to as well)
It works by pasting unique comma separated values into a linked field, forcing creation of multiple records
The setup above relies on the list of tasks being populated in the Task List
formula field, but it can be changed to use data from another table instead via a linked field
Let me know if you’ve got any questions!
Aug 02, 2022 02:36 PM
This is so helpful, just one thing. How are you generated the concatenated task name from project name and task list? I’m not seeing that field or formula. Thanks!
Aug 02, 2022 05:17 PM
Adam hid that field, but it’s very strange that Airtable isn’t showing you any of the buttons on the toolbar, so you’re unable to unhide that field.
If you duplicate the base into your own workspace, the entire toolbar will reappear in your own copy of the base, and you can unhide the field.
Aug 02, 2022 06:50 PM
Hi Samantha, that’s done via the field Text to Paste
, and its formula is attached below!
IF(
AND(Name, {Task List}),
Name & "|" &
SUBSTITUTE(
{Task List},
", ",
"," & Name & "|"
)
)
Aug 03, 2022 05:47 AM
This is fantastic, thank you both @Adam_TheTimeSavingCo and @ScottWorld!
Aug 03, 2022 06:53 AM
Hey @Adam_TheTimeSavingCo or @ScottWorld,
I’m experiencing a downstream issue that y’all might be able to help with. Generating the above CSVs is to then be pasted into a linked record field to ‘force’ create new records in the tasks field. I’m getting an error that ‘update record failed due to insufficient permissions’, do you have any way to mitigate this? Thanks!
Aug 03, 2022 08:22 AM
Hi @Samantha_Hebert, I’m not sure I understand. If you can create a Loom video of your problem, that would be helpful!
Aug 03, 2022 08:45 AM
Welcome to the Airtable community!
Scripts are not the only way to do this. It can be done with native Airtable automations or third party tools. However, I think a button script is the fastest, most reliable way to do this, and it wouldn’t use up automation runs.
I usually do this by having a [Projects] table and a linked [Tasks] table. Then I have a template project and its linked template tasks. The user clicks a button to create a new project based on the template, and the script duplicates the template project and the linked tasks.
I like keeping the templates in the same tables as the projects because there are fewer tables to clutter the base, and it makes maintenance easier if you need to add fields or change field types. Changes to field types require no changes to the script. Adding, removing, or renaming fields to be duplicated requires minimal code changes that people completely unfamiliar with code can still manage.
I don’t currently offer consulting services to new clients, but there are several other script writers who could write such a script. (@Adam_TheTimeSavingCo you should try building this setup if you haven’t already.)
Aug 03, 2022 07:10 PM
Hm, could you check the permissions for the linked field, as well as the table that you’re creating the records in? If nothing looks amiss there, DM me an invite link to your table and I’ll take a look for you
Aug 03, 2022 07:26 PM
Ahh that’s super interesting. Having the template project and linked tasks in the production table makes so much sense.
Sigh, I’ve got a few setups where there were multiple template projects where I ended up creating the tables Template Projects
and Template Tasks
for those, and I hit the maintenance complications that you mentioned
I’m going to go back and fix them up so that they’re set up the way yours are, thank you very much for sharing your solution
Yeah, definitely. Seems like a pretty interesting problem to solve
Nov 15, 2022 12:43 PM
Brilliant! thanks, the only solution I found so far. Should be a more elegant solution being part of the automation script.
Nov 25, 2022 09:46 AM
@kuovonne, @Adam_TheTimeSavingCo or anyone: do you know of a “New Project with Tasks from Template” sample script or something close to it available for purchase?
As someone with nominal coding skills/experience the lion’s share of what I employ I’ve learned from sample code so I’d like to try it myself that way before hiring someone to write it for me from scratch.
I have the same setup of separate Project and Task tables with templates in each. Another option I am going to explore is setting up a button in the Projects table combined with Kuovonne’s Prefilled Form extension that allows me to select specific tasks to create for that project from a list of template tasks.
I would assume that route would also run most efficiently from a script to create the new tasks vs an automation.
Nov 25, 2022 12:29 PM
I’m not personally aware of one. I have written many such scripts but I find that each one contains so much customization and I have not had the time to write a version that is generic enough to sell while still being easy enough for non-coders to customize.
Since you already have coding skills, I suggest you jump in with trying to write you own. The scripting documentation has a lot of code snippets.
This doesn’t quite sound right to me.
There are many ways to do things in code. It is possible to create tasks from a template using only automations and no scripting. However, I personally prefer scripting methods. Scripts take fewer automation runs, are generally faster, and require fewer helper fields.
Nov 26, 2022 10:01 PM
Not really I’m afraid. Here’s something somewhat similar that you may find useful?
Jan 16, 2023 02:59 AM
@TheTimeSavingCo I'd love to see an example of your reworked Projects / Tasks base!