Splitting one Cell into several rows
Hello, I am working on a database where someone may have to created multiple items and instead of having a bunch of columns I was wonder if it is possible to split the items from one cell into another row.
For example. I have a request from a team to build out some new content. We will be creating 3 new courses and we plan to track all new course created on another table for our "course catalog" So I was hoping to automate it so that if there is content listed under the created each bullet point would it's own row on the course catalog. - is this possible?
From memory it's possible. I was planning to split out my Markdown text in my 2022 work diary, but I ended up to a complete redesign so never got around to the project. But to help with your question, firstly, you'll need to understand the basics of the Scripting App Extension (which is similar to the Scripting in Automation, there are subtle differences however).
My app below takes data from a record, then pastes it into another record - in this case, just the "Notes" field.
let table = base.getTable("Split Text");
let sourceRecord = await input.recordAsync('Select a record to use', table);
if (!sourceRecord) throw ('No record was selected');
let targetRecord = await input.recordAsync('Select a record to overwrite', table);
if (!targetRecord) throw ('No record was selected');
await table.updateRecordAsync(targetRecord.id, {
"Notes" : sourceRecord.getCellValueAsString("Notes")
});
Here's the result, we've duplicated Bill French!
But how do we handle the text within a cell, split it up, and then create multiple records from that? Does Markdown even copy? Let's find out!
Let's replace one of the Bills with a more sensible option, 3 cats - add in console logs too so we can see more detail of the text that's copying.
And cool, it looks like just copying/pasting text does carry the Markdown over (at least for bullitin points);
Looking at the console log, I guess we need to split the string up on the dash symbol, to create an array. We'll update the original points to different emojis to make the split more clear. We can perhaps use the magic of Regex to split the string into an array;
let myArray = sourceRecord.getCellValueAsString("Notes").match(/^-.+/gm);
console.info(myArray)
Finally, let's reconfigure that array by using map() so that it fits the format needed by the Airtable .createRecordsAsync(). Here is the complete code;
let table = base.getTable("Split Text");
let sourceRecord = await input.recordAsync('Select a record to use', table);
if (!sourceRecord) throw ('No record was selected');
console.log(sourceRecord.getCellValueAsString("Notes"))
let myArray = sourceRecord.getCellValueAsString("Notes").match(/^-.+/gm);
console.info(myArray)
if (!myArray) throw ('No record was selected');
let myNewRecords = myArray.map( splitNotes => ({
fields : {
"Notes" : splitNotes
}
}))
console.log("Creating records 🤪")
await table.createRecordsAsync(myNewRecords);
And there we have it, 3 new records split out from the original;
PS: For science;
