Simple Data Scrubber

This script takes a date value stored as a string from one field and scrubs it for use in a real date field. In practice, it might be easier to clean up this date column easier with a formula field, but then you’d have another field with dependencies. While formulas are fine for simple tasks, this little block can implement very complex string handling and math computations.

To see the Distance Computations Function script see the original source.

Script Code

							/*

   ***********************************************************
   ScriptBloqs - DataScrubber
   Copyright (c) 2020 by Global Technologies Corporation
   ALL RIGHTS RESERVED
   ***********************************************************
   
*/

// configure data scrubber
let targetTableName = "Sales Data";
let sourceFieldName = "ORDERDATE";
let targetFieldName = "ORDER_DATE";
let sourcePattern   = " 0:00";
let sourcePatternI  = 0;

// display app title
output.markdown('# DataScrubber');

output.markdown('DataScrubber working...');

let targetTable = base.getTable(targetTableName);

let result = await targetTable.selectRecordsAsync();

// set a counter for testing
var count = 0;

// iterate across all the records
for (let record of result.records)
{

    // read the target field
    var targetFieldValue = record.getCellValue(targetFieldName);

    // scrub if target field is empty
    if (!targetFieldValue)
    {
        
        // get the value of the source field
        var sourceStr = record.getCellValue(sourceFieldName);

        var targetStr = toIsoDate(sourceStr.split(sourcePattern)[sourcePatternI]);
        // output.markdown(targetStr);

        // increment the count
        count += 1;

        await targetTable.updateRecordAsync(record.id, {
            [targetFieldName] : targetStr
        })
        
        // test only the first 10 hits
        if (count > 10)
          break;

    }

} 

output.markdown('DataScrubber Finished - ' + count + ' updates.');

//
// to iso date str
//
function toIsoDate(dateStr)
{
    var date = new Date(dateStr);
    var year  = date.getFullYear();
    var month = date.getMonth() + 1;
    var dt    = date.getDate();
    dt = (dt < 10) ? '0' + dt : dt;
    month = (month < 10) ? month = '0' + month : month;
    return(year + '-' + month + '-' + dt);
}