Show Linked Fields in multiple tables

[robflate]

I’m trying to accomplish a task manager for a home renovation. The hierarchical layout of tasks is;

Category > Room > Task > Subtask

Each of the above is a table.

• Category: Has 1 or many Rooms.
• Room: Belongs to 1 or many Categories. Has 1 or many tasks.
• Task: Belongs to 1 Room. Has 1 or many subtasks.
• Subtask: Belongs to 1 task

Examples;

Category  > Room     > Task      > Subtask

Plumbing  > Kitchen  > New pipes > Remove floorboards
                                 > Buy new pipes
                                 > Fit pipes
                                 > Fill with water

Plumbing  > Kitchen  > Walls     > Strip old wallpaper
                                 > Buy wall paint
                                 > paint walls
                               
Electrics > Bathroom > Lights    > Remove old light
                                 > Buy light fitting
                                 > Fit new light

I have this working using the Subtask table to enter all the data, with linked fields adding data to the other tables. The Subtask table has linked fields for Task, Room and Category.

My question is this. How do I display what room a task belongs to in the Task table? Or how can I display what Categories a Room belongs to in the Room table? These items are already Linked from the Subtask table so I’m not sure how I accomplish this.

From reading the forum, is the answer Indirect Linking? I saw this post but couldn’t work out if it would solve my issue. How to link linked columns

On the GitHub repo I also saw mention of a Lookup Column but again couldn’t work out if it would solve my issue. https://github.com/seatable/seatable/issues/16#issuecomment-598713645

Thanks.

Images for clarity;

[rdb]

In order to pull in data from other tables, use a formula column.

The syntax is very simple: {A.B} with A being the column name of the link column in the present table and B being the name of the column from which you want to pull the data.

And the formula can be extended at will! {A.B.C} is just as possible as {A.B.C.D}. The last element is always the column from which you wish to extract the data. The other elements describe the path to the table in which the destination column resides.

Now, let’s apply this to your case: Add a formula column in the task table. The formula shoud be {Subtask.Room.Name}

Can you try this out?

[robflate]

Thanks! I must have tried a million ways but not that.

The only issue is that it returns multiple Rooms that are the same. Probably 1 for each time the room is added to a Task. Is it possible to just show 1?

[rdb]

Glad to hear that it worked. (Could you please mark this thread as solved.)

The repetition of the room name in the linked column is a consequence of your data structure. SeaTable returns the room name once for every subtask in a task. Look at Lounge Floor: It has four subtasks and the room name is shown as many times.

You can get rid of the repetition, but not by changing a setting. You’d have to change your data structure. Unfortunately, at half past midnight, my brain only doesn’t cook up the right solution.

But I also have a question for you: I thought I knew all of SeaTable’s features, but apparently I do not! What is this??

[robflate]

I was never the best with data structures. As soon as things get nested a few times and data thrown around different places, my brain gets confused.

The dots aren’t really a SeaTable feature but rather me using emoji in a function;

Thanks again for all the help. I really appreciate it.

[rdb]

You are very welcome!

What you did with the emojis is cool. I had no idea this was supported.

[robflate]

No worries if not but have you any clues on how I might change the data structure to prevent the repeated rooms whilst maintaining the hierarchy/concept? I’ve spent a few hours trying various things without finding a solution. I appreciate this isn’t strictly a SeaTable issue so please ignore this if it’s an unsuitable request. Thanks.