Hi all
I’m really struggling with this - where is @Bill.French when you need him :-), Bill I need you! (or anyone else that can help please).
So I have a view based on my orders table that filters unfulfilled orders
I have written some code (with the help of others) that loops through the unfulfilled orders and creates new records in the Prod Req (Producction Required table).
This table contains the product sku as the primary field and has a Linked Orders field (linked record field) that contains all unfulfilled orders with their order numbers along with some rollup fields.
This code works well but it will create duplicate records in the Prod Req table each time it runs.
Ideally, I’d like the code to first compare the Unfulfilled Orders against the Prod Req table. If it finds the same sku in there, then add the associated order number/order record.id to the Linked Orders field. If the sku does not exist in the Prod Req table, then add it and also the linked orders.
Here’s the code…
let orders = base.getTable("Daily Orders");
let pr = base.getTable("Prod Req");
let view = orders.getView("Unfulfilled Daily Orders");
let sku = orders.getField("Line Items: Sku");
let ordernum = orders.getField("Order: Name");
let currsku = "";
let ordstr = "";
let rid = "";
let Orderresult = await view.selectRecordsAsync({
sorts: [{field: sku, direction: "asc"}]
});
output.text("Source: Unfulfilled Daily Orders results array");
output.inspect(Orderresult);
let ordersArray = [];
for (let record of Orderresult.records) {
rid = record.id;
ordernum = record.getCellValue("Order: Name");
//output.text(ordernum);
currsku = record.getCellValue(sku)[0];
ordersArray.push({
fields: {
"id": rid,
"SKU": currsku
}
})
}
output.text("Unfulfilled Daily Orders results array by Record ID and SKU");
output.inspect(ordersArray);
let Prodresult = await pr.selectRecordsAsync();
output.text("Source: Prod Req results array");
output.inspect(Prodresult);
let prodArray = [];
for (let record of Prodresult.records) {
rid = record.id;
currsku = record.getCellValue("sku");
prodArray.push({
fields: {
"rid": rid,
"SKU": currsku
}
})
}
output.text("Prod Req results array by Record ID and SKU");
output.inspect(prodArray);
const skus = [...new Set(ordersArray.map(obj => obj.fields.SKU))];
const linkedRecordsArray = skus.map(sku => {
return {
fields: {
//'id': rid,
'sku': sku,
'Linked Orders': ordersArray.filter(obj => obj.fields.SKU === sku).map(obj => ({ id: obj.fields.id }))
}
};
});
output.text("Prod Req records needed to be created array");
output.inspect(linkedRecordsArray);
output.text("This works fine but it will clearly duplicate records each time it runs");
output.text("Ideally, I'd like the code to first compare the Unfulfilled Orders agsinst the Prod Req table. If it finds the same sku in there, then add the associaed order number/order record.id to the Linked Orders field.");
output.text("If the sku does not exist in the Prod Req table, then add it and the linbked orders");
let recordsCreated = await batchAnd('Create', pr, linkedRecordsArray);
/*
Use this function to perform 'Update', 'Create', or 'Delete'
async actions on batches of records that could potentially
more than 50 records.
::PARAMETERS::
action = string; one of 3 values:
- 'Update' to call table.updateRecordsAsync()
- 'Create' to call table.createRecordsAsync()
- 'Delete' to call table.deleteRecordsAsync()
table = Table; the table the action will be performed in
records = Array; the records to perform the action on
- Ensure the record objects inside the array are
formatted properly for the action you wish to
perform
::RETURNS::
recordsActedOn = integer, array of recordId's, or null;
- Update Success: integer; the number of records processed by the function
- Delete Success: integer; the number of records processed by the function
- Create Success: array; the id strings of records created by the function
- Failure: null;
*/
async function batchAnd(action, table, records) {
let recordsActedOn;
switch (action) {
case 'Update':
recordsActedOn = records.length;
while (records.length > 0) {
await table.updateRecordsAsync(records.slice(0, 50));
records = records.slice(50);
};
break;
case 'Create':
recordsActedOn = [];
while (records.length > 0) {
let recordIds = await table.createRecordsAsync(records.slice(0, 50));
recordsActedOn.push(...recordIds)
records = records.slice(50);
};
break;
case 'Delete':
recordsActedOn = records.length;
while (records.length > 0) {
await table.deleteRecordsAsync(records.slice(0, 50));
records = records.slice(50);
}
break;
default:
output.markdown(`**Please use either 'Update', 'Create', or 'Delete' as the "action" parameter for the "batchAnd()" function.**`);
recordsActedOn = null;
}
return recordsActedOn;
}
Thanks so much for your help. @Bill.French if you’re listening, I’d love your input please. If any answers can be as detailed as possible with code examples relevant to my tables / views it would really help as I’m only learning.
I’ve never coded in my life!!!
Thanks
Claire
Founder of Make Give Live
makegivelive.co.nz
