Link to another record across multiple tables

[hallo_michael]

Hello community! 

We use airtable as an ATS (applicant tracking system) and now would like to extend it to project management.

I have 2 different tables: candidates and jobs. 

I now want to create a new task table (classical kanban project management) where I can link to either a record in the candidate OR in the jobs table. 

As the "link to another record" only lets me refer to one other table, I am wondering if there is a way to link to a record in +2 tables.

Any help is appreciated!

Michael

[Stephen_Orr1]

Hi Michael,

I would first start by deciding which of these two tables (candidates and jobs) closest resemble a working projects table that you would link your tasks to and ask if the other table is actually a subset of the first table.

If these two tables are very similar and the only difference is the stage of the work (project vs. potential project) then it makes the most sense to store these in the same table and use a flag field like "Application Status" to differentiate the two.

You can then build views to display candidates vs jobs and automations later on triggered by when a candidate becomes a job (or a job becomes a candidate). If you want to use Airtable for request intake, this structure lets you build a form in one table and then use the data submitted via form as project data without needing to duplicate it (bad practice) in your base.

Hope that is helpful and please let me know if you have any questions on any of this.

-Stephen

[hallo_michael]

Hey Stephen, thanks for the input and reply! 

The truth is that the reality is a little more complex even: as we are working as external consultants, there are more tables (besides candidate and jobs, also clients and contacts). There is some relation between each of these, but there is not a real master table. 

Each task in the new task table can (or should) be linked to a candidate OR or job OR a client OR a contact.

Examples would be:

- set up interview with Candidate X (which should just be a link to another table)

- send contract to Contact Y (which should just be a link to another table)

- Publish job Z (which should just be a link to another table)

I could create a link to another table for each of them (job, candidate, client, contact) but would like to have one field/column to keep it simple.

Hope that provides more insights into the problem!