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

[WilliamPorter]

@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

[Justin_Barrett]

WilliamPorterTech:

I wish I could format the subtasks so that I could tell which ones had been completed and which had not.

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:

[WilliamPorter]

@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