Wordpress ACF field update from Airtable using Zapier


#1

Hi everyone,

Don’t know if this is of any help to anyone - or if there something similar out there. I couldn’t find this anywhere.

Just a bit of background - we run a e-com website on wordpress. About 2k products overall, 50k “posts” including meta. We also use Advanced Custom Fields to manage special fields against products - including files.

We use Airtable to actually gather all the data with multiple stakeholders involved in the collection.

The problem:
One of the most annoying things has been updating the technical images of the products to a better, newer version that might be uploaded to AT.

The idea:

Upon a trigger event, update the website’s MySQL db using Zapier.

The (beta) execution:

A. Get you Wordpress MySQL db hooked up to zapier … this took a bit of time to figure out but after couple of quick exchanges with our hosting company all was good.

B. Setting up the Zap - 5min

  1. SLACK - Test Trigger - Post “SKU” as message on a private slack channel (instant trigger)

  2. AIRTABLE - Find Record in Airtable - Looks up the SKU in right table

  3. WORDPRESS - Upload Media to Media Library

  4. MYSQL - Custom Query – SELECT * FROM [wp_database].[wp_postmeta] WHERE meta_key = “_sku” AND meta_value = “{SKU}”;

  5. MYSQL - SELECT * FROM [wp_database].[wp_postmeta] WHERE post_id = “{post id from step 4}” AND meta_key = “technical_images”;

6a. MYSQL - Update Row --> meta id from STEP 5 // meta value from STEP 3 // Row --> Add search step and refer to step 6b // ID Column --> meta_id

6b .MYSQL = Find Row - Lookup value --> Meta ID from Step 5

The result

The zap takes about 18-20sec to execute. The website’s database is updated :slight_smile:

Notes

It’s only been tested a few times - but it works like a charm … i’ll work a bit more on the triggering side of things… I’m only learning a bit of SQL currently - so I’m pretty sure you can get them two queries in one where you get data from two tables.

The only negative is that you gotta have a subscription to zapier


#2

nice work. Is this a woocommerce based store or something else in wordpress? I’m about to launch a woocommerce storefront and familiar with their zapier connector and also done a lot of airtable so would be interested in following your progress on this project. keep us posted.


#3

Yep - it’s based on woocommerce…

We’ve we’ve been using All Import for changes similar to this. It can take about 10-15min to get things set up and ready.

With this method it will take nearly 20sec to update these files.