Clean up your Tables Quick and Easy!
5
2929
1
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;
}
