A common workflow in Airtable is the gathering of Surveys or Forms, and often we want to link multiple surveys or forms submitted by the same person back to a single Contact record for that person. However, exposing Linked Record fields to do this in the public form itself is undesirable because we don’t want form users to see all the names of existing contacts in our base, and it also precludes form users that don’t yet exist in our Contacts table.
This leaves us using manual processes to establish the link between a form submittal and a Contact, where the form user is just entering their name or email address.
My Solution:
I’ve written a script for the Scripting Block that aims to solve this problem and automatically establish links between submitted forms and existing contacts. In addition, this particular version also logs a survey/form submittal as an “Interaction” in the Interactions table.
The Script in Use:
Here’s a short video demo:
And here’s a demo base where you can try the script out for yourself:
The Script Code:
This code is written to work in a modified version of the “Business Development CRM” Template, but can be easily adapted by replacing variable name strings for the const variables at the top of the script.
/*
* SCRIPT: Link Surveys/Forms with Contacts
* AUTHOR: Jeremy Oglesby
* LICENSE: MIT
*
* All Base specific names are saved just below in the constants listed in all caps.
* Just change the string names for each of those to adapt to your own setup.
*/
const Constants = {
CONTACTS_TABLE: "Contacts",
CONTACTS_NAME_FIELD: "Name",
CONTACTS_EMAIL_FIELD: "Email",
CONTACTS_SURVEYS_LINK_FIELD: "Survey Responses",
INTERACTIONS_TABLE: "Interactions",
INTERACTIONS_NAME_FIELD: "Name",
INTERACTIONS_DATE_FIELD: "When?",
INTERACTIONS_TYPE_FIELD: "Type",
INTERACTIONS_TYPE_SURVEY_OPTION: "🔘Survey",
INTERACTIONS_SURVEY_LINK_FIELD: "Survey Response",
INTERACTIONS_CONTACTS_LINK_FIELD: "Contacts",
SURVEYS_TABLE: "Survey Responses",
UNMATCHED_SURVEYS_VIEW: "Unmatched Survey Responses",
SURVEYS_FIRST_NAME_FIELD: "First Name",
SURVEYS_LAST_NAME_FIELD: "Last Name",
SURVEYS_EMAIL_FIELD: "Email",
SURVEYS_CONTACT_LINK_FIELD: "Contact",
SURVEYS_DATE_SUBMITTED_FIELD: "Date Submitted"
}
// Get all Tables needed from Base
const contactsTable = base.getTable(Constants.CONTACTS_TABLE);
const interactionsTable = base.getTable(Constants.INTERACTIONS_TABLE);
const surveysTable = base.getTable(Constants.SURVEYS_TABLE);
// Get all Views needed from Tables
const unmatchedSurveysView = surveysTable.getView(Constants.UNMATCHED_SURVEYS_VIEW);
// Get all Record Queries needed from Tables/Views
const contactsQuery = await contactsTable.selectRecordsAsync({
fields: [
Constants.CONTACTS_NAME_FIELD,
Constants.CONTACTS_EMAIL_FIELD
]
});
const surveysQuery = await unmatchedSurveysView.selectRecordsAsync({
fields: [
Constants.SURVEYS_FIRST_NAME_FIELD,
Constants.SURVEYS_LAST_NAME_FIELD,
Constants.SURVEYS_EMAIL_FIELD,
Constants.SURVEYS_DATE_SUBMITTED_FIELD
]
});
// Parse contacts for easy comparison against Survey names/emails
const contacts = contactsQuery.records.map(record => {
let contact = {};
let nameSplit = record.getCellValue(Constants.CONTACTS_NAME_FIELD).split(' ');
contact.firstName = nameSplit[0].toLowerCase();
contact.lastName = nameSplit[nameSplit.length - 1].toLowerCase();
contact.email = record.getCellValue(Constants.CONTACTS_EMAIL_FIELD);
contact.id = record.id;
return contact;
});
let linksToMake = [];
let contactsToMake = [];
let interactionsToMake = [];
// Loop surveys gathered from the "Unmatched" view
for (let survey of surveysQuery.records) {
const surveyEmail = survey.getCellValue(Constants.SURVEYS_EMAIL_FIELD);
const surveyFirstName = survey.getCellValue(Constants.SURVEYS_FIRST_NAME_FIELD);
const surveyLastName = survey.getCellValue(Constants.SURVEYS_LAST_NAME_FIELD);
let matchFound = false;
// loop contacts
for (let contact of contacts) {
// exact match emails
let emailMatch = contact.email === surveyEmail;
// fuzzy match first name & last name
let firstNameMatch = fuzzyMatch(contact.firstName, surveyFirstName, 0.75);
let lastNameMatch = fuzzyMatch(contact.lastName, surveyLastName, 0.75);
// if match is found, link records and log interaction
if (emailMatch && firstNameMatch && lastNameMatch) {
matchFound = true;
linksToMake.push({
id: survey.id,
fields: {[Constants.SURVEYS_CONTACT_LINK_FIELD]: [{id: contact.id}]}
});
interactionsToMake.push({
fields: {
[Constants.INTERACTIONS_NAME_FIELD]: "Survey Response",
[Constants.INTERACTIONS_DATE_FIELD]: survey.getCellValue(Constants.SURVEYS_DATE_SUBMITTED_FIELD),
[Constants.INTERACTIONS_TYPE_FIELD]: {name: Constants.INTERACTIONS_TYPE_SURVEY_OPTION},
[Constants.INTERACTIONS_SURVEY_LINK_FIELD]: [{id: survey.id}],
[Constants.INTERACTIONS_CONTACTS_LINK_FIELD]: [{id: contact.id}]
}
});
break;
}
}
// if no match, create new contact, link records, and log interaction
if (!matchFound) {
contactsToMake.push({
fields: {
[Constants.CONTACTS_NAME_FIELD]: `${surveyFirstName} ${surveyLastName}`,
[Constants.CONTACTS_EMAIL_FIELD]: surveyEmail,
[Constants.CONTACTS_SURVEYS_LINK_FIELD]: [{id: survey.id}]
}
});
interactionsToMake.push({
survey: survey.id,
fields: {
[Constants.INTERACTIONS_NAME_FIELD]: "Survey Response",
[Constants.INTERACTIONS_DATE_FIELD]: survey.getCellValue(Constants.SURVEYS_DATE_SUBMITTED_FIELD),
[Constants.INTERACTIONS_TYPE_FIELD]: {name: Constants.INTERACTIONS_TYPE_SURVEY_OPTION},
[Constants.INTERACTIONS_SURVEY_LINK_FIELD]: [{id: survey.id}],
[Constants.INTERACTIONS_CONTACTS_LINK_FIELD]: []
}
});
}
}
// Establish links for existing contacts
let linksMade = await batchAnd('Update', surveysTable, linksToMake);
// Create new contacts and establish links
let contactsMade = await batchAnd('Create', contactsTable, contactsToMake);
// Now that we've created new contacts, we need a fresh query to log the interactions
const newContactsQuery = await contactsTable.selectRecordsAsync({
fields: [
Constants.CONTACTS_SURVEYS_LINK_FIELD
]
});
// For each new contact made, check the interactions we want to log
for (let contactId of contactsMade) {
let contactRecord = newContactsQuery.records.filter(r => r.id === contactId)[0];
let contactSurveys = contactRecord.getCellValue(Constants.CONTACTS_SURVEYS_LINK_FIELD).map(s => {return s.id});
// loop interactions and establish link if it belongs to this contact
for (let interaction of interactionsToMake) {
if (contactSurveys.includes(interaction.survey)) {
interaction.fields.Contacts.push({id: contactRecord.id});
delete interaction.survey;
}
};
}
// Create new interactions and establish links
let interactionsLinked = await batchAnd('Create', interactionsTable, interactionsToMake);
// Report results
output.markdown(`### ${contactsMade.length} new Contacts created.`);
output.markdown(`### ${linksMade + contactsMade.length} Surveys linked to contacts.`);
output.markdown(`### ${interactionsLinked.length} new Interactions created.`);
// =====================================================================================
// ******************************************************** \\
// ****************** FUNCTION DEFINITIONS ****************** \\
// ************************************************************ \\
// This function establishes a fuzzy match between two strings
// The strictness of a match is determined by a ration between 0-1,
// where 1 is a 100% match; values lower than '0.60' (60% match)
// will likely not be of much use in this particular scenario.
function fuzzyMatch(termOne, termTwo, ratio) {
var string = termOne.toLowerCase();
var compare = termTwo.toLowerCase();
var matches = 0;
if (string.indexOf(compare) > -1) return true; // covers basic partial matches
for (var i = 0; i < compare.length; i++) {
string.indexOf(compare[i]) > -1 ? matches += 1 : matches -= 1;
}
return (matches/this.length >= ratio || termTwo == "")
};
/*
Use this function to perform 'Update', 'Create', or 'Delete'
async actions on batches of records that could potentially
more than 50 records.
::PARAMETERS::
action = string; one of 3 values:
- 'Update' to call table.updateRecordsAsync()
- 'Create' to call table.createRecordsAsync()
- 'Delete' to call table.deleteRecordsAsync()
table = Table; the table the action will be performed in
records = Array; the records to perform the action on
- Ensure the record objects inside the array are
formatted properly for the action you wish to
perform
::RETURNS::
recordsActedOn = integer, array of recordId's, or null;
- Update Success: integer; the number of records processed by the function
- Delete Success: integer; the number of records processed by the function
- Create Success: array; the id strings of records created by the function
- Failure: null;
*/
async function batchAnd(action, table, records) {
let recordsActedOn;
switch (action) {
case 'Update':
recordsActedOn = records.length;
while (records.length > 0) {
await table.updateRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;
case 'Create':
recordsActedOn = [];
while (records.length > 0) {
let recordIds = await table.createRecordsAsync(records.slice(0, 50));
recordsActedOn.push(...recordIds)
records = records.slice(50);
};
break;
case 'Delete':
recordsActedOn = records.length;
while (records.length > 0) {
await table.deleteRecordsAsync(records.slice(0, 50));
records = records.slice(50);
}
break;
default:
output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
recordsActedOn = null;
}
return recordsActedOn;
}
@Jeremy_Oglesby Thank you for creating such a useful script. Can you help me figure out how to use this when I have my Contacts and Interactions Table’s primary field name as a formula field?
For contacts, I have a two single line text fields for first and last name and the “Full Name” field pulls them together (along with other formatting things depending on other contact info). Additionally, my Interactions table also has a formula for a specific name (Program, Date, Event Type) to the record depending on other info input.
This script was written before the existence of the Automations feature. I would actually suggest that you take a look at Automations as your avenue for linking Form submissions to existing users. You may find that you can actually do it without using a Scripting action at all (just using native Airtable actions), which may vastly simplify the process for you.
If it’s not possible to do what you want with native Airtable actions, then this script could be adapted to work as an Automation Scripting action – but that would take a bit more work than just adjusting it for a Formula field vs a Text field.
I’m getting this error-- may just be inexperience.
SyntaxError: Unexpected token ‘)’
on line 1
at a on line 1
at Generator._invoke on line 1
at Generator.F.forEach.u. [as next] on line 1
at u on line 1
at o on line 1
on line 1
on line 1
Welcome to the community, @Miyoko_Sakashita! :grinning_face_with_big_eyes: Could you share the full script that you’re running that generates that error? When pasting it, please use the preformatted text option to format it (the button that looks like this: </>)
Thanks for your response, and for creating this! Can you say more about how you could use Automations Scripting Action to link a form submission (survey response) to an existing contact, and to create a new contact when my primary field is a formula? It would be ideal to find a way to allow users to fill out a form and have their response be matched to an existing record, without asking the person to choose and link it (and thus exposing the records we have).
