Simple Data Scrubber

							
							/*

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