Incrementing product quantity when new order record created

Hi all - I am new to Airtable and am hoping to use it manage inventory for an online secondhand clothing store (Shopify-based). When we receive a new order a zapier integration will create a new row in an Airtable ‘Orders’ table with fields like quantity ordered, price etc. I would like this action to automatically trigger a corresponding downward increment of the quantity available field in my ‘Products’ table. How is this possible please? I don’t want to use formulas as then I would need to store all our orders indefinitely and would quickly exceed the size limit on the sheet. I need to be able to archive the orders information without losing the impact on my rolling ‘quantity available’ field in my products table. The incrementing impact needs to permanent and cumulative. Many thanks, Oli

you can do it if your not afraid of a little javascripting. Let me know and then ill try to write a complete explanation on how to do it.

Hi Stephen - thanks for your reply and hope you are well! No, not afraid of coded solution :slight_smile: Would really appreciate your help with it? Many thanks, Oli

So i saw your question and thought I would try and figure it out and was able to make it work based on your description.

  1. create automation with a trigger of “When a record is created”. Select your orders table for the table dropdown.

  2. In the actions section choose Run Script from the dropdown. This should open up a large window with four different sections in it.

    1. In the script window on the left side is where you can add variables from your table. I assume you have something like a item id/code that is unique to the tiem that is being bought on both the Orders table as well as the Products table. We are going to use that to match the order to. To add those variables you need ot click on the blue text that says “+ Add input variable”.

    2. now you are going to give the variable a name, I named it itemID. Then click on the blue plus in the values field and navigate through the dialogue window to the field that contains your items id.
      airtable variable select

    3. Next do the same process but for your order quantity.

    4. Now that you have created both variables your going to start coding.
      let inputConfig = input.config(); let table = base.getTable("Products"); let query = await table.selectRecordsAsync({ sorts: [ // sort by "id" in ascending order... {field: "id"} ] }); // print ID & "id" from each record: for (let record of query.records) { if(record.getCellValue("id") == inputConfig.itemID){ let newNum = record.getCellValue("quantity")-inputConfig.orderQuantity; await table.updateRecordAsync(record.id,{"quantity":newNum}); } }

      1. Let me explain what is happening here. The first line is getting the variables we just created on the input variables and storing them in inputConfig.

      2. The second line is actually getting the table reference for the table you called your products table and storing it as table.

      3. line 3-8 is getting the table rows and sorting it on the item id’s. You dont need to sort these values if you dont want to.

      4. line 10 is a for…each loop that is getting each row of the table one at a time and storing that value as “record” for that iteration.

      5. line 11 is checking to make sure that the record id from the products table equals the item id of the order record that was just added. If its true then it executes lines 12-13. line 12 stores the difference between the product table and the order table as “newNum”. Line 13 updates the product table with the new value.

    5. at this point you can test what you have created but in your case you might want to just publish it and do a real submission because any “test” you execute in airtable actually updates the table so you would have to know what record you updated so you can revert that number. Im sure you know this but before the automation will work for real you have to activate it by clicking on off toggle in the upper right corner of the automation.
      airtable automation activate

Hope this helped let me know if you have any other questions.

1 Like

Hi Stephen - that is absolutely fantastic, thank you so much for your time and effort in explaining that. Super appreciated. I’m planning to carve out a bit of time to implement it in the next day or two and will let you know if I have any more questions - Cheers!