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?
Best answer by Justin_Barrett
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.
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:
+20Welcome 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.
+20Welcome 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.
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:
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:
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
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
@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!
+20@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!
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.
+4@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
+20@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
@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.
+4@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.
All makes sense. Amazing, well in case you do manage an Automation version at some point I'll be a keen user! Best.
+5@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!
+4@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!
If you mean the “level” eg 1 for a top level, 2 for a child, 3 for a grandchild etc - then one option I use is just taking the path field from the script above, and using a basic formula which does a count on the separator? Eg “—>” or “.” in the examples above. Then you just have one script to run.
+5If you mean the “level” eg 1 for a top level, 2 for a child, 3 for a grandchild etc - then one option I use is just taking the path field from the script above, and using a basic formula which does a count on the separator? Eg “—>” or “.” in the examples above. Then you just have one script to run.
Thanks a lot. I was able to do it using that approach!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
