Splitting one Cell into several rows

[kcrouch]

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? 

 

[Karlstens]

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;

[kcrouch]

Thank you for sharing this! I will have to familiarize myself with that extension! 

[Karlstens]

Not a problem. Discovering Airtable scripting prompted me to learn JavaScript, and it's been a thoroughly enjoyable endeavor. I highly recommend learning the JavaScript basics whilst also exploring the Airtable Scripting App and other users example scripts - it makes life much easier.

[ContentMogul]

Hi Karlstens, we're trying to split out cells also. We'd like to know if we can link the new split out records to the original "Name" field (as in the example table above) or at least copy the "Name" Field to the new records that were created?