Weekend scripting block challenge and \$1,000 prize

My linking script block entry is an implementation of the Levenshtein Distance Algorithm to suggest links between two sets/tables of data. The algorithm produces a metric that determines how alike two strings are and in this base, we make a link between the records that are “sufficiently” similar.

You can see a brief walk through the process here:

and the base is published in the Universe here:

Here is the script:

// Levenshtein distance function
let levenshteinDistance = function(a, b){
if(!a || !b) return (a || b).length;
var m = [];
for(var i = 0; i <= b.length; i++){
m[i] = [i];
if(i === 0) continue;
for(var j = 0; j <= a.length; j++){
m[0][j] = j;
if(j === 0) continue;
m[i][j] = b.charAt(i - 1) == a.charAt(j - 1) ? m[i - 1][j - 1] : Math.min(
m[i-1][j-1] + 1,
m[i][j-1] + 1,
m[i-1][j] + 1
);
}
}
return m[b.length][a.length];
};

// max string length function (gets the max length of two strings)
let maxStringLength = function(a, b){
return Math.max(a.length, b.length);
};

//get the ratio that the user wants to use on this iteration, e.g. 0.8
let inputRatio = await input.textAsync("Enter the match ratio for this search (number between 0 and 1)")

// set the tables
let matchTable = base.getTable("Match");
let masterTable = base.getTable("Master");

// get the records from the master and match tables
let matchQuery = await matchTable.selectRecordsAsync();
let masterQuery = await masterTable.selectRecordsAsync();

// iterate through the match records
for (let matchRecord of matchQuery.records) {
// create an empty array to hold the matched IDs from master
let matchedIds = []
// iterate through the master records
for (let masterRecord of masterQuery.records) {
// do some regex so that only leters and numbers are considered - also lower case the two strings
let formattedMatchRecord = matchRecord.name.toLowerCase().replace(/[^a-z0-9]/gi,'');
let formattedMasterRecord = masterRecord.name.toLowerCase().replace(/[^a-z0-9]/gi,'');

// calculate the Levenshtein distance
let ld = levenshteinDistance(formattedMatchRecord, formattedMasterRecord);
// calculate the max length of each pair of strings (match and master)
let maxLength = maxStringLength(formattedMatchRecord, formattedMatchRecord);

// log the inputs and outputs for debugging
console.log(formattedMatchRecord, formattedMasterRecord, ld, maxLength, 1-ld/maxLength);

// if the calculated ratio is greater than the input threshold
if(1-ld/maxLength > Number(inputRatio)) {
// add the master id to the array
matchedIds.push(
{id: masterRecord.id}
)
}
}
// update the match record with the array of master ids
let update = await matchTable.updateRecordAsync(matchRecord, {
}
);
}

This script could be used to match two sets of data - for example a “master” customer list and a second set of customer data from another source. The script would suggest that, perhaps “Mr Jonathon Bowe” could be matched with “Jonathan Bowen”.

Hopefully the comments explain how it works, but feel free to ask any questions.

Thanks

JB

6 Likes

Sounds powerful @JonathanBowen, thank so much for sharing this!

5 Likes

Thanks for submitting @openside!

1 Like

Creating multiple linked records based on a numeric field

Here is a script inspired by a community request for creating new linked records based on an number submitted in a form.

Here is the base.

6 Likes

Script Purpose

While the current rollup fields can display the minimum or maximum value of a set of linked record field, that functionality is limited. Rollups with min/max do not work for string based values, and they are also limited to displaying the value of the min/max field, not a different field from the linked record.

This script provides the ability to lookup the first or last record of a set of linked record when sorted by any field. It then outputs any field from the first/last linked record to a field in the original record that contained the links.

Example use case / Demo base

The demo base is based on the template Individual and corporate donations.

When building a campaign to target previous donors/contacts, it is useful to know the date and amount of the last donation. You can then send different messages to recent donors versus lapsed donors. If a donor has recently sent a very large donation, you can send a extra special thank-you gift instead of another donation request.

This script looks up the date and amount of the last donation for each contact and put the values in the new {Last donation date} and {Last donation amount} fields in the [Contacts] table. Those values can then be used in other formulas.

Script Notes

• This script uses both of the helper functions that I created to make working with field values easier. The helper function that provides a hash table mapping record ids to field values helps performance when there are many records in the table holding the linked records.

• The script runs the main function two times. Once to demonstrate outputting the same field that is used for the sort, and again to demonstrate outputting a different field from the one used for the sort. When adapting the script for your own use, you may want remove the code that has the script run a second time.

• This script needs to be configured for your base. The configuration is stored in a scriptOptions variable. The first thing the script does is validate those options. If they are not valid, the script will throw an error.

• When run, before making any data changes, the script tells the user what the script does, customized with the table and field names provided in the script options.

• If a record has no linked records, or if all the linked records have null values, there is no first/last record and the output field will be blank.

Code

2 Likes

Edit: Refactored script to put all field names into variables to allow easier adaptation to any base; updated base in Universe with refactored script as well - @Jason

The workflow I aim to improve:

Automatically linking survey and form responses to existing Contacts without exposing Contacts to Form users.

Script, explanation, and video are in the following Show & Tell post:

3 Likes

Automatically link records to a summary table based on date

I’ve created a script which will automatically pull the year from a date and enter this in the linked record field. It is a common scenario where you have a reports/dashboard table which summarises key data from another table.

With thanks to Jason (Airtable) who created the church CRM base in Universe which I used to demonstrate this particular example.

Video here:

Scripting block here:
https://airtable.com/shrTq5CSFFqqJdI5q

3 Likes

Description

Hi, this is a quick little script for populating fields with a number of randomly linked records. For example if you want to give out a prize (like this one), or randomize some email recievers - you can efficiently link to a random profile / consumer / person (or anything for that matter). Maybe it’ll help you with some workflow, maybe not. Either way, here is is.

It’s my second time writing in JavaScript ever + I’m new to this so there’s probably alot of inefficiencies and bugs.

script

/* C O N F I G */
/* names: fill to match own table*/
let tableToPullFrom = 'table';
let viewToPullFrom = 'view';

let tableToWriteTo = 'table';
let viewToWriteTo = 'view';
let fieldToWriteTo = 'field';

/* settings */
let unique = true;

/* C O D E */
/* creates list with IDs of all records from the table to pull from */
tableToPullFrom = base.getTable(tableToPullFrom);
viewToPullFrom = tableToPullFrom.getView(viewToPullFrom);

let listOfRecordsToPull = [];
let pullResult = await viewToPullFrom.selectRecordsAsync();
for (let record of pullResult.records) {
listOfRecordsToPull.push({id:record.id});
}

throw "You can't request more links per record than there are in view to pull from";
}

/* choses a random element from the list and pops it if unique is true */
randomElement = listOfRecordsToPull[Math.floor(Math.random() * listOfRecordsToPull.length)]
if (unique == true) {
var index = listOfRecordsToPull.indexOf(randomElement);
if (index !== -1) listOfRecordsToPull.splice(index, 1);
else console.error('ran out of linked records for your request, add more or turn off the unique setting');
}

tableToWriteTo = base.getTable(tableToWriteTo);
viewToWriteTo = tableToWriteTo.getView(viewToWriteTo);

let writeResult = await viewToWriteTo.selectRecordsAsync();
for (let record of writeResult.records) {
/* made as set to avoid duplicate errors  */
let writeInThisRecord = [];
for (let i = 0; i < linksPerRecord; i++) {
/* chose random */
randomElement = listOfRecordsToPull[Math.floor(Math.random() * listOfRecordsToPull.length)];
/* skip and retry if duplicate */
if (writeInThisRecord.indexOf(randomElement) !== -1) {
i--;
continue;
}
/* pop if unique */
if (unique == true) {
var index = listOfRecordsToPull.indexOf(randomElement);
if (index !== -1) listOfRecordsToPull.splice(index, 1);
else console.error('ran out of linked records for your request, add more or turn off the unique setting');
};
writeInThisRecord.push(randomElement);
/* deal with duplicates */
writeInThisRecord = Array.from(writeInThisRecord);
/* update  */
await tableToWriteTo.updateRecordAsync(record,{[fieldToWriteTo]: writeInThisRecord});
}
}
markdown.output('script finished')
4 Likes

Hmm… google seems to have truncated my video but you get the gist

Lots of great entries this time!

Nice work everyone!

1 Like

@Jeremy_Oglesby Great idea for a script. The idea of a fuzzy match is also interesting.

I noticed that your script needed to be customized in several places, not just at the top.

// ** MUST MANUALLY SUPPLY FIELD NAMES **
contactsToMake.push({
fields: {
"Name": `\${surveyFirstName} \${surveyLastName}`,
"Email": surveyEmail,
"Survey Responses": [{id: survey.id}]
}
});

Since you already have these field names in variables, you can use the variables as keys if you put the variables in square brackets.

// **USE VARIABLES FOR FIELD NAMES **
contactsToMake.push({
fields: {
[CONTACTS_NAME_FIELD]: `\${surveyFirstName} \${surveyLastName}`,
[CONTACTS_EMAIL_FIELD]: surveyEmail,
[CONTACTS_SURVEYS_FIELD]: [{id: survey.id}]
}
});
1 Like

Ahhhhhhhh!!!

Thank you!

I can’t tell you how long I tried to figure out how to do that. I tried so many different ways to use variables there, and had no idea it could be done with square brackets!

Thank you, @kuovonne - that is an immensely helpful revelation!

2 Likes

Congrats on learning JavaScript and being brave enough to publish what you wrote! I think a lot of people are learning JavaScript, and that’s awesome. You also might be surprised at how recently some other people have learned JavaScript. If you keep at it, everything will get easier. I know the first script that I published had some really awful code in it. Getting a working script is a major first step!

I knew this, but I still don’t know why it works. Anyone?

It makes sense if you think about it. When you want to use a string literal as a key, you put it in square brackets. If you have a variable for the key inside square brackets, at runtime the variable is converted the the string it represents, and you are back to a string literal inside square brackets.

For example, if a script says

const fieldName = "Name";

and later on says

record.fields[fieldName]

at runtime, the variable fieldName is converted to the string “Name” so the expression becomes

record.fields["Name"]
1 Like

@JonathanBowen

Thanks for the link to the wikipedia page about the Levenshtein Distance Algorithm! Having this algorithm will help me improve a long-term project that I am working on.

1 Like

Actually, it doesn’t. The more I think about this, the less sense it makes. And I consider myself one of the top 90% most intelligent people in the room.

That part I get, but this part isn’t as obvious:

{[fieldName] : "Value..."}

I’m old; hold my hand so I get through this and coronavirus.

It’s the exact same situation.

const fieldname = "Name";
let myFields = {[fieldName] : "Value..."};

is converted at runtime to

let myFields = {["Name"] : "Value..."};

which is the same as

let myFields = {"Name" : "Value..."};

I suppose it would be easier if the square brackets weren’t necessary, but then it would be impossible to tell if the key were an actual name, or a variable because

let myFields = {fieldName: "Value..."};

at runtime becomes

let myFields = {"fieldName": "Value..."};

If you want to continue this discussion, should we create a new topic?

1 Like

Thank you again everyone for your amazing submissions! Our team is going to review these and announce the winner at the end of the week.

2 Likes