Clean up your Tables Quick and Easy!

Sometimes you import some data that looks like this:

But you want table data that looks like this:

This script does that:

let settings = input.config({
    title:'Table Optimizer',
    description: "Converts data that's stored over many fields, into data that's stored in one field with multiple parameters in a new table",
    items: [
        input.config.table('sourceTable',{
            label: 'Source Table',
            description: 'The originating table'
            }),

        input.config.text('fieldsConvert',{
            label: 'Fields to Convert',
            description: "Type out the fields you'd like to convert separated by a comma. Example: Field 1,Field 2,Field 3. All convert fields must be of the same type."
        }),

        input.config.table('destinationTable',{
            label: 'Destination Table',
            description: 'The desitination table'
        }),

        input.config.field('field1',{
            label: 'Data Field',
            parentTable: 'destinationTable',
            description: 'Field where the table data will go. Must match field type of Conver Fields',
        }),

        input.config.field('field2',{
            label: 'Field Name Field',
            parentTable: 'destinationTable',
            description: 'Field where the original field names will be transposed',
        }),

        input.config.field('field3',{
            label: 'Linked Record Field',
            parentTable: 'destinationTable',
            description: 'Linked record which will link back to the desination table',
        }),
    ]

});

let sourceTable = settings.sourceTable
let fields = settings.fieldsConvert.split(',')
let destinationTable = settings.destinationTable
let fieldNameField = settings.field2.name
let dataField = settings.field1.name
let linkedField = settings.field3.name

let query = await sourceTable.selectRecordsAsync({fields: fields})
let records = query.records

let arr = []

for (let field of fields){

    
    if( sourceTable.getField(field).type != settings.field1.type){

        output.markdown('## 🚫 Error: Please change your Data Field-Type to match your Convert Field Types')
        return

    } else{

        arr.push(
            
            records.filter( record => record.getCellValue(field)).map(record => Array.isArray(record.getCellValue(field)) 
            
            ? 
            
            record.getCellValue(field).map( element => 
            
            ({fields:{
                [linkedField]: [record],
                [dataField]: [element],
                [fieldNameField]: field
            }}))
            
            : 
            
            ({fields:{
                [linkedField]: [record],
                [dataField]: record.getCellValue(field),
                [fieldNameField]: field
            }}) )
            
        
        )
    }

}
await batchAnd('Create',destinationTable,arr.flat())




/*
    Use this function to perform 'Update', 'Create', or 'Delete'
    async actions on batches of records that could potentially 
    more than 50 records.

    ::PARAMETERS::
    action = string; one of 3 values:
           - 'Update' to call table.updateRecordsAsync()
           - 'Create' to call table.createRecordsAsync()
           - 'Delete' to call table.deleteRecordsAsync()

    table = Table; the table the action will be performed in

    records = Array; the records to perform the action on
            - Ensure the record objects inside the array are
            formatted properly for the action you wish to
            perform

    ::RETURNS::
    recordsActedOn = integer, array of recordId's, or null; 
                   - Update Success: integer; the number of records processed by the function
                   - Delete Success: integer; the number of records processed by the function
                   - Create Success: array; the id strings of records created by the function
                   - Failure: null;
*/
async function batchAnd(action, table, records) {
    let recordsActedOn;

    switch (action) {
        case 'Update':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.updateRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            };
            break;
        
        case 'Create':
            recordsActedOn = [];
            while (records.length > 0) {
                let recordIds = await table.createRecordsAsync(records.slice(0, 50));
                recordsActedOn.push(...recordIds)
                records = records.slice(50);
            };
            break;

        case 'Delete':
            recordsActedOn = records.length;
            while (records.length > 0) {
                await table.deleteRecordsAsync(records.slice(0, 50));
                records = records.slice(50);
            }
            break;

        default:
            output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
            recordsActedOn = null;
    }
    return recordsActedOn;
}
3 Likes

Thanks God we have you back… (your last post was June 25)

1 Like