Formula Help in linking sheets

Sarah_Simpson · ‎Dec 16, 2019

I’m trying to design a HR/ Training Block to track certifications for our employees.

I have a block set up with 4 sheets, Staff Database (all the personell info); A Training Record ( uploads who has done what and tracks when it’s due to expire; A Job Roles Sheet (tracks what each role needs certification for; and Certification Sheet (tracks the details of each certification)

I set up a pivot table that easily shows all the staff and who has what qualification.
I have views with each person’s current qualifications and when they expire

What I need help with is showing all qualifications an individual person needs based on their role, including which they have (and when that’s due to expire) and which they haven’t even got yet (this bit is the missing link!)

Here’s a link to the block - https://airtable.com/shrpCRJ9QIN17Vx4o

Bill_French · ‎Dec 16, 2019

Yes, this is a difficult problem domain because it’s a distant cousin of the null hypothesis paradox (i.e., it cannot be proven true, but it can be proven false).

In this case, you’re looking for gaps - data that doesn’t exist. And creating “data” about data gaps is always a challenge. As such, my only advice is to think about what it looks like when a staff member has no gaps.

A similar challenge is determining available times for a team to meet - you need to describe the possible “open” times by taking the universe of “time” (i.e., all working hours) and remove from that list the committed times for all team members.

Determining certification gaps might be far easier if you define the perfect outcome and remove from that, the certifications that a worker already has.

Ideally, this would be presented as a heatmap grid that spans time.

While I don’t have the skills to recommend the best (Airtable native) approach, others here in the forum like @Justin_Barrett are really good at questions like this).

I do like your visual aid and you came prepared with a link to tangible data. Often, details about the data and/or process model are critical to answering questions in helpful ways and I wish others would take the time to provide clues about the deeper requirements especially with complex questions.

Sarah_Simpson · ‎Dec 16, 2019

Thank you very much for your detailed reply Bill, I really appreciate your time.

That really helps to re-frame my thinking - I am looking for data that doesn’t exist! I’ll work at options that look at ALL qualifications the staff need per role, then add currency into the mix. Rather than staff & currency, trying to add need per role.

I’ve also don’t think i need four sheets now either. My simplified matrix looks like -

I really find that drawing the connections on paper help for the database design, I’m glad it helps visualise the outcome.

Bill_French · ‎Dec 17, 2019

You’re welcome.

Right, this is one thing I’ve come to realize that it [sometimes] makes sense to pivot your view of the problem domain to arrive at the simplest approach possible.

I have been told that great sculptors do not envision the clay [shape] necessary to produce the end product. Instead, they visualize what clay needs to be removed to reveal the finished product. In parallel, think many process requirements to achieve a good – perhaps more efficient – solution is to determine what needs to be removed from the block of known possibilities to reveal the topography of the gaps that are important to this reporting outcome.

Hmmm… the older I get, the more I sound like Yoda. Yoda might have been a good software architect. :winking_face: