Skip to main content

Hi guys,



Need your help in scripting. I want to concatenate month and templateName to another table using scripts as individual records.



My desired output is e.g.


templateMonth; templateName



2022-01; HD-HD1460, TR144 | 1 x RM-HD


2022-01; HD-WHITE | 5 x RM-HD, 1 x RM-WHITE




How do I achieve this using a button?



Thanks.



A button in a field will pass a single record’s ID to a script. This is designed for situations where you only want the script to run on a single record. Is that what you want, or do you want it to run on all records in a table/view?



The script itself sounds fairly straightforward. The core steps for a single record would be:





  • Get the value from {templateMonth}



  • Get the values from {templateName}



  • For each template name, build a string that merges the template month in front of it and create a new record with that string as the primary field value.




The optimal way of creating the records themselves would be to create an array to contain all new record data, then create the new records as a batch process instead of making them one-by-one.



If you look through the Scripting app documentation (in the bottom pane after adding the app to a dashboard), you’ll find lots of examples that can get you started. I also recommend having a good JavaScript reference on hand. If you get stuck, feel free to post and ask for help. If you do, please include as much of the actual code as you can, formatted with the preformatted text option in the forum toolbar: </>




A button in a field will pass a single record’s ID to a script. This is designed for situations where you only want the script to run on a single record. Is that what you want, or do you want it to run on all records in a table/view?



The script itself sounds fairly straightforward. The core steps for a single record would be:





  • Get the value from {templateMonth}



  • Get the values from {templateName}



  • For each template name, build a string that merges the template month in front of it and create a new record with that string as the primary field value.




The optimal way of creating the records themselves would be to create an array to contain all new record data, then create the new records as a batch process instead of making them one-by-one.



If you look through the Scripting app documentation (in the bottom pane after adding the app to a dashboard), you’ll find lots of examples that can get you started. I also recommend having a good JavaScript reference on hand. If you get stuck, feel free to post and ask for help. If you do, please include as much of the actual code as you can, formatted with the preformatted text option in the forum toolbar: </>


Hi Justin,



I am able to iterate through multi select but how to join it with concat / join of another column?





I want to create all combined records in another table.



if (templateName) {

// Creates record names from 'templateName' multiple select field, if any

for (let item of templateName) {

template.push({

'Name': item.name

})

}

length = template.length



// Preview records to create, prompt user to confirm creation

output.markdown('### Create ' + length + ' records for **' + r.name + '**?');



output.table(template);

await input.buttonsAsync('', '{ label: 'Create records', value: 'Create records', variant: 'primary' }]);



// Create records

let dToCreate = e];



for (let i = 0; i < length; i++) {

let name = templateli].Name



dToCreate.push({

fields: {

s.destinationField.id]: name,

s.projField.id]: d{ id: r.id }]

}

})



};


Hi Justin,



I am able to iterate through multi select but how to join it with concat / join of another column?





I want to create all combined records in another table.



if (templateName) {

// Creates record names from 'templateName' multiple select field, if any

for (let item of templateName) {

template.push({

'Name': item.name

})

}

length = template.length



// Preview records to create, prompt user to confirm creation

output.markdown('### Create ' + length + ' records for **' + r.name + '**?');



output.table(template);

await input.buttonsAsync('', [{ label: 'Create records', value: 'Create records', variant: 'primary' }]);



// Create records

let dToCreate = [];



for (let i = 0; i < length; i++) {

let name = template[i].Name



dToCreate.push({

fields: {

[s.destinationField.id]: name,

[s.projField.id]: [{ id: r.id }]

}

})



};




Concatenating strings in JavaScript can be done using the + operator, similar to how you built a string for output in your script sample. Store the value of {templateMonth} in one variable, and add it to the value of each of the {templateName} items in a loop, using whatever separating character(s) you wish.




Concatenating strings in JavaScript can be done using the + operator, similar to how you built a string for output in your script sample. Store the value of {templateMonth} in one variable, and add it to the value of each of the {templateName} items in a loop, using whatever separating character(s) you wish.


let recToCreate = s.tempField.options?.choices.length;

let templateName = r.getCellValue(s.tempField.name);

let monthName = r.getCellValue(s.monthField.name);

let combinedArray = Object.values(templateName.forEach(item=>console.log(`${monthName}; ${item}`)));



However my output of multiselect is an object. How do I get through to the specific field in an object?




let recToCreate = s.tempField.options?.choices.length;

let templateName = r.getCellValue(s.tempField.name);

let monthName = r.getCellValue(s.monthField.name);

let combinedArray = Object.values(templateName.forEach(item=>console.log(`${monthName}; ${item}`)));



However my output of multiselect is an object. How do I get through to the specific field in an object?






To see the output, use this modified version of the last line above:



templateName.forEach(item => console.log(`${monthName}; ${item.name}`));



To capture the results in a new array, use this version:



let combinedArray = templateName.map(item => `${monthName}; ${item.name}`)




To see the output, use this modified version of the last line above:



templateName.forEach(item => console.log(`${monthName}; ${item.name}`));



To capture the results in a new array, use this version:



let combinedArray = templateName.map(item => `${monthName}; ${item.name}`)




Thanks for for your guidance, now I manage to create records





However, it’s somehow not written to another table





// Create records

let dToCreate = =];



for (let i = 0; i < length; i++) {

let name = combinedArrayai].Name

dToCreate.push({

fields: {

s.destinationField.id]: name,

s.projField.id]: :{ id: r.id }]

}

})

};


Thanks @Justin_Barrett , I finally manage to do it. You teach me how to fish :grinning_face_with_big_eyes:



// Script settings

let s = input.config({

title: 'Create records by concatenating two columns (single text and multi-select) field options',

description: 'Creates 1 record in another table for each option in a multiple select field in the source table, and links the new records back to the source record.',

items: [

// Source table select

input.config.table('tableSource', {

label: 'πŸ‚ Table with existing records'

}),

// Source table: Multiple select field with deliverables

input.config.field('tempField', {

parentTable: 'tableSource',

label: 'πŸ‚ Multiple select field with names of records to create',

}),

// Source table: Multiple select field with deliverables

input.config.field('monthField', {

parentTable: 'tableSource',

label: 'πŸ‚ Single text or field with month name to create',

}),

// Destination table select

input.config.table('tableDest', {

label: '🌱 Table to create new records in'

}),

// Destination table: Name or title field

input.config.field('destinationField', {

parentTable: 'tableDest',

label: '🌱 Deliverable name or title field in destination table',

}),

// Destination table: Linked record field (back to the Source table record)

input.config.field('projField', {

parentTable: 'tableDest',

label: '🌱 Linked record field (links to table with existing records)',

}),

]

});

// You can use a button field that points to this script to run it for specific records,

// or you can run the script directly, in which case the following prompts the user to pick a record

let r = await input.recordAsync('Pick a record', s.tableSource);

while (!r) {

output.text('You must select a record.');

r = await input.recordAsync('Pick a record', s.tableSource);

}



// Gets the desired # of records to create, and deliverable names, based on the fields chosen in the script settings

let recToCreate = s.tempField.options?.choices.length;

let templateName = r.getCellValue(s.tempField.name);

let monthName = r.getCellValue(s.monthField.name);

templateName.forEach(item => console.log(`${monthName}; ${item.name}`));

let combinedArray = templateName.map(item => `${monthName}; ${item.name}`);



console.log(combinedArray);

output.table(combinedArray);



await input.buttonsAsync('', .{ label: 'Create records', value: 'Create records', variant: 'primary' }]);

let length = 0;

length = combinedArray.length



let delivNames = ];



for (let item of combinedArray) {

delivNames.push({

'Name': item

})

}

length = delivNames.length



// Create records

let dToCreate = ];



for (let i = 0; i < length; i++) {

let name = delivNames i].Name

dToCreate.push({

fields: {

>s.destinationField.id]: name,

>s.projField.id]: [{ id: r.id }]

}

})

};





// Batches the creation

while (dToCreate.length > 0) {

await s.tableDest.createRecordsAsync(dToCreate.slice(0, 50));

dToCreate = dToCreate.slice(50);

}

// Output confirmation for the user

output.markdown(`

${length} records created βœ…

`);






Thanks @Justin_Barrett , I finally manage to do it. You teach me how to fish :grinning_face_with_big_eyes:



// Script settings

let s = input.config({

title: 'Create records by concatenating two columns (single text and multi-select) field options',

description: 'Creates 1 record in another table for each option in a multiple select field in the source table, and links the new records back to the source record.',

items: [

// Source table select

input.config.table('tableSource', {

label: 'πŸ‚ Table with existing records'

}),

// Source table: Multiple select field with deliverables

input.config.field('tempField', {

parentTable: 'tableSource',

label: 'πŸ‚ Multiple select field with names of records to create',

}),

// Source table: Multiple select field with deliverables

input.config.field('monthField', {

parentTable: 'tableSource',

label: 'πŸ‚ Single text or field with month name to create',

}),

// Destination table select

input.config.table('tableDest', {

label: '🌱 Table to create new records in'

}),

// Destination table: Name or title field

input.config.field('destinationField', {

parentTable: 'tableDest',

label: '🌱 Deliverable name or title field in destination table',

}),

// Destination table: Linked record field (back to the Source table record)

input.config.field('projField', {

parentTable: 'tableDest',

label: '🌱 Linked record field (links to table with existing records)',

}),

]

});

// You can use a button field that points to this script to run it for specific records,

// or you can run the script directly, in which case the following prompts the user to pick a record

let r = await input.recordAsync('Pick a record', s.tableSource);

while (!r) {

output.text('You must select a record.');

r = await input.recordAsync('Pick a record', s.tableSource);

}



// Gets the desired # of records to create, and deliverable names, based on the fields chosen in the script settings

let recToCreate = s.tempField.options?.choices.length;

let templateName = r.getCellValue(s.tempField.name);

let monthName = r.getCellValue(s.monthField.name);

templateName.forEach(item => console.log(`${monthName}; ${item.name}`));

let combinedArray = templateName.map(item => `${monthName}; ${item.name}`);



console.log(combinedArray);

output.table(combinedArray);



await input.buttonsAsync('', .{ label: 'Create records', value: 'Create records', variant: 'primary' }]);

let length = 0;

length = combinedArray.length



let delivNames = ];



for (let item of combinedArray) {

delivNames.push({

'Name': item

})

}

length = delivNames.length



// Create records

let dToCreate = ];



for (let i = 0; i < length; i++) {

let name = delivNames i].Name

dToCreate.push({

fields: {

>s.destinationField.id]: name,

>s.projField.id]: [{ id: r.id }]

}

})

};





// Batches the creation

while (dToCreate.length > 0) {

await s.tableDest.createRecordsAsync(dToCreate.slice(0, 50));

dToCreate = dToCreate.slice(50);

}

// Output confirmation for the user

output.markdown(`

${length} records created βœ…

`);






Awesome! Glad to hear that you were able to get it working. You’re now well on your way to becoming a programming addict. :winking_face:


Awesome! Glad to hear that you were able to get it working. You’re now well on your way to becoming a programming addict. :winking_face:


I come from accounting/finance background and I find programming is indeed addictive once you have grasped some of the key concepts. I think it’s time to sign up codecademy javascript.


I come from accounting/finance background and I find programming is indeed addictive once you have grasped some of the key concepts. I think it’s time to sign up codecademy javascript.


Another great option for learning JavaScript online is www.freecodecamp.org


Thanks @Justin_Barrett , I finally manage to do it. You teach me how to fish :grinning_face_with_big_eyes:



// Script settings

let s = input.config({

title: 'Create records by concatenating two columns (single text and multi-select) field options',

description: 'Creates 1 record in another table for each option in a multiple select field in the source table, and links the new records back to the source record.',

items: [

// Source table select

input.config.table('tableSource', {

label: 'πŸ‚ Table with existing records'

}),

// Source table: Multiple select field with deliverables

input.config.field('tempField', {

parentTable: 'tableSource',

label: 'πŸ‚ Multiple select field with names of records to create',

}),

// Source table: Multiple select field with deliverables

input.config.field('monthField', {

parentTable: 'tableSource',

label: 'πŸ‚ Single text or field with month name to create',

}),

// Destination table select

input.config.table('tableDest', {

label: '🌱 Table to create new records in'

}),

// Destination table: Name or title field

input.config.field('destinationField', {

parentTable: 'tableDest',

label: '🌱 Deliverable name or title field in destination table',

}),

// Destination table: Linked record field (back to the Source table record)

input.config.field('projField', {

parentTable: 'tableDest',

label: '🌱 Linked record field (links to table with existing records)',

}),

]

});

// You can use a button field that points to this script to run it for specific records,

// or you can run the script directly, in which case the following prompts the user to pick a record

let r = await input.recordAsync('Pick a record', s.tableSource);

while (!r) {

output.text('You must select a record.');

r = await input.recordAsync('Pick a record', s.tableSource);

}



// Gets the desired # of records to create, and deliverable names, based on the fields chosen in the script settings

let recToCreate = s.tempField.options?.choices.length;

let templateName = r.getCellValue(s.tempField.name);

let monthName = r.getCellValue(s.monthField.name);

templateName.forEach(item => console.log(`${monthName}; ${item.name}`));

let combinedArray = templateName.map(item => `${monthName}; ${item.name}`);



console.log(combinedArray);

output.table(combinedArray);



await input.buttonsAsync('', .{ label: 'Create records', value: 'Create records', variant: 'primary' }]);

let length = 0;

length = combinedArray.length



let delivNames = ];



for (let item of combinedArray) {

delivNames.push({

'Name': item

})

}

length = delivNames.length



// Create records

let dToCreate = ];



for (let i = 0; i < length; i++) {

let name = delivNames i].Name

dToCreate.push({

fields: {

>s.destinationField.id]: name,

>s.projField.id]: [{ id: r.id }]

}

})

};





// Batches the creation

while (dToCreate.length > 0) {

await s.tableDest.createRecordsAsync(dToCreate.slice(0, 50));

dToCreate = dToCreate.slice(50);

}

// Output confirmation for the user

output.markdown(`

${length} records created βœ…

`);






Hi,



I can repeat once again - Airtable is a great playground to learn programming, where you can use most efficient way to learn - to create scripts, that solve real-life tasks, by your own hands. Thanks to pro developers who created here scripts simple enough to be understandable for beginners and with a lots of comments.



I would suggest to learn array transforming functions, where you can use some common reusable pieces.


I like to learn from experts here. Now I prefer to use arrow functions, like let double=x=>x*2, use const almost everywhere, if this variable not expected to be mutated, and reuse much of functions in different scripts.


Of course it’s all β€œa matter of taste”.



I always had difficulties with correct format to update or create records - because started work in Airtable as DBA, without clear understanding of arrays and objects. At first, even β€˜push’ was a great improvement for me



For example, instead of





// Create records

let dToCreate = ];



for (let i = 0; i < length; i++) {

let name = delivNames i].Name

dToCreate.push({

fields: {

{s.destinationField.id]: name,

,s.projField.id]: { id: r.id }]

}

})

};



You can use something like:



let create=el=>({ fields: {ls.destinationField]: el.name,             ns.projField.id]:  { id: r.id }] }})

let dToCreate=delivnames.map(create)


Hi,



I can repeat once again - Airtable is a great playground to learn programming, where you can use most efficient way to learn - to create scripts, that solve real-life tasks, by your own hands. Thanks to pro developers who created here scripts simple enough to be understandable for beginners and with a lots of comments.



I would suggest to learn array transforming functions, where you can use some common reusable pieces.


I like to learn from experts here. Now I prefer to use arrow functions, like let double=x=>x*2, use const almost everywhere, if this variable not expected to be mutated, and reuse much of functions in different scripts.


Of course it’s all β€œa matter of taste”.



I always had difficulties with correct format to update or create records - because started work in Airtable as DBA, without clear understanding of arrays and objects. At first, even β€˜push’ was a great improvement for me



For example, instead of





// Create records

let dToCreate = o];



for (let i = 0; i < length; i++) {

let name = delivNamesdi].Name

dToCreate.push({

fields: {

s.destinationField.id]: name,

s.projField.id]: i{ id: r.id }]

}

})

};



You can use something like:



let create=el=>({ fields: { s.destinationField]: el.name,              s.projField.id]: i{ id: r.id }] }})

let dToCreate=delivnames.map(create)


That is handy! Thanks for the tips!


Reply