Are self-joins one-way relationships?

[WilliamPorter]

In Airtable, a normal one-to-many relationship between two tables is bi-directional. When I create a link-to-record field in the parent table, Airtable automatically creates a mirroring link-to-record field in the child table.

But if I create a link-to-record field that links to the same table – a self-join – that mirroring field does not get created. I was trying (as an experiment) to create a one-table to-do list, but I wanted a task to be able to have sub-tasks. So I defined the link-to-record field linking the Tasks table to itself, and called that field (that relationship) “Subtasks”. I created a task like “Prepare for vacation” and then added subtasks to it by clicking into the “Subtasks” column’s + button. And then

1. Prepare for vacation [Subtasks: Get gas in car, Pack, File vacation form…]
2. Get gas in car
3. Pack
4. File vacation form at post office
   etc.

That works fine.

But now what I’d like to do is filter out the subtasks and show only the parent-record tasks. Problem is, I can’t see a way to do it, since there does not seem to be a way to identify a child record when the parent is in the same table.

Make sense?

William

[kuovonne · Accepted solution]

Take a look at this script for creating Same Table Linked Records Backlinks.

[WilliamPorter]

Wow, that’s fantasic, @kuovonne. Thanks for the rapid response. I’ll check that script out. Will be a great way for me to get started scripting Airtable!

William

[Justin_Barrett]

Another option is to reverse the process: instead of having a parent task link to all of its subtasks, you could have each subtask link back to its parent task. In that case, subtasks could be hidden by creating a filter to only show records where the {Parent Task} link field is empty.

[WilliamPorter]

Thanks, @Justin_Barrett. Interesting idea and I just tried it. Unfortunately, it has the same problem I reported in my original post: it just has the problem in reverse. And the reverse version of the problem may be worse.

If the link goes from parent to child, as I was doing it originally:

• CON: there’s no way to hide the children (because there’s no way to identify which records have a parent).
• PRO: But at least, in a parent record, I can click into the link-to-record field and add children there; and
• PRO: I can see for each parent record that it HAS CHILDREN.

If the link goes, as you suggest, from child to parent, then

• PRO: I can hide the children.
• CON: But now there’s now no way for me to see a parent record’s children – no way to see a primary task’s subtasks.

It’s a good suggestion and might have value in a different situation where one is doing a self-join. But at the moment I’m keeping my subtasks in a separate table while I work my way through @kuovonne’s javascript solution.

Thanks for responding!

William

[kuovonne]

WilliamPorterTech:

I work my way through @kuovonne’s javascript solution

Just to clarify, it’s not my script. It was written by @openside.

You should be able to use the script immediately by copying and pasting it into a new Scripting Block script. When you run the script it will ask you for the table and fields that you want to use.

Of course, test the script out on a copy of your database fist. Once you are comfortable with running the script, you can edit the CONFIGURATION section with the names of your table, view, and fields so that you don’t have to manually pick them every time.

[WilliamPorter]

@kuovonne,

Thank you for the correction. Actually I knew that. I thought of you because it was you who pointed me to that script from @openside. Thanks to @openside, as well.

William

[Justin_Barrett]

WilliamPorterTech:

If the link goes, as you suggest, from child to parent, then

• PRO: I can hide the children.
• CON: But now there’s now no way for me to see a parent record’s children – no way to see a primary task’s subtasks.

I enjoy being told something can’t be done, because it encourages me to find a way to do it. :slightly_smiling_face: In this case, it’s definitely possible. Granted, this only works when your parent-child tasks only go a single layer deep—i.e. you can’t have a single task be both a parent and a child—but if a single-level hierarchy is what you’ve got, this will work.

Here’s my starting point. I’ve got two parent tasks, {Task 1} and {Task 3}, with children scattered at various places in the list.

I added a formula field named {Task Group} using this formula:

IF({Parent Task}, {Parent Task}, Name)

If any task has a parent, its group will be that parent. If a task has no parent, that means that it’s a parent of other tasks, so its own name should be the group name.

Now you can make a view that groups by the {Task Group} field, and you end up with this (trimmed for brevity):

In this case, all of my child tasks were added below the parent task, which meant that the parent task would automatically appear at the top of each group. However, I also added a sort to the {Parent Task} field, which will take care of any admittedly-rare cases where a child appears in the record list before its parent. With that done, you can hide the {Task Group} field to keep things clear.

Will that work for your needs?

[WilliamPorter]

Justin_Barrett:

I enjoy being told something can’t be done, because it encourages me to find a way to do it. :slightly_smiling_face: In this case, it’s definitely possible. Granted, this only works when your parent-child tasks only go a single layer deep—i.e. you can’t have a single task be both a parent and a child—but if a single-level hierarchy is what you’ve got, this will work.

@Justin_Barrett,

Thanks for sticking with me and for taking up the challenge! This is a clever approach and you have taught me a good trick here that I will surely use. I am learning myself that I have to stop saying to myself, “Oh, well, Airtable just can’t do this” because I’ve now been wrong again and again.

I may be wrong again – I’d be tickled if you come back and take your suggestions one step further – but that said, I’m afraid this improved suggestion still doesn’t quite do what I’m trying to do. At least, it’s less than ideal.

As I said, I would really like to be able to do two things:

1. Hide the subtask records, yet
2. See the subtasks

Your last suggestion would allow me to do #1 (hide the subtasks) because your clever formula does allow me to identify which tasks are children and which parents. Alas, unless I’m missing something, your suggestion still does NOT allow me to do #2 (see the subtasks). I want to eat my cake and have it too, and at moment it still looks like I can’t.

What I want to see is just the first row below (well, that, and the fifth row about calling Ozzy, which is a parent task that has no children).

Here I can see the subtasks in the linked field. And where there are subtasks as with row 1, I can of course identify that rec as a parent. But

1. I do not see a way to identify recs 2, 3 and 4 as children, at least not automatically
2. I also do not see a way to identify rec 5 (“call Ozzy about lawn”) as a parent, because it doesn’t have any children.

Still this is a very instructive thread for me thanks both to you and to @kuovonne. Thanks again.

William

[Justin_Barrett]

WilliamPorterTech:

Your last suggestion would allow me to do #1 (hide the subtasks) because your clever formula does allow me to identify which tasks are children and which parents. Alas, unless I’m missing something, your suggestion still does NOT allow me to do #2 (see the subtasks). I want to eat my cake and have it too, and at moment it still looks like I can’t.

Unless I’m misunderstanding something about what you want to do, my solution above gives you both. The top task in each group is the parent task, and the tasks below it are its subtasks. Here’s the same setup using the task names from your example:

As I recommended initially, this has each subtask linked to its parent task, whereas your screenshot has the parent task linked to all subtasks.

If you really want to, you could use a similar formula:

IF({Parent Task}, "Subtask", "Parent Task")

To identify the type of each task:

From there you could make a different view that only shows parent tasks (if desired) by using a filter on the {Task Type} field to only show those records marked “Parent Task.”