Weekend scripting block challenge and $1,000 prize

Hi folks! Blocks Madness is ramping up with a big challenge - and a big prize to suit.

One of the most powerful features of Airtable is its database functionality — with linked records, you can eliminate redundancy, enforce data integrity, and enable new methods of automation.

For this challenge, submit a script that uses linked records to improve a specific workflow. Your script should take advantage of the power of linked records — maybe it integrates with rollups, lookups, formulas, or even blocks like page designer, Gantt, or flowchart.

You can either create this script in one of our pre-built templates or you can create your own base and publish it on Airtable Universe with the “Show code in public shares” setting enabled in the block.

For more suggestions, see our best practices for open sourcing a script. And like the last contest, please submit your script with a short walkthrough screen recording (we recommend free tools like Loom) where you explain what it does — please include audio and keep your recordings under 2 minutes.

Post your submissions here by 6:00pm (PST) on Monday, March 16, and we’ll announce the winner shortly after.

Thanks and good luck!
 

P.S. A couple of requirements for this contest:

  • Submitted scripts cannot have been already submitted to a previous contest.

  • By participating in this contest, you confirm that you are not a resident of a country sanctioned by the U.S., and Airtable is free to use submitted suggestions or feedback for any purpose.

5 Likes

I have published a project scheduler that will automatically create project tasks based on a start date, duration, and template of task dependencies. It makes working with the Gantt and Flow Chart Blocks scalable for large portfolios of projects. I hope people find it useful.

Loom video here:

Published to the universe here:

4 Likes

Thanks @Howard_Shaw, can’t wait to take a look!

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, {
            "Suggested Link to Master": matchedIds
        }
    );
}

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.

Edit: Link to Wikipedia page:

Thanks

JB

5 Likes

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

Same Table Record Link Backlinks:

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.

Video demo

Code

3 Likes

Lookup first/last linked record

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.

Video demo

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.

video

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 linksPerRecord = 1;
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});
}

if (listOfRecordsToPull.length < linksPerRecord) {
    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');
}

/* adds {linksPerRecord} number of links per record in table to write to */
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 :woman_shrugging:

Lots of great entries this time!

Nice work everyone! :tada:

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! :pray:t2:

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"]