Update if record exists, create if not - with look up fields

[Claire_Conza]

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

[Bill_French]

Claire_Conza:

where is @Bill.French when you need him

Where was the hospitality of SkyCity, Aukland when I was down to my last ten bucks and needed a 7 or less to beat the dealer? :winking_face:

I wish I was more available at the moment but I’m slammed on some AI stuff for the foreseeable future. Here’s one idea that might help.

JSON objects are really handy for avoiding duplicates and I often use them as a constructor of data that contains no duplicates. They’re fast and efficient little data blobs that can perform de-duping logic without any effort. But the best part is the ability to perform lookups blazingly fast.

And your remaining unmet requirements seem to map into this capability quite nicely.

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 the linked orders.

The specific capability I am referring to is best described as a hash index. And I believe you are sort of doing that at line 65 in your code where you create an array of the products. The challenge, of course, is how to test this array against the Unfulfilled orders data

In that reference post I made above, at the very beginning of the process I absorb the records into a JSON object that results in unique keys instead of an array. Why do it this way? Speed, efficiency, and most importing instant lookup capability. A simple evaluation like this lets me know if a data set contains a given key:

// if TRUE, the SKU doesn't exist in the data set
let isFound = (oProdReq["thisSKUNumber"] === null);

In your case, I would create index hash objects for both the Unfulfilled Orders and the Prod Req tables. Imagine two JSON data sets that have this basic structure…

oProdReq["thisSKUNumber"] = {
   "rid" : rid
}

oUnfulfilled["thisSKUNumber"] = {
   "rid" : rid
}

This would make it possible to perform simple conditional logic about the nature of the two data sets - i.e., … given a specific sku key, this evaluation would tell you exactly what you need to know to determine what action to take.

if (oProdReq[sku] && oUnfulfilled[sku]) {
   // then add the associated order number/order record.id to the Linked Orders field
} else {
   // add it and the linked orders
}

Hope that helps - wish I could join you on this journey. And even so, this has been a pleasure because I get to write code that never has to run. :winking_face:

[Claire_Conza]

Hi Bill

Thanks so much for your help and sorry to hear your bad luck at Sky City ;-).

I’ve got as far as creating the hash tables by amending your hash code to work with view as well :-).

Hash Index of Prod Req SKU’s

1. alexbeanieadultgreymarle: “recnVVrlN7bRtavSv”

2. alexsalebeanieadultcharcoal: “recN5FK8QRPaF02xO”

3. bobbiebeaniekidscharcoal13years: “recgFTupgScImyo2L”

4. charliebeanieadultink: “recfc5Eyb5SWUIA1P”

5. cocobeaniekidscharcoal48yrs: “receplaV8sYrUwxYi”

6. cocobearbeaniebabymist13yrs: “recZDkaAX7CAhYPCW”

7. cocobearbeaniebabymist612mth: “recZQokWFRuOsDaJn”

8. cocobearbeaniebabymistshell612mth: “recAPx4w5jz3AkmX2”

9. cocobearbeaniebabysand03mth: “recJaRgUf5fBXfMUR”

Hash Index of Unfulfilled Orders SKU’s

1. cocobeaniekidscharcoal48yrs: “recaiwhfvTVIFEjjE”

2. charliebeanieadultink: “recVAt6OyzsJ52quL”

3. cocobearbeaniebabymist13yrs: “recr2RvsCCccNqFON”

4. cocobearbeaniebabymist612mth: “rec4z9uwzELfSWUe1”

5. alexbeanieadultgreymarle: “rec9Q7XkJjeb1sJ6O”

6. bobbiebeaniekidscharcoal13years: “rec88dddBsaBWhLJj”

7. cocobearbeaniebabysand03mth: “recTu9YL4L5Sz9Gdg”

8. cocobearbeaniebabymistshell612mth: “rec6qOhfIRVaIG4Ef”

9. alexsalebeanieadultcharcoal: “recoQd8123eoQ8mY8”

My understanding however is that I need the recordid’s of the Prod Req table to update the Linked Orders field but I’d also need the order numbers and associated record id’s of these orders given that the problem I’m trying to solve for is as follows:-

1. Loop unfulfilled orders.
2. For each SKU in unfulfilled orders, does it exist in Prod Req, if not then add it as new record and add linked order (record id of order) to Linked Orders field.
3. If SKU exists, then check if unfulfilled order record id exists in the Linked Orders field, if not add it, if it exists, then ignore.

On this basis, I’m assuming my Prod Req hash table should also contain the record id’s and / or order numbers of the orders shouldn’t it?

I’m also struggling with the syntax for updating a linked record in this context.

Can you help with what the code should like please @Bill.French

Thanks

Claire

[Bill_French]

Claire_Conza:

I’m assuming my Prod Req hash table should also contain the record id’s and / or order numbers of the orders shouldn’t it?

Yes. And you say you’re not a coder. :slightly_smiling_face: This is precisely one qood way to streamline your access to all the data for this process.

Claire_Conza:

I’m also struggling with the syntax for updating a linked record in this context.

Sure, in 2021. :winking_face: Seriously though, I probably can’t get away for at least a few weeks to focus on this code. But I’m going to suggest someone like @kuovonne who is also very busy but can suss out betterments to code like this faster than anyone I’ve seen. I also call upon @Jason at Airtable to nudge of a few of the experts to lend a hand.

I think you’re really close to resolving this so push on through and you’ll be a great coder in no time.

[Jeremy_Oglesby]

@Claire_Conza I’m not sure about the rest of what you’re trying to do, but with regard to this:

Claire_Conza:

the syntax for updating a linked record in this context.

I think the documentation in the Cell values & field options section can be of help:

So to update a “Link to Records” field, you have to pass it an array of objects, and each object needs to have the property id: <record_id>.

And if you don’t want to clobber all the existing record links on the record you are updating, you need to ... spread the current contents of that field into the array first.

I think that’s what you are after… but I could be mistaken.

[kuovonne]

Thanks for the shout-out, @Bill.French. Yes, I’m busy balancing several projects right now, but I do have a few quick comments about this situation.

1. Welcome @Claire_Conza to the world of coding! Learning coding is a journey and personal projects are great for learning.

2. There are many ways to solve a coding problem. My suggestions here are just suggestions. There are many trade-offs in coding decisions–how fast the code will run, how long it will take you to write, how well you understand the solution, how easy it will be to adapt the code for future changes, etc.

3. In Airtable, you can update a linked record from either table. In this case, it is easier to update the linked field in the [Daily Orders] table because it will always start out blank and end up with only one value. If you update the [Production Required] table, you will have to make sure that you retain all the existing linked records, especially when multiple orders will have the same sku. While this method will result in more records to update and will take a bit longer to run, based on the data in your base and your coding experience level, I think it will be easier to write and maintain.

4. I recommend that you compartmentalize your code in functions.

   For example, you can write a function getRecordIdOfSku that inputs the text string for a SKU & a hash of the current names and IDs in the [Production Required] table, and returns the corresponding record ID. Then your main function that loops through the records can call that function to get the record ID and continue on with that record ID.

   In the body of the function, lookup the SKU in the hash table. If the SKU exists, return the record ID. If the SKU doesn’t exist, create the new record, add the new record ID to the hash, and return the record ID.

5. It looks like this script will be triggered by a button in Scripting block. However, if your orders will be coming in via a form or other integration, you might want to consider writing the script so that it can be used in Automations.

Here’s some pseudocode for you

for (let record of Orderresult.records) {
  // get the sku from the record
  // function call to get the record ID for the sku (function creates the record if necessary)
  // update the current record with the record id for the sku (or push the update to an array for a batch update)

Hope this helps!

[Claire_Conza · Accepted solution]

Hi all

Thank you to @Bill.French, @Jeremy_Oglesby and @kuovonne for your help. Along with a good friend here in Auckland, we have a solution.

In case it’s useful to someone else, I’ll post the solution here…

// Create reference to the Unfulfilled Orders view of the Daily Orders table

const unfulfilledOrders = base.getTable('Daily Orders').getView('Unfulfilled Daily Orders');

// Get the records from the Unfulfilled Orders view

const unfulfilledOrdersRecords = await unfulfilledOrders.selectRecordsAsync();

// Get the SKU of each unfulfilled order

const unfulfilledOrdersSkus = [

    ...new Set(unfulfilledOrdersRecords.records.map(record => record.getCellValue('Line Items: Sku')[0]))

].sort();

console.log('SKUs of all unfulfilled orders:', unfulfilledOrdersSkus);

// Get the records from the Prod Req table

const prodReq = base.getTable('Prod Req');

const prodReqRecords = await prodReq.selectRecordsAsync();

// Get the SKU of each record in the Prod Req table

const prodReqSkus = prodReqRecords.records.map(record => record.getCellValue('sku')).sort();

console.log('SKUs of all records currently in Prod Req table:', prodReqSkus);

// For each SKU of the Unfulfilled Orders view, check if it already exists in the Prod Req table

const skusToCreateInProdReq = unfulfilledOrdersSkus.map(sku => (prodReqSkus.includes(sku) ? null : sku)).filter(res => res !== null);

// If SKUS exist in Unfulfilled Orders view, but not in the Prod Req table, then create it there.

if (skusToCreateInProdReq.length) {

    console.log('Need to create new records for these SKUs in Prod Req:', skusToCreateInProdReq);

    // Max number of records to create at once is 50, so split all new records to create into chunks of that size

    const size = 50;

    const chunkedArray = [];

    for (var i = 0; i < skusToCreateInProdReq.length; i += size) {

        chunkedArray.push(skusToCreateInProdReq.slice(i, i + size));

    }

    chunkedArray.forEach(async arr => {

        const data = arr.map(sku => ({ fields: { sku: sku } }));

        const newRecords = await prodReq.createRecordsAsync(data);

        console.log('Created these new records:', newRecords);

        return newRecords;

    });

} else {

    console.log('No records to create in Prod Req');

}

// For each record in Unfulfilled Orders, check if the order number exists as a linked order for the relevant SKU in Prod Req

const skusAndIds = [...new Set(unfulfilledOrdersRecords.records.map(record => record.getCellValue('Line Items: Sku')[0]))].reduce(

    (obj, sku) => {

        obj[sku] = [];

        return obj;

    },

    {}

);

unfulfilledOrdersRecords.records.forEach(record => {

    const sku = record.getCellValue('Line Items: Sku')[0];

    skusAndIds[sku].push(record.id);

});

console.log('Order numbers and their IDs', skusAndIds);

const updatedProdReqRecords = await prodReq.selectRecordsAsync();

updatedProdReqRecords.records.forEach(async record => {

    const sku = record.getCellValue('sku');

    const linkedOrders = record.getCellValue('Linked Orders');

    const unfulfilledOrderIds = skusAndIds[sku];

    const existingLinkedOrderIds = linkedOrders ? linkedOrders.map(order => order.id) : [];

    const toAdd = unfulfilledOrderIds.map(id => (existingLinkedOrderIds.includes(id) ? null : id)).filter(id => id !== null);

    if (toAdd.length > 0) {

        console.log(toAdd);

        const data = [...toAdd.map(id => ({ id: id }))];

        if (linkedOrders) {

            data.push(...linkedOrders);

        }

        console.log(`Updating ${sku} with ${JSON.stringify(data, null, 4)}`);

        const update = await prodReq.updateRecordAsync(record, { 'Linked Orders': data });

        return update;

    }

});