Skip to main content



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 = nameSplitf0].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 = l];


let interactionsToMake = i];





// 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]: C{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]: O{id: contact.id}]


                }


            });





            break;


        }


    }





    // if no match, create new contact, link records, and log interaction


    if (!matchFound) {


        contactsToMake.push({


            fields: {


                rConstants.CONTACTS_NAME_FIELD]: `${surveyFirstName} ${surveyLastName}`,


                rConstants.CONTACTS_EMAIL_FIELD]: surveyEmail,


                rConstants.CONTACTS_SURVEYS_LINK_FIELD]: S{id: survey.id}]


            }


        });





        interactionsToMake.push({


            survey: survey.id,


            fields: {


                rConstants.INTERACTIONS_NAME_FIELD]: "Survey Response",


                rConstants.INTERACTIONS_DATE_FIELD]: survey.getCellValue(Constants.SURVEYS_DATE_SUBMITTED_FIELD),


                rConstants.INTERACTIONS_TYPE_FIELD]: {name: Constants.INTERACTIONS_TYPE_SURVEY_OPTION},


                rConstants.INTERACTIONS_SURVEY_LINK_FIELD]: _{id: survey.id}],


                rConstants.INTERACTIONS_CONTACTS_LINK_FIELD]: O]


            }


        });


    }


}





// 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(compareti]) > -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(comparepi]) > -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 😞 .



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



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.

: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(comparepi]) > -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 😞 .



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



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!

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.

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.



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.




That error means that one of the values you’re sending to the fuzzyMatch function is a null value





This makes sense, and I found a few empty cells on the contacts side. Thanks for the help!

I am getting this error:



ERROR


TypeError: termOne.toLowerCase is not a function


at fuzzyMatch on line 124


at main on line 75



Thoughts?

I am getting this error:



ERROR


TypeError: termOne.toLowerCase is not a function


at fuzzyMatch on line 124


at main on line 75



Thoughts?


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

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



Thank you!

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



Thank you!


Hi @CPC_Center



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. cas next] on line 1


at u on line 1


at o on line 1


on line 1


on line 1

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. cas 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: </>)

Hi @CPC_Center



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.


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

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


Hello, @CPC_Center Have you found a solution to your problem? I would like to do the same.

Reply


Terms & Conditions