Nov 03, 2021 12:14 PM
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.
Nov 04, 2021 11:16 AM
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:
Nov 04, 2021 11:49 AM
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.
Nov 04, 2021 06:14 PM
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.
Nov 05, 2021 03:34 PM
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.
Nov 05, 2021 04:00 PM
Yes, I meant loan officers, not customers.