Field Tweaker

							
							/*

   ***********************************************************
   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();
        }
    );
}