Are self-joins one-way relationships?

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

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.

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

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.

@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

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?

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

@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

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.”

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

@Justin_Barrett,

Thanks for staying with me. Yes, your method does allow for the subtask records to be visible. It also allows subtask records to be hidden. And I did see that when I read your last response and tested it out for myself.

What your solution does not allow, however, is for subtask records to be hidden while the subtasks simply as bits of info remain visible. At the same time. Yes, I know I’m being greedy. :slightly_smiling_face: But for this particular base–the task manager that more or less rules my life–I want maximum flexibility and efficiency. I want to be able to create different views, different filters, and different sorts, and always have the subtasks inextricably attached to their parent. I want to see as many rows of parent tasks as I can without having the screen filled up with rows of subtasks; and I’d prefer not to have to click on disclosure triangles constantly to show or hide the subtasks.

I have everything almost as I want right now with the subtasks in another table:

I wish I could format the subtasks so that I could tell which ones had been completed and which had not. If I adopted the approach you’ve shared with me, I could do that and that would be valuable. It would compromise the efficiency and flexibility of the base in other respects, but it would have some advantages. And for that reason, I will continue to think about. So thanks again!
.

But to recap: This thread arose not because I was very unhappy with the solution I already had found (subtasks in a separate table, as shown in screenshot). I started the thread because I was interested in the question I put into the thread title: Are self-joins one-way relationships? And it’s pretty clear that the answer is yes.

Cheers,

William

If you’re using separate tables, you can do that, though not directly on the subtask links themselves unless you were willing to do some further tweaking. I don’t have time to do a screenshot breakdown, but here’s the basic process in my mind:

In your [Subtasks] table, make a formula field that checks the completed status of the task, and echoes its name with a preceding emoji to indicate its state, like this:

:white_check_mark: What about tripods?
:x: Sell the Pany LX100
…etc

Roll up that field into your main task table, using ARRAYJOIN() in the rollup aggregation formula to add line breaks between each item:

ARRAYJOIN(values, "\n")

That will keep each subtask on its own line in the rollup field in your primary task table, and you can easily see the status.

Another option is to type the subtask name into a secondary field (i.e. not the primary), and have this emoji-adding formula in the primary field, which would avoid the need to add a rollup, as the link bubbles would update to show the completed state of each task automatically. The downside is that you’re typing your subtask names into a secondary field, but depending on the situation that may be acceptable. It’s up to you, of course.

On a different note, I might argue from a conceptual standpoint that your parent tasks aren’t really tasks, but more like projects. For example, “sell cameras and computers” isn’t a single thing to check off, but a descriptor for a collection of true tasks that can be checked off one by one. I only say this because I have my own task tracking base (which I’ll be sharing a little later on; stay tuned for more) where I have nothing but individual tasks, using a grouping structure (sort of equivalent to your {Category} field, but more deep) to organize related tasks. I’ve thought about a subtask idea now and then, but the way my mind likes to think about this stuff, I haven’t put much effort into it. In my setup, the nearest equivalent might be tying a series of tasks to a related weekly goal, but my use of goals as a tracking/organization method is still in the early stages, and not very refined. Anyway, now I’m just mumbling, so I’ll stop. :slightly_smiling_face:

ARRAYJOIN(values, "\n")

@Justin_Barrett:

You may be mumbling–but I’m paying attention! This is a very interesting digression.
.

The conceptual thing

Your last point first (“from a conceptual standpoint”): Yes, I have thought about this myself, that the parent records are kind of like a grouping value more than a task. I thought about it and rejected the thought because it’s not right, at least not for my tasks. Not all tasks (parents) have subtasks (children). If I slide my terms one step to the left, so

Tasks becomes Groups
Subtasks becomes Tasks

then presumably I’ll be working in what is now the Tasks table (formerly known as ‘Subtasks’). What do I do there if a task has no, um, siblings with which it needs to be grouped? If I want to have a view that shows groups + tasks, every task has to have a parent record in groups. And now I’m required to create 2 records for every task. Now, in my invoicing base, it’s a fixed business rule that I can’t record activity in the Activity table unless the activity can be assigned to a Project, and I can’t have a project unless it belongs to a Client. But tasks are simpler, and most of the tasks I enter don’t have children (subtasks).

.

Rollups, emojis, arrays–oh my!

Anyway, that’s just a personal disagreement and not a big deal. I’m very grateful to you for the other idea. I’ve tried it and it works very nicely:

I hadn’t thought of using an emoji in a formula field (just not something I’d ever do in FileMaker, not sure why). And I’ve never used the ARRAYJOIN() function before, either. Very nifty.

Of course, it’s inevitable that every advance in technology brings with it a new problem, too, at no extra charge. :slightly_smiling_face: The problem here is, I can’t click into the rollup field, hit enter, and add a new subtask. So adding a new subtask either requires (a) adding the primary Subtasks_ column to the Tasks table to sit alongside the rollup field, or (b) switching to a different view to add subtasks. Neither is optimal.

That said, I might make this work anyway. Here’s the compromise that I’ve come up with in my test base. The link-to-record field (that links to Subtasks) sits right next to the rollup field that shows subtasks + status icon. But I’ve made the link-to-record column very narrow and renamed it “+ST*” (which to me means “add an optional subtask”). Okay, it’s not pretty. But now I can click on that narrow column to add subtasks; I can view the first few subtasks in a nice list in the adjacent column, and if I click on that adjacent column I can very nicely see everything.

The yellow highlighting shows the two subtasks that couldn’t be displayed in the row at its max display height. And if I need to edit a subtask (mainly that means marking it done) I click in or tab to the +ST* column, type shift-space to expand and I get a nice listing of the subtasks that lets me open one and mark it done.

.

You’ve been most generous with your time and these are terrific techniques to learn. Thank you so much!

William

The lack of single table Parent/Child Linked Field features is (at least in my opinion) one of the biggest technical limitations of Airtable.

I've personally used scripts/automations to maintain relationships, but it's an ugly workaround. With the recent work being placed into Airtable Grid View, I hope that they introduce this as a feature soon. Especially considering that there has been development into concepts associated around parent/child fields, such as List Views, and the Linked Field "Configure Date Dependencies" feature.