Link several contacts to a single ID
I mocked this up real quick, is this what you are aiming for?
Table 1:
Table 2:
Table 3: the desired output
Maybe I don’t know enough about how this data will be used, but it feels like you’re making this harder than it needs to be. Starting with the contact method first—especially when each contact method will be in a different field—and trying to find out who’s tied to that contact method makes this whole thing much more challenging.
My initial impression is that it would be far easier to link to a record for Bob—because I’m pretty sure that Jose knows it’s Bob he’s talking to—and then, if you really want to see his email/phone/slack details in the list, you can use a lookup.
With this all I had to do was pick Bob’s name and the contact method. The ID came in via a lookup, and so did the email (the hidden fields look up all of Bob’s info, and the formula in {Detail} displays the appropriate one based on the chosen method).
That definitely makes sense.
How often will you receive contact info from someone who’s never been on the list before? Or is it a case where most/all contacts are known, and you just need to automate the connection to the ID?
If it’s the latter (which I’m hoping it is), then the suggestion from @Mike_McLaughlin is pretty much what I’d suggest. To clarify the contents of Table 1, I suggest making that table the core place where all contacts are stored. While Mike’s example only shows the ID and contact methods fields, I recommend adding a field with the user’s full name so you know exactly who it is (this may have been implied in Mike’s example, but it’s not in the screenshot, so I’m just clarifying).
The second table is the one that will take some work to connect each contact address/phone/username/??? to its associated user by ID. However, once all that’s set up, the only time you’d need to revisit it is when a new contact comes along who’s not already on the list.
The easiest part will be importing new data. Assuming all of the to-be-connected contact info is in the same column in your import, all you’d need to do is paste that data into the {Customer Address} field (again using Mike’s example). If all of those items are connected to existing contacts in the [Contact Methods] table, you’ll see the associated IDs appear in the adjacent field. If not, that field will probably be blank, and you’ll have new connections to make in the [Contact Methods] table. But again, assuming that new contacts won’t come along very frequently, this probably won’t happen a lot.
Going back to one of your comments on Mike’s suggestion:
The reason they’re all mashed together in a single field is because those are the automatic reciprocal links coming from Table 1. If you wanted to, you could still have separate fields for each contact method. However, the question that arises from that is: won’t that mean that a person’s email (for example) has to be edited in two places—the [IDs] table and the [Contact Methods] table—if it ever changes?
Not necessarily.
Instead if storing the contact info in those fields directly, make those fields links back to the [Contact Methods] table. You might think that this would create a circular reference, but oddly enough it doesn’t. Here’s my own test of this setup. Here’s my version of the [Contacts] table (what Mike called “IDs” above). I hide the reciprocal links field for clarity:
All contact types link to the [Contact Methods] table. And here’s the thing: rather than going to that other table to add them, I just changed the field type from my original test, and all of the records in [Contact Methods] were made automatically by Airtable.
“What about new records?” you may ask. “Won’t I have to go to Contact Methods and add the info there?”
Nope.
Stay in the [Contacts] table. Add a new record, and enter the person’s name in the {Name} field. For the others, just click the + to search, and enter (or paste) the new email/phone/???. When it can’t be found, just hit Enter (making sure the “Add a new record” link at the bottom of the search box is highlighted):
After filling out new info for my sample guy George, the [Contact Methods] table looks like this:
Knowing that George’s ID is 4, what I need to do now is put a link to “4” into the ID column for those new records. That could be done manually one record at a time (type “4” with the field selected, then hit Enter when the record appears in the search), but knowing that the links in the various contact fields would never overlap, I made a formula column that concatenated all of them. That way I could just copy that four-row selection, then paste it into the ID field to make automatic links.
Now each person’s contact info can be both added and edited in one place, the [Contacts] table. Because those are actually links to the [Contact Methods] table, the latter table can also be a link source on the [Contact Log] table, instantly connecting anyone’s contact info with their ID.
