I use forums quite regularly, but cannot seem to find information that suits my case. It’s my first time actually posting, so please bear with me!
I realise this would be easy to do by a simple rollup, but the business case is a little more complicated. Please allow me to explain.
I have a HR AirTable system which applicants apply via an AirTable form, which the submitted data feeds into Table A. One of the questions (and therefore a field in Table A) asks the applicants what their top 5 skills are – this is a single answer question. The answer options are fed in from a “linked” table (Table B), which has each skills listed as a separate record in the primary key field. In the second field of this Table B, are archetypes. These archetypes are based a cluster of 5-7 skills (trying not to complicate my explanation, but this is present in a third Table, Table C) E.g. the “Marketing Wizz” archetype would have the skills copywriting, promotion, event management, etc. To summarise, we have 11 archetypes, and 41 skills (41 possible answer choices).
I want Table A to automatically assign an archetype (in a field) to a new application (record) as they are submitted. Applicants cannot “choose” their archetype, only their top 5 skills. I think the minimum requirement across all 11 archetypes should be that they have at least 3 skills to be “assigned” an archetype.
At the moment, I have a rollup set up with shows these corresponding archetypes, but they are not unique, and are TRUE if there is just one skill listed (rather than the minimum requirement of 3, as mentioned just above). E.g. an application may list copywriting, promotion, accounting, etc… which the rollup field would show Marketing Wizz, Marketing Wizz, Finance Master etc. As I am aware, there should be some sort of formula written here, but I just don’t know how to go about it.
I hope that makes sense. Happy to explain further if need be.
Does anyone have any suggestions or pointers about setting this up?
Really appreciate any information!