Link several contacts to a single ID

[Gregory_Ferenst]

I want to analyze communications between different users and need a way to combine different contact methods into a single identifier. I have a sheet that lists all the different contact methods for a single person (email, slack, phone), but not a way to link that to the sheet that keeps track of communication.

I found a solution to this problem in Google sheets after asking the question on stackoverflow. Here’s a link to the sheet, which resembles the airtable i’m using (the formula answer is in C2)

docs.google.com

sample spreadsheet

contact methods Contact method,person,Column i don't know how to make. I want this ID to appear automatically when a row is added with information in the second...

How would I do something similar in airtable? Keep in mind that i’ll have many many contacts and several different types of contact methods (slack user names, real names, emails, phone number etc).

Part of the challenge is that a phone number, slack user name or email is all in the same column. In previous forum questions, Zapier was suggested as an answer, but given the complexity of different users and contact methods, that might be too cumbersome.

Thanks and let me know if i can make the question more clear

[Mike_McLaughlin]

is the goal that you will have a list of contact addresses (i.e. emails, phone number, and slackID’s) that will be alone in a table, that will then be assigned a person ID (by a human)?

Then presumably, you want the person ID to have 3 fields (columns) that will each (auto)populate with the appropriate address type for that field?

[Gregory_Ferenst]

The goal is to create a column in the first sheet (the contact log) that has the appropriate ID in the row, no matter what the contacts method is used (email phone, slack, etc).

[Gregory_Ferenst]

any idea how I might solve this? thanks again for the help!

[Mike_McLaughlin]

I mocked this up real quick, is this what you are aiming for?
Table 1:

Table 2:

Table 3: the desired output

[Gregory_Ferenst]

It’s pretty close @Mike_McLaughlin ! the only difference is that in the first image, email, phone and slack are in separate columns. But, yes. how would i create something like that? in this case, id’ want to create the customer ID column in the third table

[Justin_Barrett]

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).

[Mike_McLaughlin]

I’m with @Justin_Barrett, this is now feeling like an exercise in data manipulation, i.e. going around in a complete circle of transforming the relationships between different pieces of data where you’ll wind up exactly with what you started with.

Also, the ‘ID’ I put in the first table was intended as a ‘Customer ID’.

As a piece of more constructive feed back, you should make up a visual mock-up of what you want.
-create a table (A) for the input data, entered by the customer service person.
-create a table (B) for what you want the output to be.
-also table © which may have any fixed data that you want to reference in these other tables.
-then draw some arrows/lines illustrating where you want data from (A) to ultimately go into (B), and any relationships to ©

[Gregory_Ferenst]

Sorry if this wasn’t clear @Mike_McLaughlin and @Justin_Barrett . A few notes

1. The mockup of what I want is in this google spreadsheet. Its column C that I don’t know to create in airtable.

docs.google.com

sample spreadsheet

contact methods Contact method,person,Column i don't know how to make. I want this ID to appear automatically when a row is added with information in the second...

2. The problem is that the way the information comes in is I don’t have the ID. the sheet autopopulates from text messages or email. So, if its an email, the sheet row will be created with “email” and the email of the sender. The sheet cannot connect that this email belongs to one of my existing contacts. I found a way to do it in sheets, but not airtable, because I can’t use the same VLookup formula in airtable.

I could manually link each row, but the volumn of messages is too high for that.

Does this make sense? Thanks again for the help

[Justin_Barrett]

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:

Gregory_Ferenstein:

the only difference is that in the first image, email, phone and slack are in separate columns.

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.