WooCommerce Orders to Airtable record when I have data in 1 cell in Airtable?

[rbeason]

I have WooCommerce orders being added as a new record to a AirTable table called "Orders".  I want to add a checkbox besides each row when the order comes in that will move certain information from the "Orders" table to another table in AirTable called "Fulfillment".  This "Fulfillment" table has rows pre-populated with Data.

Is there a way to add data from one AirTable table to another table that already has some data in certain columns/cells?

[TheTimeSavingCo]

Yeap, you'd use an automation that would trigger when that checkbox field gets ticked, and it'll have a "Find Record" action to find the appropriate row in "Fulfillment" and then an "Update Record" action to update the fields you want

I take it there's a business requirement that prevents us from just using linked fields and a conditional lookup to display data from the "Orders" table in the "Fulfillment" table?

[rbeason]

If there's a better way I'm all for it.

We sell exam vouchers and I'm trying to bring our WooCommerce orders into AirTable so that we can fulfill orders automatically.  We have blocks of codes already populated in the Fulfillment table and would like to bring the WooCommerce orders into that table to assign each order a code.  I did not think that would be possible as there is only a Create record in AirTable automation in Zapier, not an Update record based on certain condition.

So I figured I could just use Zapier to trigger on a WooCommerce order to put it into its own Airtable table and then add a checkbox in that AirTable table to run when checked.  When you check it, it would add it to the Fulfillment table based on what code was ordered (column "Course").

When there were no more codes, it would then fire off a trigger to email us to put more codes in there type of thing.

[TheTimeSavingCo]

Hmm yeah, there's probably a better way but let's answer your original question first!

So you'd have a checkbox that ran the automation like you said:

And once it's checked the automation would run and look for a record in "Fulfillment" that had the same "Course" value in it's "Name" field, and then put the text from the "Data" field in there:


And the automation would look something like this:

---

The other potential way to handle it would be for us to link the records together with the automation and use a lookup field:

And that way any updates to the "Data" field in "Orders" would reflect immediately in "Fulfillment".  Probably not that useful for you since you're not going to change the data in "Orders" ever

Link to base
---
Finally, assuming the primary field in "Fulfillment" is the "Course" field, I think you might be able to establish the link directly within Zapier.  To do this, we'd need a linked field from "Fulfillment" to "Orders", and in your Zap to create the record in "Orders", we'd just put the code value into the linked field to "Fulfillment".  This should automatically create a link between the two records and you could use the lookups to display the text without needing any extra automations

[rbeason]

I will try this out as it's not just 1 cell of data that would need to be moved to the Fulfillment table.  There's multiple cells of data (name, email, address, etc, etc).

[rbeason]

Could not find a edit button for my reply.

I understand how to do the run automation when the checkbox is checked.  The find records in Fulfillment where Course field = this should be a variable that pulls from the Line Items Name column in the Orders table.  Is there a way to do that?

[rbeason]

 Tried creating an automation using the above steps but it just fails to run.

Here is the automation I have setup.

 

 

[TheTimeSavingCo]

Your 'Update Record' step is set up incorrectly I think!  Try using the record IDs of the found records like so:

And so it should look like this:

  

[rbeason]

So, first of all, thank you for all the help previously Adam.

I am almost there with this.  I ended up going the script route.  It works, but fills in the record update on the wrong record.

The script below pulls the data from the Orders table when the checkbox is checked.

There are other columns, but the main one I'm looking at is the Line Items Name column.  When you check the checkbox at the end of this table, it fires off the actions below, which work fine and all.  However, it will put the line item with the name AEO - ITIL 4 Foundation into the Course field that is for AEO - ITIL 4 Foundation w/TAKE2 as shown below.

How do I prevent this and have it update the available record only if the Line Items Name field in Orders matches the Course field in Order Fulfillment?

Here is the full automation steps

Here is the script for the first Run a script action.

let orderFulfillmentTable = base.getTable("Order Fulfillment Spreadsheet");
let pitgWooCommerceTable = base.getTable("PITG WooCommerce Orders");

// Fetch all records from PITG WooCommerce Orders
let pitgWooCommerceRecords = await pitgWooCommerceTable.selectRecordsAsync();

// Create a map to store the mapping between course names and PassionIT order numbers
let courseToPassionITOrderMap = {};
for (let pitgRecord of pitgWooCommerceRecords.records) {
    let lineItemName = pitgRecord.getCellValue("Line Items Name"); // Assuming "Line Items Name" is the name of the field in PITG WooCommerce Orders
    let passionITOrderNumber = pitgRecord.getCellValue("Number"); // Assuming "PassionIT Order #" is the name of the field in PITG WooCommerce Orders
    courseToPassionITOrderMap[lineItemName] = passionITOrderNumber;
}

// Fetch all records from Order Fulfillment Spreadsheet
let orderFulfillmentRecords = await orderFulfillmentTable.selectRecordsAsync();

// Update records in Order Fulfillment Spreadsheet with PassionIT Order numbers
for (let orderFulfillmentRecord of orderFulfillmentRecords.records) {
    let courseName = orderFulfillmentRecord.getCellValue("Course"); // Assuming "Course" is the name of the field in Order Fulfillment Spreadsheet

    // Check if the course name exists in the map
    if (courseToPassionITOrderMap.hasOwnProperty(courseName)) {
        let passionITOrderNumber = courseToPassionITOrderMap[courseName];

        // Update "PassionIT Order #" field in Order Fulfillment Spreadsheet
        await orderFulfillmentTable.updateRecordAsync(orderFulfillmentRecord, {
            "PassionIT Order #": passionITOrderNumber
        });
    } else {
        // If course name doesn't exist in the map, try to find matching course name
        let lineItemName = orderFulfillmentRecord.getCellValue("Course"); // Assuming "Line Items Name" is the name of the field in Order Fulfillment Spreadsheet

        // Check if the line item name exists in the map
        if (courseToPassionITOrderMap.hasOwnProperty(lineItemName)) {
            let passionITOrderNumber = courseToPassionITOrderMap[lineItemName];

            // Update "PassionIT Order #" field in Order Fulfillment Spreadsheet
            await orderFulfillmentTable.updateRecordAsync(orderFulfillmentRecord, {
                "Course": lineItemName, // Update the Course field to match Line Items Name
                "PassionIT Order #": passionITOrderNumber
            });
        }
    }
}

Here is the Find records action that runs after the first script:

Then here is the second script that runs after Find records:

Finally here is the Update record that runs last:

[TheTimeSavingCo]

Hmm, I would double check that the script's checking for the names properly, from the error you mentioned it seems to not be checking for a direct match?  DM me an invite link and I'll see what I can do!