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;
}
Great work, Jeremy! I wonder if with a bit more code this could be a way to allow individuals to edit a single record in a database through a form without having database editing privileges. For example, imagine you had a directory of businesses with information about each one, and you wanted businesses to be able to change only their information. You could do this by having a password field (that would be hidden in the public table) in an “edit business information” form. This password combined with the business name could be used to link the records together. Of course, you’d need to be able to tell the business if the password didn’t match their username after submitting the form. Being able to restrict editing privileges to records in databases has been a feature requested by Airtable users for quite some time. I’m not a developer myself, so I hope someone who is interested can take this idea and run with it!
:man_facepalming:t2: I made a boo-boo in my script – if anyone is using this (@Casey_Conway1, going to single you out since you’ve responded to the post), please replace the entire fuzzyMatch() function with the following:
// 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/string.length >= ratio || termTwo == "")
};
There’s really only one term that was wrong, but for non-technical users, it may be easier to just wholesale copy-paste the entire function over the top of the old one.
While no error in software is a good thing, and I’m embarrassed to have shared this with people with an error like this in it, I’m at least glad that my error caused the function to be TOO strict in matching, rather than the opposite… in fact, as it was, my function would only clear exact matches. In other words, it wasn’t fuzzy-matching at all :disappointed: .
For those interested in the technicals of my blunder, I originally has this function structured to be called off a string object, and the string-to-compare with was the only string passed in as a parameter. ex:
let myString = "Do I match?"
let compareString = "No, probably not"
let match = myString.fuzzyMatch(compareString, 0.6)
// match == false
I made this work in other contexts by extending the String prototype with this function. However, Airtable’s scripting environment doesn’t allow the extending of prototypes, so I had to modify the function to take both strings as parameters, rather than being called off the initiating string. When I did this, I missed an instance of the this keyword in the return statement, which would have been referring to the initiating string that the function was called from. But there is no this with the way I modified the method! So the value being compared to ratio in the return was always a value divided by 0… which JavaScript happily, and quietly passed right over as a falsey value :confounded: .
I hope I haven’t caused anyone consternation or trouble with this mistake, and I hope anyone who has used this script, or parts of it, will see this and be able to make the necessary adjustments.
This is super helpful! I modified it slightly by removing the interactions table and just let unmatched survey responses alone for me to review. Thank you so much! I was wondering though if you could tell me how to also fuzzy match the email? Seems like it is case sensitive and will not match unless the case matches in the email. Thanks a million!
The fuzzyMatch function that @Jeremy_Oglesby provided above should work for email comparisons. Just feed both email options into that function. Part of its operation is to force both compared items to lowercase, so that case differences between the two versions won’t impact the comparison.
Hi there. I’m trying to use the fuzzyMatch function on emails as @Justin_Barrett mentioned in the last reply. I’m running into the following error: TypeError: Cannot read property ‘toLowerCase’ of null
My code is posted below:
// exact match emails
// let emailMatch = contact.email === surveyEmail;
// fuzzy match emails
let emailMatch = fuzzyMatch(contact.email, surveyEmail, 0.75);
fuzzy match first name & last name
let firstNameMatch = fuzzyMatch(contact.firstName, surveyFirstName, 0.75);
let lastNameMatch = fuzzyMatch(contact.lastName, surveyLastName, 0.75);
I don’t usually work in javascript, so it’s probably a simple thing that I’m getting wrong, but I’d appreciate any help.
That error means that one of the values you’re sending to the fuzzyMatch function is a null value:
contact.email
contact.firstName
contact.lastName
surveyEmail
surveyFirstName
surveyLastName
If you’re using the getCellValue method when retrieving data from a field, an empty cell will return null. You might try changing the code to use getCellValueAsString instead. With that, and empty field will return an empty string, which won’t cause an error.
@Megan_Eccleston That most likely means that the data that you’re passing as termOne isn’t a string. Check the field that it’s coming from. If you’re retrieving the value using getCellValue, the data you get back isn’t always a string depending on the field type. You could use getCellValueAsString instead, which will auto-convert the result into a string.
