Linking Multiple Tables


We have experimented with the ‘link’ field type but this only allows us to connect a single record at a time. Our data set is too complicated to keep in one table and manage using filters and grouping only.

Ideally we are wanting to create a base where multiple tables are connected to each other such that changes to one table are reflected in all the others.

We think of this is a 2 tier model:
1 - A single master list. All tasks and processes are recorded.
2 - Filtered copies of the master list: one table for each employee and process

As a simple example, the master list may contain 8 tasks. 4 relate to selling and 4 to buying. 2 tasks have been allocated to each of the 4 employees.

- Buying and selling processes should each have a table where tasks of the other process are filtered 
      out. Within this table, different views and grouping can be used to manage the tasks. 

- Each employee should have their own table where they filter out tasks given to other employees. 
       Different views and grouping can be used to manage the tasks. 

When someone is making changes to their table, e.g. adding or completing a task, this action should be reflected in all other tables (even if it is filtered out).