Script - multiple select update error

[Mike_Wilson1]

Hey Airtable Friends!
I have a problem I can’t solve, but I feel like it has to be doable. I would love some help!

Situation: We’re a mortgage company and we keep a database of our Loan Officers and what states they’re licensed in. Those licenses need to be kept up by the employee, so they come and go very often. Each state their own license syntax, so in Airtable, we have 50 text fields, for the 50 states and when a license gets added or removed, someone goes in and types in the license number for the Loan Officer in the corresponding state field, or deletes it from the state field (if it’s removed).

Problem: We very often need to see who all we currently have licensed in X state. But it’s very annoying to sideways scroll through 50 fields - So, how we currently do it is someone adds or removes a license, then they scroll over to a multiple select field and add the state from a drop down. That way we can go to ‘John Smith’ and see that he’s licensed in states, X,Y and Z.

What I can’t solve: I want a way to for the multiple select field to be populated by whether or not a state’s license field is empty or not. So for example, I enter in a California license for ‘John Smith’ in the California License Text Field, and in the multiple select field, CA gets added. In logic that would be:
IF: CA License Field if not Empty
THEN: Select CA from Multiple select.
But for all 50 states.

Is that doable?
Any help would be appreciated.
Thank you!

[ScottWorld]

That would require scripting or using an external automation platform like Integromat.

You could definitely setup your database system in the way that you described, but I don’t personally think that that’s the best way to setup your system because it’s not an efficient use of fields nor your user interface.

It sounds like you would probably benefit by creating a many-to-many relationship, and your junction table should be the intersection of customer and state. Then, from your customer table, instead of a multi-select field, you would use a linked record field.

Better yet, if you still wanted to automate a multi-select field after setting it up that way, you could then do that natively with Airtable’s native automations (no scripting nor external tools necessary).

This article explains more about setting up a many-to-many relationship:

Airtable Support

Airtable's guide to many-to-many relationships

What sets Airtable apart from ordinary spreadsheet applications is its ability to link related concepts together. Knowing how to represent the relationships between different groups of objects can ...

[Mike_Wilson1]

Thanks so much for your reply!

I looked through the article you linked about the many-to-many relationships, but unless I’m not understanding it correctly, I believe our relationship is actually a one-to-one relationship.

I say that because each loan officer can only have one license per state, and each state license is unique to that specific loan officer. There’s no intersection there for a junction table because the data we’re trying to automate isn’t a new piece of data, it’s just an acknowledgment of that connection.
ie. John Smith the loan officer - has license number in California - therefore John Smith is licensed in california - and then so on and so on for all of the states.

By automating the acknowledgement of that connection we’re trying to make a field that summarizes those connections.
ie. Has license number in X, Y, Z states - Therefore, is licensed in X Y Z states.

[ScottWorld]

You could still set that up as a many-to-many relationship (where the junction table would be the intersection of the customer table and states table), but it might be significantly easier to just setup your base as a more standard “one-to-many” relationship.

So you would just need to create one additional table to keep track of all the licenses, and back in the customers table, you would create a linked record field which would link each customer to all of their particular licenses.

That same support article above describes one-to-many relationships.

[Mike_Wilson1]

When you say customers are you referring to our Loan Officers? If we broke out the licenses into a different table, we would still have to mark in that table which Loan Officer holds that license. If we had 100 Loan Officers licensed in CA we would have 100 different CA licenses, and we’d have to know who the license belongs to for each one. I guess to me that seems like we would just be duplicating information.

And if we did that it still wouldn’t solve our automation problem of updating a multiple select field to see what states any given Loan Officer is licensed in.

[ScottWorld]

Yes, I meant loan officers, not customers.

Well, you wouldn’t necessarily need the multiple select field anymore because you could search the linked record field instead. However, if you still felt like you wanted the multiple select field, it would be significantly easier to automate it with the linked record setup, because you wouldn’t need to account for 50 different fields. As I mentioned above, having 50 different fields would require that you write a custom JavaScript or turn to an external automation platform.