Best way to structure employees and tasks

I did not quite think through the entire project myself, but I can offer a tentative answer your basic question. I am 83% confident that you’ll want to put employees and onboarding tasks into separate tables. That “83%” is a rough guess. Let’s say 83% ± 3. :slightly_smiling_face:

Avoid super-wide tables!

I can tell you with a higher degree of confidence that, in the data-modeling game, if you’re starting to think of creating a table that’s go 60 different columns/fields that are all basically connected items, you’re almost certainly doing it wrong. When the number of related columns is small — say, Phone1, Phone2, Phone3 — the case can be argued either way. But for one thing, think of the UI issues that having 60 columns each representing a different task represents!

Multiselect? Maybe, but probably not

I suspect that a single column of type Multi-Select might make more sense, if you were determined to keep things in a single table. I haven’t tried this. I’m not sure that it’s POSSIBLE to create a Multi-Select field with 60 options. But just thinking about it, I can see a problem with this approach: Those selection descriptions are going to have to be pretty darned succinct.

Many-to-many!

When I’ve done things like this in the past, I’ve generally used a many-to-many design: PERSONS on the left (so to speak), FIXEDOPTIONS over on the right, and in the middle, the join table “PERSON-OPTIONS”.

The advantage of the many-to-many approach is that it gives you a lot of flexibility. For example, for each task linked to a single employee, you can have a field marked “Progress” or “DateDue” or whatever. Say you put in a date due field. You can then look at a calendar in Airtable and see what specific tasks are due today, tomorrow etc. and for what new employees.

The problem with many-to-many relationships in Airtable is that they raise awkward UI issues. Not impossible, just awkward, because if you stay in Airtable (don’t use a front-end builder, don’t write scripts to help you here) a lot of the linking will have to be done manually.

What it looks like in real life

But actually in this scenario this might not be too hard. Sounds like you know that every new employee needs to have these 60 things done. So you can, for each new employee, do the following (by hand):

1. Copy the value in the primary field for all 60 records in the OnboardingTasks table
2. Copy the value of the primary field for the new employee (probably employee’s name)
3. Go into the join table (EmployeeOnboarding?) and go to a view that isn’t filtered so you can create new records by pasting.
4. Paste the sixty values from the OnboardingTasks table to create 60 new join records
5. Select the EmployeeName (or whatever the link column is called) of all 60 records and paste the employee name.

(The steps above assume that you’re using Windows Clipboard history or some similar utility. Otherwise just add a step in there: copy, paste, copy something else, go back and paste that.)

You’ve now created your sixty onboarding task records for new employee Elmer Fudd. Once this is done, you

• go to the join table, filter for Elmer, and see exactly how things are proceeding with his onboarding
• make notes when appropriate about each onboarding task that are specific to Elmer
• mark the date each task is concluded
• use date due fields to plan upcoming onboarding efforts
• assign tasks to different people

and so on. And over in Employees, you can look at Elmer Fudd’s record and have a rollup fields that shows completed tasks, tasks due, etc.

Rollups vs Views

Of course rollup fields are not very nice to look at and it is not easy in Airtable to jump from Elmer’s record in Employees over to a view in EmployeeOnboard (the join table) to view just his 60 tasks. This is where front-end builders like Stacker or Softr can help. Maybe can be done with JavaScript but you have to be careful not to run afoul of the problem that in Airtable record sets are global. So (depending on how many new employees the company is onboarding in an average week or month) you might just keep half a dozen or so views over in EmployeeOnboarding that are filtered and named for specific employees. When Elmer’s onboarding is completed, you can delete or repurpose the view for him and assign it to Daisy Duck or some other newer employee.

Response to WP_Rucksack

Thank you so much for that super-detailed response. That was really helpful. I apologize for not responding sooner. Turns out I had my email notifications off for the Community forum, so I’ve now fixed that.

I agree with everything you said. I knew intuitively that keeping Employees and Onboarding tasks in separate tables was the way to go and you’ve just confirmed that and given me a clear picture of how to set up the junction table.

So I now have tables “Employees”, “Onboarding tasks”, and “Employee Onboarding” – the third one being the junction table.

Also, although obvious, I didn’t realize until your response (so thank you once again), that the junction table would also allow me to add all the meta info like due date, completion date, last follow up, or whatever to the junction table ‘Employee Onboarding’ for each record (d’uh).

You’re right in that adding records to the junction table makes it awkward and I like your approach of copying the primary key from employees to the junction table and then copying the 60 onboarding tasks against each of that employee’s rows. I’m thinking of taking this one step further and just setting up an automation for this. So as soon as a new employee is added to the Employee table, (1) Make a linked reference entry in the junction table, and then (2) against that newly created linked reference in the junction table, enter the 60 onboarding tasks, one for each of those new employee linked reference rows. This may be tricky to do using a single Airtable automation and may not even be possible given that it involves looping and iterating – there’s no way I’m creating 60 native Airtable automations – I don’t even think that’s possible. Alternatively, I could set up an loop/iterator using Integromat to generate new employee-onboarding tasks entries for each new employee added.

With Respect to Rollups/Views thank you for the great insight. Very fair point and now I’m giving some serious thought to slapping on a front-end like Stacker/Softr (I’ll have to learn these first), but seems like it eases out the awkward UI scenarios and can also circumvent certain Airtable limitations.

Thanks a bunch for the deep insights. Really appreciated it.