Hi Justin, thanks for the response!
So I’ll try to explain this the best I can. I’m using my database for business purposes within a manufacturing plant. More specifically, I want to track training certifications for employees.
The way this process works in our plant is that there are many individual processes that an employee must be certified to. But there are specific roles that require an employee to possess a specific “set” of those individual certifications to be considered certified for the role.
The data that is in the two rollup columns contain controlled document numbers for individual training documents (in other words, an individual process). Each controlled document number is a single “string” (if that’s the proper terminology?) without any spaces or anything, but it is comprised of both letters and numbers.
An example of one of the controlled document numbers would be : " SOP-101-6002_OPERATION ".
The first column will be a rollup of a bunch of controlled documents that an individual employee has been certified to.
The second column will be a rollup of a bunch of controlled documents that are required for an employee to be certified for a specific job role.
The third column would then look to the first column to see if any of the controlled document numbers that the employee has already been certified to match the ones “required” in the second column for being certified to a specific role. I would like the third column to list any controlled documents that are unique to the second column to be listed. (Essentially showing which individual training certifications the employee still needs to be certified for the role.) If the employee possesses all the training certifications (column one) that are required (column two) then I would like it to return a green checkmark emoji to illustrate that the employee possesses the documents required and is therefore “certified” for the role.)
Employee names would be the “records” (or rows). The employee’s individual controlled documents they’ve been certified to would be rollup column one and the job role with its own group of required controlled documents would be column two.
The problem I’m having is that column one will be representing every individual controlled document that the employee may have, including ones that don’t apply to the role they’re attempting to be certified to. I need it to ignore any additional certifications the employee may possess (in column one) and only look for the applicable ones that match the ones required for the role (in column two).
My trouble might be that I don’t fully understand the characteristics (or rules) of how “strings” and/or “arrays” work. My database is pretty robust and I’m pretty proud of some of the clever tricks I’ve been able to implement so far (or table gymnastics as you cleverly put it ). But when it comes to working with manipulating text my formula knowledge is admittedly a little more lacking. I’ve been able to use simple functions and such to manipulate text, but maybe what I’m trying to make Airtable do is too ambitious? Maybe I need a proper SQL system?
(Sorry for the novel! I hope that made sense? )