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

[Jeremy_Brunet]

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 :slightly_smiling_face:

[Jeremy_Oglesby]

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”.

[Jeremy_Brunet]

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! :thumbs_up:

[Taj_Deluria]

Hi I have a very similar use case. In my case, I run a farm with multiple plots that are assigned to different workers. I already have a table for each plot, as well as a table for each of my workers. I wanted a way to store the HISTORY of who worked each plot at any given time since they tend to be moved around a lot.

I can understand having the two tables (one detailing worker info, the other detailing plot info). So this solution is about making a third table that references a worker, the plot, and start and end dates. That sounds great. My question is how does the system determine which is the current plot assigned to the person? I’m thinking I’d like to create a column on the worker table that shows where he is currently assigned. I can’t quite wrap my head around how to pull that data out of the third (let’s call it ‘assignments’) table.

Anyway, thanks for the tip. I think this solution is 99% of what I need. I just need more skill in order to display the appropriate data. :slightly_smiling_face:

*T

[Jeremy_Oglesby]

Hi @Taj_Deluria

It sounds to me like you will want this 3rd table, as you mentioned, of “Assignments”. And it sounds like you know how to set that up with start dates and end dates. I would say that you could classify an “Active” assignment as one that does not yet have an “End Date” filled in. If you have filled in the “End Date” field, it would indicate that the worker is no longer on that particular assignment, and therefore not on the same plot anymore.

So you are concerned with pulling a worker’s “Current Plot” out of the “Assignments” table and into the “Workers” table. I’d suggest doing this by creating a formula field in the “Assignments” table (let’s call it “Current Plot?”) that checks for an “End Date”. If there is no “End Date”, it supplies the name of the “Plot” linked to that “Assignment” record.
It could look like this:

IF({End Date}, BLANK(), {Plot})

where {Plot} is the field that links to the “Plot” from the “Plots” table. So if you have filled in the “End Date”, this field will be blank, but if there is not an “End Date”, it will reflect the name of the Plot for this Assignment.

Now, in your “Workers” table, you should be able to create a Rollup field (let’s call it “Current Plots”) that looks at all of a Worker’s “Assignment” records, and ARRAY_JOIN(values) the “Current Plot?” field, to see a list of all Plots that Worker has active Assignments for.

[Taj_Deluria]

Sometimes in life, you ask a question. Invariably, someone nonchalantly chucks out an answer and you stand there dumbstruck because you realise that everyone has been lying to you. Everyone who has every told you that you were smart–your teachers, your parents, Aunt Flo–EVERYONE LIED.

Or the person giving the answer could just be really smart. I hope it’s the latter, because otherwise, I’m going to need to buy me some rubber pants. Can’t wait to try this. :slightly_smiling_face: THANKS!!

[Jeremy_Oglesby]

:joy: – it has very little to do with being “smart”, and a whole lot more to do with being saturated in the language and conventions of the environment. I’m a web developer, so I work in databases, and think about relationships between tables of data all day, every day. Start asking me questions about how to cultivate plants at scale… I’ll be the one dumbstruck.

[Taj_Deluria]

IT WORKS. One last kvetch. It returns the name of the current assignee, but that name is not a link to his record (unlike ordinary links to other records). Is there a way to make it display as a link?

FYI. I was a web developer / engineer for almost 20 years before I realised I was now too old and wanted to try something new. :slightly_smiling_face: Now I’ve been a farmer for about 10. And here I am again, on a web-based db trying to run things like how I used to run everything in my life. Hahahah. You gotta run home to momma sometimes. :slightly_smiling_face: