Guidance for my formulas using Airtable as an ATS

[Claire1]

Hello,

I am very new to Airtable, and would love some advice or guidance bringing a few ideas to life. I am using Airtable for an applicant/candidate tracking system, interview scheduling, and job tracking for a recruitment firm.

The current set-up is that we have a master database table that houses any and all candidates we've ever spoken to for any role, past or present, like a CRM. In the master database, I have a "Considered for" column, which shows which role(s) this individual has or is being considered for. Then we have a table for each active role.

1. I want to create a formula/linked workflow so that if an applicant's "considered for" column is the same as the name of the open role of another table, they are automatically added to that table. Example: We are opening an Account Executive role and create a new table for it. If an applicant's "Considered for" column contains "sales" OR "account executive," or their skillset keywords include "sales," then they are automatically imported into the Account Executive interviewing table.

2. I want to confirm that any updates that are made on the applicant's information on the role-specific table are also reflected in the master database. A few examples:
*If a candidate is added to the Account Executive table with no interview columns filled out, they are updated in the master database as an "uncontacted lead" (status column)
*If a candidate in the Account Executive table has information added to their interview columns (for example, the phone screen interview column has a date added), their status in the master database is updated to "active candidate"
*If a candidate is interviewing for the Account Executive role but they are marked as rejected on the AE table after being interviewed, their status in the master database is updated to "previous candidate."

I am looking through a bunch of tutorials to learn how to do this, but figured I would post here in tandem as I'm learning in case anyone is able to help me quickly create some type of formula template for this. Thank you so much!

[Alexey_Gusev]

Hello,

I would rather recommend a tutorial "don't do this":  https://support.airtable.com/docs/using-views-to-section-out-related-data-in-airtable

Of course, 'not recommended' doesn't mean 'not allowed', If you follow the way you described, with further development of your base, you will find it harder to maintain such configuration. Similar entities should be listed in the same table. It's not about number of tables, rather to make clear relations between these lists. And if you need totals, especially grouping, for example by departments, make a list of departments, link to master table , and do lookups and rollups.
Key to understanding basics of Airtable base design - linked records, how they created, and how it works.

Of course, I can have wrong vision of your data, so you should better read and decide.