Scripting Block: Link Surveys/Forms Submitted to Contacts

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.

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;
}
9 Likes

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!

1 Like

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

1 Like