Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Nov 03, 2021 09:54 AM
Hi… I’m looking for a scripting solution that lets me auto-populate a field with a parent and child relationship.
Eg -
Column 1 has the children
Column 2 has the parent of the child
I’m wondering if I can write a script that can populate a new column with the tree hierarchy like this -
wco:WC Interface → wco:Area → wco:Block → wco:Image Card → wco:Hero Image Card
I have looked at a solution posted by joao_melo which works.
But i’m wondering if this is doable in a script?
Solved! Go to Solution.
Nov 03, 2021 10:52 AM
Here’s a link to the script. It uses the script settings features so you don’t have to mess with the code itself to set it up before running it.
Here’s a sample of the output (the {Label}
field):
You can pick any separator that you want, and also build labels based on hierarchy depth instead of the record name:
Nov 03, 2021 10:20 AM
Welcome back to the community, @Anirudh_Subramaniam! :grinning_face_with_big_eyes: This is definitely doable via a script. In fact, I wrote a script a while ago that does this very thing. I thought that I’d shared it in the community already, but I can’t find it via a search. It needs a few quick updates to bring it up to spec, so I’ll reply in a few minutes once it’s ready.
Nov 03, 2021 10:52 AM
Here’s a link to the script. It uses the script settings features so you don’t have to mess with the code itself to set it up before running it.
Here’s a sample of the output (the {Label}
field):
You can pick any separator that you want, and also build labels based on hierarchy depth instead of the record name:
Nov 03, 2021 11:10 AM
Thanks @Justin_Barrett. This looks amazing. I tried running it but I am getting the following error -
ERROR
Error: No field matching “Parent” found in table “Class”
at query.records.filter.record on line 63
at parse on line 62
at main on line 92
Nov 03, 2021 11:46 AM
@Justin_Barrett - Nevermind. I fixed it… the code had the “Parent” field had coded. I changed it to linkfield and it worked. Thank you so much!
Nov 03, 2021 01:48 PM
Sorry about that. I was in too much of a hurry and forgot about that one hard-coded field name. I changed it in the gist linked above, so anyone else grabbing it will be good to go.
Feb 12, 2024 04:33 AM
@Justin_Barrett This is fantastic and I've been using it for a few months as a Scripting Extension. Thank you!!
Just wondered, do you think it would work as an Automation, so it triggers just for a record that was modified, rather than running through the whole table? I've noticed for some other scripts, people sometimes provide two versions (a Button script and an Automation script)... but I'm not sure how difficult it is for experts like yourself to convert them between the two!
Thanks,
Ed
Feb 12, 2024 07:35 AM
@Ed_Goble1 Thanks! I'm glad that it's proving useful for you.
When it comes to converting a tool like this to run as an automation script, the conversion process is seldom the issue. There are usually other issues that come into play.
The main issue that I see here is your desire to have the script only run on the modified record instead of the whole table. The point of this script is to provide an accurate rendering of the relationships between all records in the table. When one record is changed, that change could affect other records as well, so an isolated change on a single record could easily lead to an inaccurate representation of the full series of relationships in the table. That's why running it on the full table is always preferable.
On the plus side, telling Airtable to update a field to a value that's already in that field results in no change, so the full table calculation really isn't an issue; e.g. if there are 1000 records to update but only 3 actually change, only those 3 will show changes in their record history. However, the script could be updated to ignore records where there's no change. An update of 1000 records still takes a lot of time to execute in groups of 50 (Airtable's max for batch updates). Whittling the collection down to only those records that must be updated would definitely speed things up.
Anyway, I can see the benefit of having an automation version of this script. I'll see what I can do about making that happen, as well as making the update process more efficient, though I can't promise anything in terms of timing.
Feb 12, 2024 08:34 AM
All makes sense. Amazing, well in case you do manage an Automation version at some point I'll be a keen user! Best.
Apr 28, 2024 02:56 PM
@Justin_Barrett That looks like a great solution. Is it possible to modify the script to get a counter that assigns a number to each task in the hierarchy based on the position of the task in the chain?
Thanks a lot!