Help with scripting/creating multiple records

[Jake_Price]

Hi! I have a sort of complicated situation and I’ve been able to figure out what i need to do, but not the way to actually accomplish it.

I have a base with three tables in it:
Jotform (A) | Supplies (B) | Split Requests (C)

Table A is bringing in responses, Table B is a list of every unique item that has been requested in the jotform, and Table C is where I will split up requests with multiple items in Table A to separate ones.

In the Jotform, people can select multiple items in one form submission. even though someone might send in one form submission, that submission might include 50+ items. The jotform takes the info for each item (data points like item,qty,notes) and separates them by comma, and each item by a line break. that’s my way in with parsing out the data.

I need to take a cell that might say:

Apples,50,red apples
Apples,49,granny smith
Peaches,23
Oranges,44

and split that into separate requests in Table C. then i need to link that record back to the original record in Table A that it came from.

I also need to then create a linked record that matches the name of the item (the first datapoint) and connect that to the corresponding item in Table B.

I also need the script to check if the split item in Table C is already in Table B or not. if it is in that table, link it. if it isn’t, create a new record in that table, pull in the text from the record in Table C to name it, and then link that record to theTable C record.

in summary, im parsing out a list of data in Table A into separate records in table C (that link back to the record they came from in Table A), and then im linking those parsed records to the list of items in Table B (and if i need to create a new record in order to link them, then do that as well).

I’d prefer to have this run from a button in Table A, so once a jotform comes in, i can review it, make sure it’s good to go, and then click the button to run the script. Any help would be greatly appreciated!

[Jake_Price · Accepted solution]

It’s finished! Here’s the working script, factoring in values from two fields in Table A, matching or creating a record in Table B, and then splitting the request and linking everything in Table C. A cherry on top, I have a field in Table A where I keep track of which records I’ve pressed the button.

Final questions:

1. Could this code be made more concise than it currently is?
2. Are there any things that should be changed?

const tA = base.getTable('Supply Request Jotform');
const tA_General = tA.getField('General Supplies');
const tA_Other = tA.getField('Other Supplies');
const tB = base.getTable('Supplies');
const tC = base.getTable('Split Requests');

// Button press pulls in that record instead!
let tA_Record = await input.recordAsync('Select a record',tA);
let tA_Gen_Text = tA_Record.getCellValue(tA_General);
let tA_Oth_Text = tA_Record.getCellValue(tA_Other);

function isBlank(str) {
    return (!str || /^\s*$/.test(str));
};

if (isBlank(tA_Gen_Text)) {}
else {
    let genItemCount = tA_Gen_Text.split("\n").length;
    for (let i = 0; i < genItemCount; i++) {
        let array = tA_Gen_Text.split("\n");
        let line = array[0+i];
        let [tA_Item,tA_Qty,tA_Notes] = line.split(',');
        
        /*
        output.markdown(
        `# General Item #${i+1}:

        Item: ${tA_Item}
        
        Qty: ${tA_Qty}
        
        Notes: ${tA_Notes}`);
        */

        let tB_Query = await tB.selectRecordsAsync({fields:["Item"]});
        let matched = tB_Query.records.filter(item => {return item.name === tA_Item});
        let tB_Record;
        // If a matching record exists, use it; otherwise make a new record
        if (matched.length)
            tB_Record = matched[0].id;
        else
            tB_Record = await tB.createRecordAsync({
                "Item": tA_Item,
                "Type": {name: 'Other (Custom) Supply'}
            });
        await tC.createRecordAsync({
            "Linked Submission ID": [{id: tA_Record.id}],
            "Linked Supply Item": [{id: tB_Record}],
            "Qty":parseInt(tA_Qty, 10),
            "Notes":tA_Notes
        });
    };
};


if (isBlank(tA_Oth_Text)) {}
else {
    let othItemCount = tA_Oth_Text.split("\n").length;
    for (let i = 0; i < othItemCount; i++) {
        let array = tA_Oth_Text.split("\n");
        let line = array[0+i];
        let [tA_Item,tA_Qty,tA_URL,tA_Cost,tA_Notes] = line.split(',');
        
        /*
        output.markdown(
        `# Other Item #${i+1}:
        
        Item: ${tA_Item}
        
        Qty: ${tA_Qty}

        URL: ${tA_URL}

        Cost Per: ${tA_Cost}
        
        Notes: ${tA_Notes}`);
        */

        let tB_Query = await tB.selectRecordsAsync({fields:["Item"]});
        let matched = tB_Query.records.filter(item => {return item.name === tA_Item});
        let tB_Record;
        // If a matching record exists, use it; otherwise make a new record
        if (matched.length)
            tB_Record = matched[0].id;
        else
            tB_Record = await tB.createRecordAsync({
                "Item": tA_Item,
                "Type": {name: 'Other (Custom) Supply'},
                "URL":tA_URL,
                "Cost Per":parseInt(tA_Cost, 10),
                "Notes":tA_Notes
            });
        await tC.createRecordAsync({
            "Linked Submission ID": [{id: tA_Record.id}],
            "Linked Supply Item": [{id: tB_Record}],
            "Qty":parseInt(tA_Qty, 10),
            "Notes":tA_Notes
        });
    };
};

await tA.updateRecordAsync(tA_Record, {"Already Split?":{name: 'Yes'}});

Thanks for the help @Justin_Barrett and @Dominik_Bosnjak !