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

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:

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

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…

1oProdReq["thisSKUNumber"] = {
2   "rid" : rid
3}
4
5oUnfulfilled["thisSKUNumber"] = {
6   "rid" : rid
7}
8

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.

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

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:

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

1oProdReq["thisSKUNumber"] = {
2   "rid" : rid
3}
4
5oUnfulfilled["thisSKUNumber"] = {
6   "rid" : rid
7}
8

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

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

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.

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.

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

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.

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

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

Hope this helps!

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…

1// Create reference to the Unfulfilled Orders view of the Daily Orders table
2
3const unfulfilledOrders = base.getTable('Daily Orders').getView('Unfulfilled Daily Orders');
4
5// Get the records from the Unfulfilled Orders view
6
7const unfulfilledOrdersRecords = await unfulfilledOrders.selectRecordsAsync();
8
9// Get the SKU of each unfulfilled order
10
11const unfulfilledOrdersSkus = [
12
13    ...new Set(unfulfilledOrdersRecords.records.map(record => record.getCellValue('Line Items: Sku')[0]))
14
15].sort();
16
17console.log('SKUs of all unfulfilled orders:', unfulfilledOrdersSkus);
18
19// Get the records from the Prod Req table
20
21const prodReq = base.getTable('Prod Req');
22
23const prodReqRecords = await prodReq.selectRecordsAsync();
24
25// Get the SKU of each record in the Prod Req table
26
27const prodReqSkus = prodReqRecords.records.map(record => record.getCellValue('sku')).sort();
28
29console.log('SKUs of all records currently in Prod Req table:', prodReqSkus);
30
31// For each SKU of the Unfulfilled Orders view, check if it already exists in the Prod Req table
32
33const skusToCreateInProdReq = unfulfilledOrdersSkus.map(sku => (prodReqSkus.includes(sku) ? null : sku)).filter(res => res !== null);
34
35// If SKUS exist in Unfulfilled Orders view, but not in the Prod Req table, then create it there.
36
37if (skusToCreateInProdReq.length) {
38
39    console.log('Need to create new records for these SKUs in Prod Req:', skusToCreateInProdReq);
40
41    // Max number of records to create at once is 50, so split all new records to create into chunks of that size
42
43    const size = 50;
44
45    const chunkedArray = [];
46
47    for (var i = 0; i < skusToCreateInProdReq.length; i += size) {
48
49        chunkedArray.push(skusToCreateInProdReq.slice(i, i + size));
50
51    }
52
53    chunkedArray.forEach(async arr => {
54
55        const data = arr.map(sku => ({ fields: { sku: sku } }));
56
57        const newRecords = await prodReq.createRecordsAsync(data);
58
59        console.log('Created these new records:', newRecords);
60
61        return newRecords;
62
63    });
64
65} else {
66
67    console.log('No records to create in Prod Req');
68
69}
70
71// For each record in Unfulfilled Orders, check if the order number exists as a linked order for the relevant SKU in Prod Req
72
73const skusAndIds = [...new Set(unfulfilledOrdersRecords.records.map(record => record.getCellValue('Line Items: Sku')[0]))].reduce(
74
75    (obj, sku) => {
76
77        obj[sku] = [];
78
79        return obj;
80
81    },
82
83    {}
84
85);
86
87unfulfilledOrdersRecords.records.forEach(record => {
88
89    const sku = record.getCellValue('Line Items: Sku')[0];
90
91    skusAndIds[sku].push(record.id);
92
93});
94
95console.log('Order numbers and their IDs', skusAndIds);
96
97const updatedProdReqRecords = await prodReq.selectRecordsAsync();
98
99updatedProdReqRecords.records.forEach(async record => {
100
101    const sku = record.getCellValue('sku');
102
103    const linkedOrders = record.getCellValue('Linked Orders');
104
105    const unfulfilledOrderIds = skusAndIds[sku];
106
107    const existingLinkedOrderIds = linkedOrders ? linkedOrders.map(order => order.id) : [];
108
109    const toAdd = unfulfilledOrderIds.map(id => (existingLinkedOrderIds.includes(id) ? null : id)).filter(id => id !== null);
110
111    if (toAdd.length > 0) {
112
113        console.log(toAdd);
114
115        const data = [...toAdd.map(id => ({ id: id }))];
116
117        if (linkedOrders) {
118
119            data.push(...linkedOrders);
120
121        }
122
123        console.log(`Updating ${sku} with ${JSON.stringify(data, null, 4)}`);
124
125        const update = await prodReq.updateRecordAsync(record, { 'Linked Orders': data });
126
127        return update;
128
129    }
130
131});