Taking a 2D array in one record and adding a record for each value in the array

[Jordan_Crosley]

That is probably a very confusing title, I will do my best to explain what I want but I am pretty new to coding and Airtable.

I already have a Grid View with multiple fields, and I am using the Forms View to add to my Grid View. In my Form I have a multiple select option where I can add multiple pieces of equipment that will be assigned to one project.

However, for my Gantt Views I need the Primary Field to only contain one value. Right now I am using a formula to define my Primary Field.

I think what I need to do is create a script that will extract the individual values of the array and assign them their own records. You can see from the images that not every record contains an array. Just for the sake of clarity, my Forms View updates the “Equipment # and Description” field, which has the lookup field “New # (Don’t Use)” which is what is used to define the Primary field. Not really an elegant solution but it is what I have so far.

My Forms View:

My Table, with primary field formula:

Any help or suggestions here would be appreciated!

[Jordan_Crosley]

Update

I have made some progress so far, but I am running in to trouble with my Linked Record field, Equipment # and Description. Right now I am able to separate the array of New #'s into respective Outputs. The formula I have in the primary field is just {Output}. This is what my table looks like before and after I run my code:

Before:

After:

The problem I am having is that I apparently I can’t a string to a linked record field, as far as I can tell? I may need create a work around sorts by using additional tables and primary fields, but for now I would like to know if there is a way to put the corresponding Equipment # and Description in their respective fields. I also would like to delete the initial record because the primary field is left blank, which is causing issues in other areas.

My current code:

As always, thanks for the time anyone spends on this. Also, I thought I would just reply to myself instead of making a super long post…

[Dominik_Bosnjak]

Have you actually tried running it? :slightly_smiling_face: What error is it throwing?

[Jordan_Crosley]

Yep, I have tried running it but I don’t get an error. I actually have a slightly updated version below that shows my most recent attempts. Visually, refer to the above to see what this is doing. The problem I am having is getting the individual values of Equipment # and Description to show up. I don’t know how to use scripting to place values into a linked record.

let table = base.getTable("CopyRows");
let query = await table.selectRecordsAsync();
let field = await table.getField("Equipment # and Description");
for (let record of query.records) {
    let quantity = record.getCellValue("Count");
    if (quantity > 1) {
        for (let i = 0; i < quantity; i++) {
            let myArray1 = [record.getCellValueAsString("New # (Don't Use)")]
            let myArray1j = myArray1.join()
            let myArray2 = [record.getCellValueAsString("Equipment # and Description")]
            let myArray2j = myArray2.join()
            let myArray2s = myArray2j.split(",")
            let id = await query.getRecord(query.recordIds[0])
            let newRecord = await table.createRecordAsync({
                "Equipment # and Description": { id: myArray2s[i] }[0],
                // ^^ where my current issue is
                "Output": Number(myArray1j.substring(i * 8, (i * 8) + 6)),
                // ^^ for some reason I could't removeAll spaces and commas, hence the formula
                "Project Number": record.getCellValue("Project Number"),
                "Start": record.getCellValue("Start"),
                "End": record.getCellValue("End")
            })
            //let id = await query.getRecord(query.recordIds[0])
            //await table.updateRecordAsync(record, 
            //{"Equipment # and Description" : {id : myArray2s[i] }[]})
            // ^^ can't figure out if I need to update the record after it's created or fill in as I create the record 
            console.log(myArray2j)
            console.log(myArray2s)
            console.log(myArray2s[i])
            console.log(id)
        }
    }
    else (
        await table.updateRecordAsync(record, {
            "Output": Number(record.getCellValue("New # (Don't Use)"))
        }))
}

[Dominik_Bosnjak]

Ok, we’re getting somewhere, a few things:

awaiting individual fields isn’t necessary, or consequential in any way, for that matter.

you probably know this already but having special characters tied to reference values is not a good habit to get into. Ditto for not using the semicolon to end your statements; which might actually be the source of some of your problems due to code leakage.*

for some reason I could’t removeAll spaces and commas, hence the formula

I have a feeling that “some” reason is the same thing giving you trouble elsewhere hah.

No easy way to bootstrap a test base but those whitespaces suggest your typing is wrong;

I’d suggest rewriting the whole script in a way that doesn’t allow for nested loops and you might be surprised how smoothly things will go from there. As things stand right now, you shot yourself in the foot with the overly convoluted syntax, halfway in.

[Jordan_Crosley]

I don’t have any experience with Javascript syntax. Or coding at all, really. I guess I should have clarified that more in my original post.

I’m not sure if it possible to rewrite this without having at least one nested loop, since what I need to do is an iterative process.

Is there anyway you could give me some pointers on how to accomplish what I’m trying to do? If my objective is still confusing I apologize, I can try and explain more if I know what is unclear!

I took most of my source code from the below link and edited to fit my needs after a ton of trial and error.

Create multiple records based on a quantity field with a Script

[Jordan_Crosley · Accepted solution]

Just in case anyone ever has a similar question, I finally came to a solution. Much thanks to Erin with the Airtable team!!!

let table = base.getTable("Gantt Charts")
let query = await table.selectRecordsAsync()
for (let equipment of query.records) {
    let quantity = equipment.getCellValue("Count")
    if (quantity > 1) {
    let myArrayPrint = equipment.getCellValueAsString("Equipment # and Description")
    console.log('Entry Deleted:',[myArrayPrint])
        for (let i = 0; i < quantity; i++) {
            let myArray1 = equipment.getCellValue("Equipment # and Description")
            let myArray2 = myArray1[i].id
                await table.createRecordAsync({'Equipment # and Description': [{id:myArray2}],
                "Project Number": equipment.getCellValue("Project Number"),
                "Start": equipment.getCellValue("Start"),
                "End": equipment.getCellValue("End")
            })
        }
    let firstid = equipment.id
    await table.deleteRecordAsync(firstid)
    }
}