Skip to main content

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

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


Reply