How would you store the employment history of hundreds of people in Airtable?

Hello community! :wave:

I’m working on a project for a HR agency. They have a list of people looking for a job or currently employed. For each of them, they track their employment history as they change frequently positions and/or companies.

Listing these people in Airtable is straightforward, consider this part done.
But for the individual employment history, I have some doubts about how to tackle this.

For each past job, I must quote the start and end date, the company name & location, the salary rate, etc.

Now, they’re filling up an Excel sheets and adds new columns at the end of it when it’s necessary to add one more past job experience. The position currently occupied by the person is visible in the first columns and when it’s over, they copy/paste the job infos in columns somewhere near the end of their sheet.

In short, the sheet’s columns looks like this:

A: Reference (Concatenation of the first & last name)
B: First Name
C: Last Name
D: Current Position
E: Current Company
F: Start Date
G: End Date

W: Last Position
X: Last Company
Y: Last Start Date
Z: Last End Date

AW: Second-to-last Position
AX: Second-to-last Company
AY: Second-to-last Start Date
AZ: Second-to-last…

Of course, I won’t do that in Airtable but I’m not sure if storing the employment history in a linked table will be handy too (How to move the current position to the employment history when the person is done with the job?)

If any of you have an idea, I’m all ears :slight_smile:

1 Like

This seems like a straightforward 2-table solution should work… unless I am misunderstanding something.

You have your “People” table with a record for each person - the easy part.

Then you have a “Positions” table with a record for each position a person has held - this table would have columns for “Position Title”, “Start Date”, “End Date”, “Company” (probably a third table for these), and any other information needed. These are linked to “People” records such that a “Person” can have many “Positions”, but a “Position” is linked only to one person. A person’s current position would be signified by the absence of an “End Date”.

Hi @Jeremy_Oglesby

Thanks for your input, it looks like it’s the only way to deal with it.

What I’ll try to do is to add a “Created Time” column to detect the latest job (and normally the current one) of the person in the “Positions” table. Can work with a “Checkbox” column too but it will require an action from the table user.

In the “People” table, I’ll find a way to play with views & filters to see the current job position appear in the first columns and all the others in the linked column to the “Positions” table. The goal is to change dynamically the jobs history when something happens in “Positions”.

I think I had a hard time tackling this because I wanted to store everything in one table and play with views. I’m doing this for the most part of this project, I wanted to ship only one table that can store it all but it looks like this job history thing can’t fit my plan.

Thanks again for your post! :+1:

1 Like