Link Surveys/Forms Submitted to Contacts

Creator: Jeremy Oglesby

The Problem:

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.

Check out the full written and video tutorial, as well as a sample Airtable Universe base, on how the script works here.

Source Code

							
							/*
 * 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;
}