I was wondering if anyone has come across a situation where they need to relate two (or more) items of the same type to each other. For example, you have a list of actions/tasks and you want to link related actions together so you are able to jump from one action to the other easily.
When I setup many-to-many relations within the same type, it actually generates two relation fields. I think this makes sense from a data structure point of view but it is really confusing from a UI view as there are now two collections for related items.
I am wondering if I’m missing something or if this is the best that can be done.
An example use case for many-to-many self-relation where two distinct fields are needed (one for each direction of the relationship) would be tasks linking to other tasks as ‘Blocked by’ and ‘Blocking’.
There are use cases where a single field self-relation might be preferable, but this is not supported, and can often be achieved in other ways.
You might want to read this discussion for some of the previous thinking around this topic:
This is a good point. I’ve achieved this by putting in place an automation that creates the reciprocal relation to make sure that both entities have a relation in both fields. That way I can at least show only one of the fields in table view. Not the neatest solution but it works!
Another way to solve it (I think it has been discussed elsewhere) is to create a database, called perhaps ‘Task groups’.
Then create a many-to-one relation to groups. Every time you want to link tasks together, you add them to a group. The group database doesn’t need to have any fields itself (apart from the relationship to tasks) and the name of a group could be derived using a formula that aggregates some useful info from the tasks that are members, e.g. ID or Name.
You can then create a lookup in the tasks database to show all tasks in that group.
Out of curiosity though, I wonder what the intention/meaning is with relating the tasks together?
If they are tasks to be carried out one after the other, perhaps you would be better served by creating a one-to-one relation with fields to represent ‘next task’ and ‘previous task’.
In this case, i had a list of actions that were not necessarily sequential. However, I needed to indicate if they were just related to 1 or more other actions (either similar in nature or archiving the same thing).
I also thought that using a rich text area with a series of inline references was also an option but you would need to maintain these on both sides.