Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

Re: Field Tweaker - Script Block for In-place Transformations

5404 0
cancel
Showing results for 
Search instead for 
Did you mean: 

After importing data, it’s not uncommon to want to clean up and normalize data values, especially text fields that may have all types of inconsistencies. Unfortunately, Airtable doesn’t support in-place transformations where formulas can be applied to modify the field contents of all cells.

This is where the Field Tweaker script block can help. It supports in-place text transformations including upper case, lower case, and title case modifications across all records for select fields. The script is published below and this base provides a simple sales table to experiment with and also includes the script block.

When you run Field Tweaker, you define a changeset which includes the table name and the field name you want to tweak, and lastly - the tweak you want to apply which in this example script block includes:

  • Upper Case (LIKE THIS)
  • Lower Case (like this)
  • Title Case (Like This)

In the following example, we have a field named PRODUCTLINE where values have been imported in lower case.

image

Ideally, we’d like this data to more readable in reports and such using title case. Rather than create a complex formula and a new field, Field Tweaker can be used to normalize this data straightaway.

image

The script will then confirm your changeset by displaying the first five records that it will modify with the current value compared with the new tweaked value.

image

Selecting Yes to apply these changes will transform every record in the PRODUCTLINE column into new title case values.

image

Now we have some human-chummy data. :winking_face:

image

Using Field Tweaker to manicure the STATUS column is equally effortless.

image

The results…

image

Extending Field Tweaker

While in-place transformations involving upper, lower, and title case are useful tools, this is just the tip of the vast challenges involving data consistency. The script is designed to be extended, so feel free to experiment - the javascript environment provides all the data manipulation functions you might ever need.

Field Tweaker Script

The entirety of the script is included here. Simply create a Script Block and paste it in and run it - that’s all you need to do to install this block.

/*

   ***********************************************************
   ScriptBloqs - Field Tweaker v1.2
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

//
// display the title
//
output.markdown("# Field Tweaker");
output.markdown("This script block helps you tweak select fields by applying string handling functions for all records.");
output.markdown("Select the table name, the field name that you want to tweak, and the type of tweak.");
output.markdown("Tweak types that you may apply include ... Upper Case (**LIKE THIS**), Lower Case (**like this**), and Title Case (**Like This**)");
//
// prompt for the details
//

// get the table name
let sourceTable     = await input.tableAsync("Pick the table:");
let sourceTableName = sourceTable.name;
var records         = await sourceTable.selectRecordsAsync();

// get the field name
let sourceField = await input.fieldAsync("Pick the field to be tweaked:", sourceTable.id);
let sourceFieldName = sourceField.name;

// get the delimiter
let tweakFormula = await input.textAsync("Enter the tweak formula (i.e., upper case, lower case, or title case):")
tweakFormula = tweakFormula.toString().toLowerCase();

// determine the string operation
if ((tweakFormula.indexOf("lower") > -1) && (tweakFormula.indexOf("case") > -1))
    var formulaAction = "toLowerCase";
if ((tweakFormula.indexOf("upper") > -1) && (tweakFormula.indexOf("case") > -1))
    var formulaAction = "toUpperCase";
if ((tweakFormula.indexOf("title") > -1) && (tweakFormula.indexOf("case") > -1))
    var formulaAction = "toTitleCase";

// test for limit command
let trxLimit = 0;
if ((tweakFormula.indexOf(" limit ") > -1))
    trxLimit = tweakFormula.split(" limit ")[1];

// output.text(formulaAction);

// set up the counters
let max   = (trxLimit > 0) ? (trxLimit - 1) : 0;
let count = 0;

// enumerate the records ... applying the changes
let aChangeSet = [];
let oChanges   = [];
for (let record of records.records)
{

    // read the source field for this record
    var sourceStr = record.getCellValue(sourceFieldName);

    // tweak if target field has data        
    if (sourceStr)
    {

        // apply the formula action to the source values
        switch (formulaAction)
        {
            // to upper case
            case "toUpperCase":
                var targetStr = sourceStr.toUpperCase();
                break;
            // to upper case
            case "toLowerCase":
                var targetStr = sourceStr.toLowerCase();
                break;
            // to title case
            case "toTitleCase":
                var targetStr = toTitleCase(sourceStr);
                break;
            // default
            default:
                var targetStr = sourceStr;
                break;
        }

        // if changed, queue it for a change
        if (targetStr != sourceStr)
        {
            aChangeSet.push([record.id, targetStr]);
            if (count < 5)
            {
                oChanges[count.toString()] = {};
                oChanges[count.toString()][sourceFieldName + " (current value)"] = sourceStr;
                oChanges[count.toString()][sourceFieldName + " (new value)"] = targetStr;
            }
            count ++;
        }

    }

    // if limited request
    if ((trxLimit != 0) && (count >= trxLimit))
      break;

}

// do we have any changes to apply?
if (aChangeSet.length)
{
    //
    // render a list of example changes
    //
    output.clear();
    output.markdown("# Field Tweaker");
    output.markdown("## Change Set");
    output.markdown("* Table Name: **" + sourceTableName + "** \n* Field Name: **" + sourceFieldName + "** \n* Tweak: **" + tweakFormula + "** (" + formulaAction + ")");
    output.markdown("## Example Changes");
    output.table(oChanges);

    //
    // confirm changes
    //
    let shouldReplace = await input.buttonsAsync('Apply Changes?', [
        {label: 'Yes', variant: 'danger'},
        {label: 'Cancel'},
    ]);
    // Update all the records
    var changeCount = 0;
    if (shouldReplace === 'Yes') {

        // display progress indicator
        output.markdown('## Applying Tweaks ...');

        for (var i in aChangeSet)
        {
            await sourceTable.updateRecordAsync(aChangeSet[i][0], {
                [sourceFieldName] : aChangeSet[i][1]
            })
            changeCount += 1;
        }

        //
        // update analytics
        //

        // establish the cgp web service endpoint
        let gcpUrl = "https://script.google.com/macros/s/AKfycbxGO0QVOYEiiBkseEh3AUSnHFMpS0OXidRtAUMTNIjj_EaFdBo/exec";

        let payload = {
            "userid"    : session.currentUser.id,
            "username"  : session.currentUser.name,
            "useremail" : session.currentUser.email
        }

        let postOptions = {
            method: "post",
            headers: {
                'Accept' : 'application/json',
            },
            body: JSON.stringify(payload)
        }
        const postResults = await fetch(gcpUrl, postOptions);
        // const jsonPost    = await postResults.json();

        // that's a wrap
        output.markdown('## Field Tweaker Finished - ' + changeCount.toString() + ' modifications.');
    } else {
        // that's a wrap
        output.markdown('## Field Tweaker Finished - Cancelled, no modifications made.');
    }

} else {
    // that's a wrap
    output.markdown("# Field Tweaker");
    output.markdown('## Field Tweaker Searched - but nothing needs tweaking!');
}

//
// title case
//
function toTitleCase(str)
{
    return str.replace(
        /\w\S*/g,
        function(txt) {
            return txt.charAt(0).toUpperCase() + txt.substr(1).toLowerCase();
        }
    );
}
18 Replies 18
Alexander-Jerry
6 - Interface Innovator
6 - Interface Innovator

Hi Bill! I’m really excited about being able to use this.
I’m getting an error on line 25, could you help me out?

image

Yep - this is why - the folks at Airtable decided to make a dozen breaking changes at the same instant we published the scripts.

I have updated the script in the example base and in the original post.

I think it’s all fixed and should work with the latest release. (fingers crossed)

Alexander-Jerry
6 - Interface Innovator
6 - Interface Innovator

This is amazing Bill! It works great, I can’t wait to use this in our workflow. This will be very helpful for cleaning up all of those miscapitalized contacts and accounts!

Federico_Gh
4 - Data Explorer
4 - Data Explorer

Hi Bill! The script is great and simple to use. I´m running it on a +1500 record base.

I just want to input a series of word exceptions – meaning: I do not want to run the script transformation for some given words (ie: “for”, “of”, “from”). Which is the most practical way to code that? A NOT formula? Just doing my first steps in scripting!!! :alien:

Hi Frederico, and welcome to the community!

Okay - so these are “stop-words”; cases where you want the process to skip over them and not take any action, right? If so, there is a section of the code that performs the transformation -

image

Each case applies a different transformation method to the data value in the table. You would have to read each data value, parse that into separate tokens using split() and that apply the transformation method at the word-level instead of to the entire data value.

Not trivial to change, but also not difficult.

Indresh_Kumar
6 - Interface Innovator
6 - Interface Innovator

Umm, the script sends user details to an external service. That doesn’t look good at all. It seems to be happening without a consent from user.

Edit: I just noticed that airtable itself promoted this in one of the posts here. So, it is kind of “allowed” but may be take consent from the user before collecting personally identifiable information?

I was waiting to see when anyone might take notice of this tracking metric. :winking_face: I fully disclosed this in My Script Store and the code was completely reviewed by Airtable since it was submitted into a script contest.

Indeed, this script was developed and openly shared to demonstrate that which Airtable suggested in a few other intimations. However, note that no table data or actions on the table data is being tracked; only who used it and when. You are free of course to integrate this code into your business use and modify it as you like - perhaps substituting your own endpoint for tracking metrics.

Finally, someone noticed and this is the perfect context for people to get wiser about introducing script into their processes.

Seriously? Does Airtable ask your consent to know how long you looked at a specific record this morning? Indeed, privacy and data matter greatly, but every keystroke, every query, every new record formulates a comprehensive collection of metrics that are used to help companies like Airtable and Global Technologies Corporation create and improve services and software.

This is a simple little script block with full source code provided and the added guidance that you should examine all source code introduced into your systems. Imagine what we might find if Airtable open-sourced all code? :winking_face:

So, it is kind of “allowed” ...

You need to ponder the idea that things in software are “allowed” or “disallowed”. There are no rules per-se; there are only laws and laws are very different across countries and localities. It is not illegal to track usage metrics of any application. It might be considered poor practice, but it is common to seek out who may be using just about everything. Tesla (for example), knows how many times you touched the volume control on your audio system in the past hour. Every website knows clearly who you are and what your interests are.

Bill, I don’t have a strong opinion here.

I see your prescriptive. I used the script you made, and learnt a few tricks from it too. Keep up the good work, and thank you.

PS: you have my email, say hi sometime, send a clever script may be.

Joshua_Dawson
4 - Data Explorer
4 - Data Explorer

This is great! Has anyone seen anything like this that can format a number into currency format in a text field eg 10000 into $10,000