How to compare two fields to test for a match

[Tom_Glatt]

I’m trying to compare two fields. One contains certification requirements for a role and the other the certifications achieved by an individual. The certification requirements are contained in a lookup field. The certification achievements are contained in a linked records field.

I tried an if statement which works if the items are in the exact same order in each field, but it fails if the order is not the same. This makes sense now that I think about it, but I’m not sure what my alternative is to determine whether an individual has all of the required certifications.

Thanks in advance for a nudge in the right direction.

[Bill_French]

Tom_Glatt:

I tried an if statement which works if the items are in the exact same order in each field, but it fails if the order is not the same.

Yeah - the nudge you need is to test in the IF() statement a search result.

SEARCH(stringToFind, whereToSearch,[startFromPosition])

[Tom_Glatt]

Didn’t think about combining the two. I’ll give it a try. Thanks Bill!

[Tom_Glatt]

Bill.French:

SEARCH(stringToFind, whereToSearch,[startFromPosition])

I got the suggestion to work somewhat. Here is what I wrote:

IF({Succession Required Certifications}="",“NA”,IF(SEARCH({Succession Required Certifications},Certifications)>0,“Current”,“Not Current”))

If the required certifications only contains one record it conducts the search/check just fine. However I still run into a problem if required certifications are listed as Cert A and Cert B while certifications are listed as Cert B and Cert A. Again makes sense because it is searching for the whole of the requirements entry in the certifications column (Cert A and Cert B) and doesn’t find it - but how to make the search more forgiving?

[Bill_French]

Tom_Glatt:

entry in the certifications column (Cert A and Cert B) and doesn’t find it - but how to make the search more forgiving?

Well, this is not so much a constraint of search as it is your data model.

Assuming the data model is in stone, you really want a way to search for records where the field contains “Cert[space]” and (contains “[space]A” OR “[space]B”), right?

To do this, you’d need to craft a new (more encompassing) algorithm to express this requirement using the LEFT(), RIGHT(), and MID() string functions. It can be done in native formulas, it’s just tedious nailing it down. The alternative is a Script Block.

[Tom_Glatt]

The model is definitely not set in stone. The basic desire is to be able to say “these certifications are required for this role” and then to check individual qualifications against the requirements. Here is how I set up the base:

1. Role Table: Contains various organizational roles, the people in those roles (one-to-one related to People table) and certification requirements (one to many related to Certifications table).
2. People Table: Contains individuals, their current role (one-to-one related to Role table), their current certifications (one to many related to Certifications table).
3. Certifications Table: Contains various certifications, related to roles and people as described.

I figured the way to check certifications is through the people table, where I look up the required certifications via the role table relationship and then compare what is required to what is actual.

If there is a more streamlined way to construct the model to make the comparisons easier I’d welcome the insight. Otherwise I’ll dig into the native formulas or the script block.

Appreciate the dialogue. Thank you!

[Bill_French]

Tom_Glatt:

The model is definitely not set in stone.

Then I would entertain the possibility that these questions can be answered by specific views that would require subtle changes to the data model, although this would require me to dive deeper on the actual data. However, I have a sense that even the current data model might be suitable to develop a series of views that use filters to scope the reporting and search requirements. Perhaps @ScottWorld has a few ideas.

[ScottWorld]

I think @Bill.French is right on the money, yet again! :slightly_smiling_face: Bill’s analysis is solid, but that is to be expected from one of our local superheroes.

@Tom_Glatt, if you end up getting over your head with reconstructing your database model, and you have a budget for your project, feel free to send me a private message. I am an expert Airtable consultant and I do this as my profession! :slightly_smiling_face:

[Tom_Glatt]

Thanks for the replies and insight. We ended up going a different route with a few key assumptions about workflow that changed one of our tables - meaning a little tweak to the data model - that made the need to compare in the way we were thinking unnecessary.