Mar 01, 2020 11:41 PM
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
Mar 02, 2020 12:38 AM
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": ""
})
}
}
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
Mar 02, 2020 01:17 AM
Hi JB, Incredible! This works like a charm. Thanks so much!
Mar 02, 2020 05:59 AM
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.
Details
fieldHistory
fieldDetails
fieldOne 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.
Mar 02, 2020 10:50 PM
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
Mar 03, 2020 04:45 AM
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.
Mar 03, 2020 07:40 AM
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.
Mar 03, 2020 07:42 AM
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.
Mar 12, 2020 12:47 PM
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
Mar 13, 2020 07:44 AM
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