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]

Further thinking on this, I’m trying to “storyboard” together what needs to happen, this is what I’m thinking:

Button in Table A, linked to script that pulls info with ASync.

1. split cell content by line break (regex)
2. split those into segments based on the “,”
3. i think forEach is somehow going to get me to where i need to be able to loop through each line break, and perform the necessary splits.
4. within that “foreach” loop, i need to create a variable for each value in the comma-separated row.
5. then create a new record in Table C with those parameters.
6. then do a name match of the item name in Table C to see if it appears in Table B.
   If it does, get record id and link them with a linked field i created.
   If it doesn’t, create a new record in Table B, get its Record ID, use that to update the record with a new name, and to then link it to the record in Table C. (or can i input the item’s name before needing to retrieve the record id?)
7. I also need the newly created record in table C to link to it’s source record in Table A.
8. I think once that happens, I can loop over in the forEach i’m running for each line of data.

There are going to be more steps in my actual script, because i do this for two different fields in Table A, but if I know how to do one, I’ll be able to duplicate the steps for the other.

[Justin_Barrett]

Nice work breaking down the problem, Jake! I might take a different approach to some of the things that you describe (#1, for instance, can be done with the split() string method; no regex required), but that’s just because I’ve written lots of code by now. :slightly_smiling_face:

I recommend taking your outline, simplifying the descriptions a bit, and creating comment lines in an empty script as a starting point. Then just start working it from the top down, writing and testing the code for each commented section as you go. Make sure that the script does what you want in each section before moving on to the next one. If you write too much code before you begin testing, it can sometimes be harder to find problems.

Jake_Price:

There are going to be more steps in my actual script, because i do this for two different fields in Table A, but if I know how to do one, I’ll be able to duplicate the steps for the other.

If you design the script in a more modular fashion, you could probably do write the core code once as a function, then call that function for each field. That’s a slightly oversimplified description, but the point is that the more reusable you make the code, the more versatile it becomes, and the easier it is to maintain.

[Jake_Price]

Thanks! I’m working through it now. Here’s what I’ve got so far, working my way down, right now just trying to parse out the lines in the textbox and see if i can view it split in markdown before going to step two. i also made variables for all of the fields i know im going to use in the script to prep. for #1, i actually am using a split() but it has regex as the identifier inside of it. is that the simpler way you were thinking of? or is there another way to use split to break up each line of text?

here’s the code so far:

const tA = base.getTable('Supply Request Jotform');
let tA_General = tA.getField('General Supplies');
let tA_Other = tA.getField('Other Supplies');

const tB = base.getTable('Supplies');
let tB_Item = tB.getField('Item');
let tB_Type = tB.getField('Type');
let tB_URL = tB.getField('URL (Other)');
let tB_Notes = tB.getField('Notes');

const tC = base.getTable('Split Requests');
let tC_Submission = tC.getField('Linked Submission ID');
let tC_Item = tC.getField('Linked Supply Item');
let tC_Qty = tC.getField('Qty');
let tC_Notes = tC.getField('Notes');

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

//let tA_Gen_Line = tA_RecordValue.split(/\r?\n/);
//let [tA_Gen_Item,tA_Gen_Qty,tA_Gen_Notes] = tA_RecordValue.split(',');
//tA_Gen_Line.forEach(dataDisplay);

for (let i = 0; i < tA_RecordValue.split(/\r?\n/).length; i++) {
let [tA_Gen_Item,tA_Gen_Qty,tA_Gen_Notes] = tA_RecordValue.split(',');
output.markdown(`# You have selected
 ${tA_RecordValue}.
 
 Item: ${tA_Gen_Item}

 Qty: ${tA_Gen_Qty}
 
 Notes: ${tA_Gen_Notes}`);
}

function dataDisplay (item, index, array) {
    
}

It doesn’t totally work. I don’t get errors (yay!) but i also don’t get the text to display the way it’s supposed to. Looking to see what is wonky, but I’m sure it’s in the last few lines of code, seeing as almost all of what I’ve got there are variable declarations

[Justin_Barrett]

Jake_Price:

i also made variables for all of the fields i know im going to use in the script to prep.

From my experience, this is overkill. You can get cell values without first making variables for each field. Just pass the field name:

let tA_RecordValue = tA_Record.getCellValue('General Supplies');

Jake_Price:

for #1, i actually am using a split() but it has regex as the identifier inside of it. is that the simpler way you were thinking of? or is there another way to use split to break up each line of text?

These days it’s extremely unlikely that you’ll get carriage returns (\r). I would go with this:

for (let i = 0; i < tA_RecordValue.split("\n").length; i++) {

Those are my initial thoughts. I’ve gotta step away to take care of some other stuff, but I might circle back later if I have time.

[Jake_Price]

thanks! i’ll keep working on it.

[Jake_Price]

Update – I got past the parsing hurdle! I can now click the button in Table A and get a markdown readout.

Here’s the cell I’m testing with:

Extension Cord (specify length in notes),2,12+ feet
Power Strip,4

And here’s the script now:

const tA = base.getTable('Supply Request Jotform');
let tA_General = tA.getField('General Supplies');
let tA_Other = tA.getField('Other Supplies');

const tB = base.getTable('Supplies');
let tB_Item = tB.getField('Item');
let tB_Type = tB.getField('Type');
let tB_URL = tB.getField('URL (Other)');
let tB_Notes = tB.getField('Notes');

const tC = base.getTable('Split Requests');
let tC_Submission = tC.getField('Linked Submission ID');
let tC_Item = tC.getField('Linked Supply Item');
let tC_Qty = tC.getField('Qty');
let tC_Notes = tC.getField('Notes');

// Button press pulls in that record instead!
let tA_Record = await input.recordAsync('Select a record',tA);
let tA_RecordValue = tA_Record.getCellValue(tA_General);
let itemCount = tA_RecordValue.split("\n").length;

output.markdown(`# # of Records in this text box: ${itemCount}`);

for (let i = 0; i < itemCount; i++) {
    let array = tA_RecordValue.split("\n");
    let line = array[0+i];
    let [tA_Gen_Item,tA_Gen_Qty,tA_Gen_Notes] = line.split(',');
    output.markdown(
        `# Item #${i+1}:
        
        Item: ${tA_Gen_Item}
        
        Qty: ${tA_Gen_Qty}
        
        Notes: ${tA_Gen_Notes}`);
}

And here’s my result:

thoughts

going forward, i wont make all those variables, but since i already made these, i figured i might as well keep em. good to know for the future though, thanks!

i looked through Tables B and C and realized i need to slightly adjust my code flow –
next i need to take the data ive parsed out and match the item up with a record in Table B. if the record exists and shares the same value in the notes column, then link that record. if there isn’t a record with matching title and notes, i need to create a new one.
then, i can create a record in table c where it’s linked to the submission in Table A and to the item in Table B. it also needs to input the qty from Table A into the new record in Table C.

[Justin_Barrett]

Sounds like a good plan! :thumbs_up:

[Dominik_Bosnjak]

Kudos on the initiative, maybe consider functional syntax like the Map.prototype.forEach method you mentioned to make your life and prototyping a lot easier - try to write what you want to achieve, not the details on how it needs to happen like going above and beyond to reference your fields as objects.

Don’t use the let keyword unless you’re specifically looking to mutate data, which you mostly aren’t, just creating new records. Mutation leads to side effects, usually bugs that get tougher to predict the more you keep doing it. And you definitely need a simpler method of referencing your fields. The built-in record.getCellValue method doesn’t require you to pass in an actual field object, even though that works. Better to use shorter field names. Or maybe a config of references the script loads separately from another record.

A lot of your setup seems to be reliant on your good judgment, automating that can feel overwhelming at times because computers suck at adapting, which is all the more reason to go functional or at least procedural. Take markdown output, for instance, why not just grab the cell values and use something like JSON.stringify to do figure out what to do? You can pass arguments like

output.markdown('>> ' + JSON.stringify(someString, null, '\n\t'))

To get the codeblock look you’re going for and deal with indentation handling.

[Jake_Price]

I’m stuck again and scratching my head. I am trying to match up the name of the item from Table A with an item in Table B, and if it can’t, then it creates a new record in table B with the values from the aprsed out info in Table A. I found a thread with a script from @Justin_Barrett but I’m having trouble putting it into my script. How do I match up a value “Item Name” to a record in Table B?