Skip to main content

Non-developer type here! My team is using Airtable as a task management tool Each week we run a report, which includes a long text field (“Details Field”) with details on the work performed on a specific task during that week. To retain a record of the content that’s appeared in the Details Field, I wonder if it would be possible to write a script that would copy and clear the values of the Details Field and cumulatively add them to a field (“History Field”). The History Field would then eventually contain a list of all the updates that’s been put into the Details Field over time (and ideally with date stamps to next to the text that was copied at a given time).



Thanks so much for your feedback on this!



Jens

Hi @Jens_Schott_Knudsen1 - try this script:



// set the table

let notesTbl = base.getTable("Notes");

// get the table records

let notes = await notesTbl.selectRecordsAsync();



// loop through the records

for (let record of notes.records) {

// set variables to the record values

let notes = record.getCellValue("Notes");

let notesHistory = record.getCellValue("Notes History")

// set variable for today's date

let now = new Date().toLocaleDateString("en-GB");

// define the newNotes variable with starter text (includes the date)

let newNotes = "======= Copied " + now + " ======= ";

// only run on records which have notes

if (notes) {

output.text(`Copying notes for record ${record.name}`)

// build the newNotes value from the notes...

newNotes = newNotes + notes;

// ...and the notes history

newNotes = newNotes + notesHistory;

// update the notes history value and reset the notes value

notesTbl.updateRecordAsync(record, {

"Notes History": newNotes,

"Notes": ""

})

}

}


WARNING:



Quickly put together so please try this out on a copy of your base and check that it works for you before you put it into production!



It takes the text from the “Notes” field and prepends it to the “Notes History” field with a date stamp:







JB


Hi JB, Incredible! This works like a charm. Thanks so much!


Hello @Jens_Schott_Knudsen1 and welcome to the airtable community.





Yes, this is a good task for a script block and the general design is quite simple.





  1. Read each record’s Details field


  2. Append the content to the History field


  3. Clear the content from the Details field


  4. Rinse/repeat for all records




One issue - it would require a manual process so forgetting to run the script block would result in the weekly report containing two week’s worth of content.



The script would also need to format the concatenation perhaps with line breaks between each update for good historical reading enjoyment.


Hi @Bill.French ,



I believe you can make a quick template and put it on the Universe that takes the value from 2 cells and concatenate it into a 3rd cell, this will be very helpful to many people.



Something like













































Bill 1235 Bill 1235
French 365 French 365
Jen 9685 Jen 9685


The reason for this would be so the 3rd field is a linked field instead of a formula.



BR,


Mo


Hi @Bill.French ,



I believe you can make a quick template and put it on the Universe that takes the value from 2 cells and concatenate it into a 3rd cell, this will be very helpful to many people.



Something like













































Bill 1235 Bill 1235
French 365 French 365
Jen 9685 Jen 9685


The reason for this would be so the 3rd field is a linked field instead of a formula.



BR,


Mo




Well, yes - this might be a useful template, but that’s not the requirement (as I perceived it). @JonathanBowen seems to have nailed it.



Sidebar



I’m not convinced Universe is where script block examples are best shared. Imagine if Duracell shipped you one triple-A battery in a giant box. I think this new feature has created an opportunity for a kinder, simpler approach where entire bases are not required to share such simple code fragments.




Well, yes - this might be a useful template, but that’s not the requirement (as I perceived it). @JonathanBowen seems to have nailed it.



Sidebar



I’m not convinced Universe is where script block examples are best shared. Imagine if Duracell shipped you one triple-A battery in a giant box. I think this new feature has created an opportunity for a kinder, simpler approach where entire bases are not required to share such simple code fragments.




We recommend sharing on Universe in cases where the script relies on a specific base schema. This makes it easier for other users to see the script in context (and enables one-click install), without having to reverse engineer the relevant structure. For smaller code fragments that are more general purpose, feel free to share them as-is in code blocks on the community forum.




We recommend sharing on Universe in cases where the script relies on a specific base schema. This makes it easier for other users to see the script in context (and enables one-click install), without having to reverse engineer the relevant structure. For smaller code fragments that are more general purpose, feel free to share them as-is in code blocks on the community forum.




Yep - that makes sense. In my work, I tend to build things that intentionally avoid base dependencies, thus offering users far greater agility when applying the scripts to their own solutions.


Hi @Jens_Schott_Knudsen1 - try this script:



// set the table

let notesTbl = base.getTable("Notes");

// get the table records

let notes = await notesTbl.selectRecordsAsync();



// loop through the records

for (let record of notes.records) {

// set variables to the record values

let notes = record.getCellValue("Notes");

let notesHistory = record.getCellValue("Notes History")

// set variable for today's date

let now = new Date().toLocaleDateString("en-GB");

// define the newNotes variable with starter text (includes the date)

let newNotes = "======= Copied " + now + " ======= ";

// only run on records which have notes

if (notes) {

output.text(`Copying notes for record ${record.name}`)

// build the newNotes value from the notes...

newNotes = newNotes + notes;

// ...and the notes history

newNotes = newNotes + notesHistory;

// update the notes history value and reset the notes value

notesTbl.updateRecordAsync(record, {

"Notes History": newNotes,

"Notes": ""

})

}

}


WARNING:



Quickly put together so please try this out on a copy of your base and check that it works for you before you put it into production!



It takes the text from the “Notes” field and prepends it to the “Notes History” field with a date stamp:







JB


Hi @JonathanBowen



I was wondering if this script can be used/modified for copying a list of multi select options from one field (filled in already) to another filled in multi select field.



If so, could someone help me with this?



Thank you,


Mary Kay


Hi @JonathanBowen



I was wondering if this script can be used/modified for copying a list of multi select options from one field (filled in already) to another filled in multi select field.



If so, could someone help me with this?



Thank you,


Mary Kay


Hi @M_k - this script does it:



// set the table

let table = base.getTable("Table 1");

// get the table records

let query = await table.selectRecordsAsync();



// loop through the records

for (let record of query.records) {

// check that Multi-select 1 is not empty...

if (record.getCellValue("Multi-select 1")) {

output.text(`Copying the multi-select values for record ${record.name}`)

// now update Multi-select 2 with the array of ids in Mulit-select 1

let msCopy = await table.updateRecordAsync(record, {

"Multi-select 2": record.getCellValue("Multi-select 1")

})

}

}


In some ways, it is a bit simpler than the other script as we don’t need to take account of values in the 2nd multi-select (I assume), just copy MS1 to MS2:





If you wanted the script to be additive rather than replacing, then it would be a bit more involved as you would need to form an array of IDs from MS2, then add the IDs from MS1 to it, before saving this array to back MS2.



One thing to be aware of is that the script cannot create new multi-select values in MS2, so if you have a record in MS1 with a value of “Five” and this doesn’t exist as an option in MS2, then you will get an error. Also, nothing to stop the second multi-select being in a different table, just need to set the second table and reference this in the update.



Hope this helps



JB


Hi @M_k - this script does it:



// set the table

let table = base.getTable("Table 1");

// get the table records

let query = await table.selectRecordsAsync();



// loop through the records

for (let record of query.records) {

// check that Multi-select 1 is not empty...

if (record.getCellValue("Multi-select 1")) {

output.text(`Copying the multi-select values for record ${record.name}`)

// now update Multi-select 2 with the array of ids in Mulit-select 1

let msCopy = await table.updateRecordAsync(record, {

"Multi-select 2": record.getCellValue("Multi-select 1")

})

}

}


In some ways, it is a bit simpler than the other script as we don’t need to take account of values in the 2nd multi-select (I assume), just copy MS1 to MS2:





If you wanted the script to be additive rather than replacing, then it would be a bit more involved as you would need to form an array of IDs from MS2, then add the IDs from MS1 to it, before saving this array to back MS2.



One thing to be aware of is that the script cannot create new multi-select values in MS2, so if you have a record in MS1 with a value of “Five” and this doesn’t exist as an option in MS2, then you will get an error. Also, nothing to stop the second multi-select being in a different table, just need to set the second table and reference this in the update.



Hope this helps



JB


Hi @JonathanBowen



Thank you!



It will only be to copy Multi select field options, since I have the list created already, so this will be great.



Now, silly question. Do I just copy and paste the script into the block? Once I do this, do I need to do anything in the multi select field?



Pardon my questions, this is quite new to me.



Thank you for your patience.



Mary Kay


Hi @JonathanBowen



Thank you!



It will only be to copy Multi select field options, since I have the list created already, so this will be great.



Now, silly question. Do I just copy and paste the script into the block? Once I do this, do I need to do anything in the multi select field?



Pardon my questions, this is quite new to me.



Thank you for your patience.



Mary Kay


Hi @M_k, so the only things you should need to change are the table name (my script refers to “Table 1”) and the two multi-select field names (where I have “Multi-select 1” and “Multi-select 2”). Change these values to match your base. In fact, maybe make a copy of your base first, edit the values noted above to match your base, then try it out. Assuming all is well, then move it over to your original base (making sure the table and field names are still good).



Beyond that, there should be nothing. The script doesn’t care what the multi-select values are as long as they are the same in both MS fields.



Any probs, post back here 🙂



JB


Hi @M_k, so the only things you should need to change are the table name (my script refers to “Table 1”) and the two multi-select field names (where I have “Multi-select 1” and “Multi-select 2”). Change these values to match your base. In fact, maybe make a copy of your base first, edit the values noted above to match your base, then try it out. Assuming all is well, then move it over to your original base (making sure the table and field names are still good).



Beyond that, there should be nothing. The script doesn’t care what the multi-select values are as long as they are the same in both MS fields.



Any probs, post back here 🙂



JB


@JonathanBowen, As a follow up question, is it possible to set the colors for the multi-select options as well? It seems looking through the different field options for multi-select it should be possible, and if I had a column/array of the color options that it looked to for the choices.


Hi @M_k - this script does it:



// set the table

let table = base.getTable("Table 1");

// get the table records

let query = await table.selectRecordsAsync();



// loop through the records

for (let record of query.records) {

// check that Multi-select 1 is not empty...

if (record.getCellValue("Multi-select 1")) {

output.text(`Copying the multi-select values for record ${record.name}`)

// now update Multi-select 2 with the array of ids in Mulit-select 1

let msCopy = await table.updateRecordAsync(record, {

"Multi-select 2": record.getCellValue("Multi-select 1")

})

}

}


In some ways, it is a bit simpler than the other script as we don’t need to take account of values in the 2nd multi-select (I assume), just copy MS1 to MS2:





If you wanted the script to be additive rather than replacing, then it would be a bit more involved as you would need to form an array of IDs from MS2, then add the IDs from MS1 to it, before saving this array to back MS2.



One thing to be aware of is that the script cannot create new multi-select values in MS2, so if you have a record in MS1 with a value of “Five” and this doesn’t exist as an option in MS2, then you will get an error. Also, nothing to stop the second multi-select being in a different table, just need to set the second table and reference this in the update.



Hope this helps



JB


These are great examples, I’m seeking to use the script block to populate image url’s into image fields. I have an image url field and have been attempting to modify your code to copy / past to the image field, but it needs an array.



“Error: Can’t set cell values: invalid cell value for field ‘Image’.


Cell value has invalid format: must be an array.


Attachment field value must be an array of objects. New attachments must have property ‘url’ and optional ‘filename’. Existing attachments must be passed back unmodified.”



Do you have any ideas as to how to go about using the script block for image updating in this manner?



Thank you.


These are great examples, I’m seeking to use the script block to populate image url’s into image fields. I have an image url field and have been attempting to modify your code to copy / past to the image field, but it needs an array.



“Error: Can’t set cell values: invalid cell value for field ‘Image’.


Cell value has invalid format: must be an array.


Attachment field value must be an array of objects. New attachments must have property ‘url’ and optional ‘filename’. Existing attachments must be passed back unmodified.”



Do you have any ideas as to how to go about using the script block for image updating in this manner?



Thank you.


@Hannah_Henry



Attachment fields have a different write format. Even a single attachment must be an array of object(s).



See the Cell value write format for the multipleAttachments field type in the API reference for scripting block.





TYPEDEF


Array<{


url: string,


filename?: string,


}>





Example:



o { url: "https://dl.airtable.com/foo.jpg" } ]


@JonathanBowen thanks for the multi-select script example. I have a similar challenge, but would like to copy comma separated values from one field to another which is a linked value field (so the unlinked values become linked values). Any ideas?


@JonathanBowen thanks for the multi-select script example. I have a similar challenge, but would like to copy comma separated values from one field to another which is a linked value field (so the unlinked values become linked values). Any ideas?


Hi @Sabostar - If I understand your question correctly, using a script you want to copy a value in one field like this:



item1, item 2, item 3



to a linked field (i.e. the values item1, item 2 and item 3 are values in the primary field of the linked table). Is that right?



You can’t do this at the moment - at least not directly. Using the script block you can update a linked field but you need to know the record IDs of the records in the linked table. From the script docs the format is:





So, in code, this would be something like:



table.updateRecordAsync(record, {

MyLinkedField: :

{id: recabc12345678},

{id: recabc12345abc},

{id: recabc12345xyz}

]

});


Where the record IDs (“rec…”) above are the record IDs from the linked table.



It is possible to do this in a script - I did have something similar to this that I was working on, so will try and put this out later today. Tbh, I’m sure someone else has already put out something like this on the community, but I can’t put my hand on it. Other members might be able to point to it in the meantime.



This script from @Sam_Cederwall is also along similar lines so you might be able to tweak this for your use case:







JB


Thanks for the quick response @JonathanBowen.



Your assumption is accurate on what I am trying to achieve. This is part of a project to successfully backlink between rows in the same table. At the moment I am using a script to conduct the backlink (works fine), but then I have to join two linked fields on the same row into a single field, which I’m using a concatenate formula to achieve, but I can’t do that on a linked field, hence the last step of trying to copy and paste via a script to a linked field. Perhaps I could use a script to join the two linked fields


instead?



Hope that makes sense!


Hi @M_k - this script does it:



// set the table

let table = base.getTable("Table 1");

// get the table records

let query = await table.selectRecordsAsync();



// loop through the records

for (let record of query.records) {

// check that Multi-select 1 is not empty...

if (record.getCellValue("Multi-select 1")) {

output.text(`Copying the multi-select values for record ${record.name}`)

// now update Multi-select 2 with the array of ids in Mulit-select 1

let msCopy = await table.updateRecordAsync(record, {

"Multi-select 2": record.getCellValue("Multi-select 1")

})

}

}


In some ways, it is a bit simpler than the other script as we don’t need to take account of values in the 2nd multi-select (I assume), just copy MS1 to MS2:





If you wanted the script to be additive rather than replacing, then it would be a bit more involved as you would need to form an array of IDs from MS2, then add the IDs from MS1 to it, before saving this array to back MS2.



One thing to be aware of is that the script cannot create new multi-select values in MS2, so if you have a record in MS1 with a value of “Five” and this doesn’t exist as an option in MS2, then you will get an error. Also, nothing to stop the second multi-select being in a different table, just need to set the second table and reference this in the update.



Hope this helps



JB




I have tried the same thing and copy exactly everything, yet I still get this error



P: Can't set cell values: invalid cell value for field 'Multi-select 2'.

Could not find a choice with that ID or name

at main on line 12








I have tried the same thing and copy exactly everything, yet I still get this error



P: Can't set cell values: invalid cell value for field 'Multi-select 2'.

Could not find a choice with that ID or name

at main on line 12






Your multi-selects were probably created in a different way so the choices have different ids and/or names.



If you are sure that the target field contains all of the choices in the source field, you can map the original choices to make the array match the write format for a multi-select, using the name but not the id of the choice.


Your multi-selects were probably created in a different way so the choices have different ids and/or names.



If you are sure that the target field contains all of the choices in the source field, you can map the original choices to make the array match the write format for a multi-select, using the name but not the id of the choice.


@Mohd_Abdulatef, yes, as per @kuovonne, the error is saying that the two multi-select fields don’t have the same options, which is required for this particular script to work.


Hey Everone! Similar to @Jens_Schott_Knudsen1 above, I’m wondering if I could get some scripting help. It’s super similar to her request (but not similar enough for me to figure out on my own…).



I’m looking for a script that will take a numerical value from one “Added Quantity” and add it to a numerical value in “Current Quantity” … then clear the “Added Quantity” cell.



Is anyone able to give me a hand with this one? I can’t crack the code and am not much of a script’er 😦



Thanks!





  • Adam



Hey Everone! Similar to @Jens_Schott_Knudsen1 above, I’m wondering if I could get some scripting help. It’s super similar to her request (but not similar enough for me to figure out on my own…).



I’m looking for a script that will take a numerical value from one “Added Quantity” and add it to a numerical value in “Current Quantity” … then clear the “Added Quantity” cell.



Is anyone able to give me a hand with this one? I can’t crack the code and am not much of a script’er 😦



Thanks!





  • Adam



Hi @Adam_Finley, were you able to find a solution to this? I have a similar issue.



Thanks,


Erica


Hi @Adam_Finley, were you able to find a solution to this? I have a similar issue.



Thanks,


Erica


Hey!



I did. I ended up solving this with three automatons. The first automation takes the input value from Cell 1 and adds it to Cell 2. The second automation takes the Cell 2 value and copies it to Cell 3. The third automation clears Cell 1 and re-sets it back to zero. So when you input a value in Cel 1, at the end of the automation, it is added to Cell 3, Cell 1 is cleared and ready for a new input value 🙂


Hi @Jens_Schott_Knudsen1 - try this script:



// set the table

let notesTbl = base.getTable("Notes");

// get the table records

let notes = await notesTbl.selectRecordsAsync();



// loop through the records

for (let record of notes.records) {

// set variables to the record values

let notes = record.getCellValue("Notes");

let notesHistory = record.getCellValue("Notes History")

// set variable for today's date

let now = new Date().toLocaleDateString("en-GB");

// define the newNotes variable with starter text (includes the date)

let newNotes = "======= Copied " + now + " ======= ";

// only run on records which have notes

if (notes) {

output.text(`Copying notes for record ${record.name}`)

// build the newNotes value from the notes...

newNotes = newNotes + notes;

// ...and the notes history

newNotes = newNotes + notesHistory;

// update the notes history value and reset the notes value

notesTbl.updateRecordAsync(record, {

"Notes History": newNotes,

"Notes": ""

})

}

}


WARNING:



Quickly put together so please try this out on a copy of your base and check that it works for you before you put it into production!



It takes the text from the “Notes” field and prepends it to the “Notes History” field with a date stamp:







JB


Such a cool example and an excellent script for an Airtable Script App introduction.



Thanks to your post - Airtable scripts are all starting to make sense to me. So much so that I modified your original script to remove a “null” string bug and also amended the output with the copied notes detail.



// set the table

let notesTbl = base.getTable("Notes");

// get the table records

let notes = await base.getTable("Notes").selectRecordsAsync();



// loop through the records

for (let record of notes.records) {

// set variables to the record values

let notes = record.getCellValue("Notes");

let notesHistory = record.getCellValue("Notes History")

//clear the History cell for first entry - removes "null" string

if (notesHistory == null) {

notesHistory = ""

}

// set variable for today's date

let now = new Date().toLocaleDateString("en-GB");

// define the newNotes variable with starter text (includes the date)

let newNotes = "======= Copied " + now + " ======= ";

// only run on records which have notes

if (notes) {

output.text(`Copying notes "${notes}" for record ${record.name}`) //print notes in debug.

// build the newNotes value from the notes...

newNotes = newNotes + notes;

// ...and the notes history

newNotes = newNotes + notesHistory;

// update the notes history value and reset the notes value

notesTbl.updateRecordAsync(record, {

"Notes History": newNotes,

"Notes": ""

})

}

} here



Oh man, I’m going to lose hours… days? to Airtable scripts! So cool!


Such a cool example and an excellent script for an Airtable Script App introduction.



Thanks to your post - Airtable scripts are all starting to make sense to me. So much so that I modified your original script to remove a “null” string bug and also amended the output with the copied notes detail.



// set the table

let notesTbl = base.getTable("Notes");

// get the table records

let notes = await base.getTable("Notes").selectRecordsAsync();



// loop through the records

for (let record of notes.records) {

// set variables to the record values

let notes = record.getCellValue("Notes");

let notesHistory = record.getCellValue("Notes History")

//clear the History cell for first entry - removes "null" string

if (notesHistory == null) {

notesHistory = ""

}

// set variable for today's date

let now = new Date().toLocaleDateString("en-GB");

// define the newNotes variable with starter text (includes the date)

let newNotes = "======= Copied " + now + " ======= ";

// only run on records which have notes

if (notes) {

output.text(`Copying notes "${notes}" for record ${record.name}`) //print notes in debug.

// build the newNotes value from the notes...

newNotes = newNotes + notes;

// ...and the notes history

newNotes = newNotes + notesHistory;

// update the notes history value and reset the notes value

notesTbl.updateRecordAsync(record, {

"Notes History": newNotes,

"Notes": ""

})

}

} here



Oh man, I’m going to lose hours… days? to Airtable scripts! So cool!


@Karlstens - good spot. We can actually simplify this further with a ternary:



    let notesHistory = record.getCellValue("Notes History")

//clear the History cell for first entry - removes "null" string

if (notesHistory == null) {

notesHistory = ""

}


could be:



    let notesHistory = record.getCellValue("Notes History") ? record.getCellValue("Notes History") : ''

Reply