How to integrate WordPress with Airtable

Using Airtable as a coupon system

Here at MemberFix, we’re big fans of Airtable.

Recently, one of our customers asked us to figure out a coupon assignment and redemption system for his deals-based membership site.

We decided upon a solution using Airtable and WordPress, which worked out quite nicely.

In this post you’ll learn how we solved our customer’s coupon problem, including the exact code snippets we used.

Perhaps you’ll even come up with your own unique solutions using this case study as a template. 🙂

If you do, make sure to share it with us!

Modeling AppSumo’s coupon redemption approach

The approach our customer asked us to model is from the popular deals site, AppSumo.

You see, when you buy an AppSumo deal you get a code—or, several codes if you bought multiple licenses—that you redeem in order to get access to the software you purchased.

We came up with a custom solution that works similarly to AppSumo by integrating Airtable with WordPress.

Why WordPress?

Because this particular customer’s deals based membership site is built on WordPress, so that’s where his members will be claiming and redeeming their coupon codes.

Let me explain what this scenario actually looks like with an example:

Let’s say you’ve made a deal with a JV partner who is going to give you 2000 coupon codes for his cool, new app.

You’re going to sell these coupon codes to your members and pay part of the fee to your JV partner.

Now, how do you ensure that each coupon code is used only once?

And for that matter, how do you:

• Randomly generate 2000 unique coupon codes…
• Track who bought and redeemed which codes…
• Share that information in a legible format with your JV partner so he can verify it…?

Doing it manually would take ages and wouldn’t be scalable, so that’s no good.

But with Airtable, WordPress and a little programming magic, we can streamline and automate this process almost entirely.

Airtable also provides a clean and attractive user interface for both parties, resulting in a big win.

Ok enough flirting, let’s see what this Airtable WordPress integration looks like!

Example and demo of the Airtable coupon database

We already have a demo site running with this integration and we have an Airtable database for this.

You can see the demo site by clicking here.

You can log in with the following credentials:

User: visitor
Password: visitor

Here is the Airtable database:

How does the Airtable coupon system work?

The underlying mechanics are:

1 – You build a base for your coupons in Airtable

2 – You add the integration in this post to your WordPress site

Everything runs by itself from there.

Now let’s see how to actually configure everything. step by step.

Airtable WordPress coupon system Video

Step 1 – Create your Airtable object

In order for you to have any Airtable data inside your WordPress site you need to build some functionality that will access your Airtable base to get that data and then pass it to WordPress.

Before that we need to know our Airtable base structure. Here are the fields we need to have for this use case:

Default Airtable Field – As the name says this is the default required field from Airtable. Usually, it’s called “name”.

Code – Single Line Text field

User_ID – Single Line Text field – Default = null

Assign_Status – Single Line Text field – Default – 0

User_Email – Single Line Text field

Partner – Single Line Text field

Here is the object that I built for this use case. I’ll explain all the methods below as well.

But before explaining I need to specify that this code has to be added into a custom plugin. If you don’t know how to create your own custom pluign here is a link to WordPress codex.

<?php

/**
 * The main Airtable object
 */

class mf_airtable
{
    private $mf_airtableApiKey = 'YOUR API KEY';
    private $mf_endPoint = 'https://api.airtable.com/v0/';
    private $mf_airtableBase = 'YOUR AIRTABLE BASE';


    /**
     * Check if the user has a code
     * @return bool;
     */

    public function mf_userHasCode($user_id_raw, $partner)
    {
        $user_id = strval($user_id_raw);
        $request = 'Master%20Tracker?maxRecords=3&view=Grid%20view&filterByFormula=IF(AND(User_ID='.$user_id.',Partner='.$partner.'),TRUE())';
        $con = curl_init();
        curl_setopt_array($con, array(
            CURLOPT_URL => $this->mf_endPoint . $this->mf_airtableBase .$request,
            CURLOPT_HTTPHEADER => array(
                "authorization: Bearer $this->mf_airtableApiKey",
            ),
            CURLOPT_RETURNTRANSFER => true
        ));

        $exec = curl_exec($con);
        $error = curl_error($con);
        curl_close($con);

        if ($error){
            die('cURL error:'.$error);
        }

        $response = json_decode($exec);

        if (empty($response->records)){
            return false;
        }else{
            return true;
        }
    }

    /**
     * Assign a code to the user if the user doesn't have one
     */

    public function mf_assignCode($user_id_raw, $partner, $email){
        $user_id = strval($user_id_raw);
        if ($this->mf_userHasCode($user_id,$partner) == false){
            $request = 'Master%20Tracker?maxRecords=3&view=Grid%20view&filterByFormula=AND(Partner='.$partner.',Assign_Status=0)';
            $con = curl_init();
            curl_setopt_array($con, array(
                CURLOPT_URL => $this->mf_endPoint . $this->mf_airtableBase .$request,
                CURLOPT_HTTPHEADER => array(
                    "authorization: Bearer $this->mf_airtableApiKey",
                ),
                CURLOPT_RETURNTRANSFER => true
            ));

            $exec = curl_exec($con);
            $error = curl_error($con);
            curl_close($con);

            if ($error){
                die('cURL error:'.$error);
            }

            $response = json_decode($exec);
            if (!empty($response->records)) {

                $notAssignedCode = $response->records->id;
                $request = 'Master%20Tracker/';
                $con = curl_init();
                $data = $user_id;
                $data = $email;
                $data = "1";
                $jsonData = json_encode($data);
                curl_setopt_array($con, array(
                    CURLOPT_URL => $this->mf_endPoint . $this->mf_airtableBase . $request . $notAssignedCode,
                    CURLOPT_HTTPHEADER => array(
                        "authorization: Bearer $this->mf_airtableApiKey",
                        "Content-Type: application/json"
                    ),
                    CURLOPT_RETURNTRANSFER => true,
                    CURLOPT_CUSTOMREQUEST => 'PATCH',
                    CURLOPT_POSTFIELDS => $jsonData
                ));

                $exec = curl_exec($con);
                $error = curl_error($con);
                curl_close($con);
            }else{
                return false;
            }
        }
    }

    /**
     * Return user's code if the user has a code
     * @return string
     */

    public function mf_getUsersCode($user_id_raw,$partner){
        $user_id = strval($user_id_raw);
        if ($this->mf_userHasCode($user_id,$partner) == true) {
            $request = 'Master%20Tracker?maxRecords=3&view=Grid%20view&filterByFormula=AND(User_ID=' . $user_id . ',Partner=' . $partner . ')';
            $con = curl_init();
            curl_setopt_array($con, array(
                CURLOPT_URL => $this->mf_endPoint . $this->mf_airtableBase . $request,
                CURLOPT_HTTPHEADER => array(
                    "authorization: Bearer $this->mf_airtableApiKey",
                ),
                CURLOPT_RETURNTRANSFER => true
            ));

            $exec = curl_exec($con);
            $error = curl_error($con);
            curl_close($con);

            if ($error) {
                die('cURL error:' . $error);
            }

            $response = json_decode($exec, true);
            return $response;
        }else{
            return false;
        }
    }
}

Pretty complex, huh? Let’s explain everything.

private $mf_airtableApiKey = 'YOUR API KEY';
private $mf_endPoint = 'https://api.airtable.com/v0/';
private $mf_airtableBase = 'YOUR AIRTABLE BASE';

The 3 private properties in the code snippet above keep the data that you need to login to the Airtable API.

$mf_airtableApiKey – As the name says, this stands for your API access key. Here is how you can find it.

$mf_endPoint – In my example, you can see that I added a default value for that. Because it’s unlikely for that to change until they update their API.

$mf_airtableBase – This is the base ID. Here is how you can find that:

1 – Access the Airtable API page

2 – Now click “MASTER TRACKER TABLE”

3 – Click “List Records”

4 – There you go, you have the base ID in the right console. In my screenshot it’s blurred for security reasons.

public function mf_userHasCode($user_id_raw, $partner)
{
    $user_id = strval($user_id_raw);
    $request = 'Master%20Tracker?maxRecords=3&view=Grid%20view&filterByFormula=IF(AND(User_ID='.$user_id.',Partner='.$partner.'),TRUE())';
    $con = curl_init();
    curl_setopt_array($con, array(
        CURLOPT_URL => $this->mf_endPoint . $this->mf_airtableBase .$request,
        CURLOPT_HTTPHEADER => array(
            "authorization: Bearer $this->mf_airtableApiKey",
        ),
        CURLOPT_RETURNTRANSFER => true
    ));

    $exec = curl_exec($con);
    $error = curl_error($con);
    curl_close($con);

    if ($error){
        die('cURL error:'.$error);
    }

    $response = json_decode($exec);

    if (empty($response->records)){
        return false;
    }else{
        return true;
    }
}

The method in the code snippet above checks if the current user has a coupon code assigned.

It does that by sending a CURL request to Airtable and then checking if the response has results or not.

And it returns a boolean (true or false) value based on that.

public function mf_assignCode($user_id_raw, $partner, $email){
    $user_id = strval($user_id_raw);
    if ($this->mf_userHasCode($user_id,$partner) == false){
        $request = 'Master%20Tracker?maxRecords=3&view=Grid%20view&filterByFormula=AND(Partner='.$partner.',Assign_Status=0)';
        $con = curl_init();
        curl_setopt_array($con, array(
            CURLOPT_URL => $this->mf_endPoint . $this->mf_airtableBase .$request,
            CURLOPT_HTTPHEADER => array(
                "authorization: Bearer $this->mf_airtableApiKey",
            ),
            CURLOPT_RETURNTRANSFER => true
        ));

        $exec = curl_exec($con);
        $error = curl_error($con);
        curl_close($con);

        if ($error){
            die('cURL error:'.$error);
        }

        $response = json_decode($exec);
        if (!empty($response->records)) {

            $notAssignedCode = $response->records->id;
            $request = 'Master%20Tracker/';
            $con = curl_init();
            $data = $user_id;
            $data = $email;
            $data = "1";
            $jsonData = json_encode($data);
            curl_setopt_array($con, array(
                CURLOPT_URL => $this->mf_endPoint . $this->mf_airtableBase . $request . $notAssignedCode,
                CURLOPT_HTTPHEADER => array(
                    "authorization: Bearer $this->mf_airtableApiKey",
                    "Content-Type: application/json"
                ),
                CURLOPT_RETURNTRANSFER => true,
                CURLOPT_CUSTOMREQUEST => 'PATCH',
                CURLOPT_POSTFIELDS => $jsonData
            ));

            $exec = curl_exec($con);
            $error = curl_error($con);
            curl_close($con);
        }else{
            return false;
        }
    }
}

Then, as shown in the code snippet above, it sends a CURL request to Airtable to assign to the current user a coupon code.

And it also changes the values of the other fields in the template.

Of course, there is some validation to see whether or not the coupon code is already assigned to somebody else.

public function mf_getUsersCode($user_id_raw,$partner){
    $user_id = strval($user_id_raw);
    if ($this->mf_userHasCode($user_id,$partner) == true) {
        $request = 'Master%20Tracker?maxRecords=3&view=Grid%20view&filterByFormula=AND(User_ID=' . $user_id . ',Partner=' . $partner . ')';
        $con = curl_init();
        curl_setopt_array($con, array(
            CURLOPT_URL => $this->mf_endPoint . $this->mf_airtableBase . $request,
            CURLOPT_HTTPHEADER => array(
                "authorization: Bearer $this->mf_airtableApiKey",
            ),
            CURLOPT_RETURNTRANSFER => true
        ));

        $exec = curl_exec($con);
        $error = curl_error($con);
        curl_close($con);

        if ($error) {
            die('cURL error:' . $error);
        }

        $response = json_decode($exec, true);
        return $response;
    }else{
        return false;
    }
}

As the name of the function in the first line of the code snippet above hints, this piece of code retrieves the coupon code for the current user, and returns it as a string.

Creating your WordPress helper

Like before, I’ll put the whole code here and will explain everything.

<?php

/**
 * Registers the short code for the coupon code
 */

function mf_add_code($atts){
    $atts = shortcode_atts(array(
        'partner'=>'No Partner',
    ),$atts,'mf_airtable_code');
    $mf_airtable = new mf_airtable();
    $mf_userid = get_current_user_id();
    $current_code = $mf_airtable->mf_getUsersCode($mf_userid,'"'.$atts.'"');
    //Check if the user is logged in and if has a code
    if (is_user_logged_in()){
        if (strlen($current_code) >= 1){
            return '<input id="mf_coupon_code" type="text" value="'.$mf_airtable->mf_getUsersCode($mf_userid,'"'.$atts.'"').'"><button onclick="mfCopytoClipboard()" class="infostak-btn light-blue-btn">Copy to Clipboard</button>';
        }
    }
}
add_shortcode('mf_airtable_code','mf_add_code');

/**
 * WP AJAX handlers
 */

function mf_assign_ajax(){
    $partner = '"'.$_POST.'"';

    // Check if the user is logged in
    if (is_user_logged_in()){
        $mf_currentUser = wp_get_current_user();
        $mf_airtable = new mf_airtable();
        $mf_userid = $mf_currentUser->ID;
        $mf_userEmail = $mf_currentUser->user_email;

        //Assign the code
        $mf_airtable->mf_assignCode($mf_userid,$partner,$mf_userEmail);

    }else{
        return 'You need to be logged in to redeem a code';
    }
}
add_action( 'wp_ajax_mf_assign_ajax', 'mf_assign_ajax' );
add_action( 'wp_ajax_nopriv_mf_assign_ajax', 'mf_assign_ajax' );

function mf_assign_button($atts){
    $atts = shortcode_atts(array(
        'partner'=>'No Partner'
    ),$atts,'mf_assign_code');
    $mf_airtable = new mf_airtable();
    $mf_userid = get_current_user_id();
    $current_code = $mf_airtable->mf_getUsersCode($mf_userid,'"'.$atts.'"');

    if (strlen($current_code) <= 1) {
        return '<a class="mf-btn light-blue-btn" partner="' . $atts . '" href="javascript:void(0)" id="mf-assign-code">Assign Code</a> <img id="mf-loader" style="width:50px; display:none;" src="' . plugin_dir_url(__FILE__) . '../assets/img/loader.gif' . '">';
    }
}
add_shortcode('mf_assign_code','mf_assign_button');

What the code snippet above does is it displays a button that assigns the coupon code to the current user.

It also displays the code for the current user if he has one.

Of course, only one of the options will display.

So that basically means that this code snippet checks if the user has a coupon code or not, and shows one of the options based on the result.

function mf_add_code($atts){
    $atts = shortcode_atts(array(
        'partner'=>'No Partner',
    ),$atts,'mf_airtable_code');
    $mf_airtable = new mf_airtable();
    $mf_userid = get_current_user_id();
    $current_code = $mf_airtable->mf_getUsersCode($mf_userid,'"'.$atts.'"');
    //Check if the user is logged in and if has a code
    if (is_user_logged_in()){
        if (strlen($current_code) >= 1){
            return '<input id="mf_coupon_code" type="text" value="'.$mf_airtable->mf_getUsersCode($mf_userid,'"'.$atts.'"').'"><button onclick="mfCopytoClipboard()" class="infostak-btn light-blue-btn">Copy to Clipboard</button>';
        }
    }
}
add_shortcode('mf_airtable_code','mf_add_code');

Next, the function in the code snippet above creates a shortcode that we will use inside of WordPress.

This shortcode passes partner data so our Airtable object will know which partner the code comes from.

function mf_assign_ajax(){
    $partner = '"'.$_POST.'"';

    // Check if the user is logged in
    if (is_user_logged_in()){
        $mf_currentUser = wp_get_current_user();
        $mf_airtable = new mf_airtable();
        $mf_userid = $mf_currentUser->ID;
        $mf_userEmail = $mf_currentUser->user_email;

        //Assign the code
        $mf_airtable->mf_assignCode($mf_userid,$partner,$mf_userEmail);

    }else{
        return 'You need to be logged in to redeem a code';
    }
}
add_action( 'wp_ajax_mf_assign_ajax', 'mf_assign_ajax' );
add_action( 'wp_ajax_nopriv_mf_assign_ajax', 'mf_assign_ajax' );

function mf_assign_button($atts){
    $atts = shortcode_atts(array(
        'partner'=>'No Partner'
    ),$atts,'mf_assign_code');
    $mf_airtable = new mf_airtable();
    $mf_userid = get_current_user_id();
    $current_code = $mf_airtable->mf_getUsersCode($mf_userid,'"'.$atts.'"');

    if (strlen($current_code) <= 1) {
        return '<a class="mf-btn light-blue-btn" partner="' . $atts . '" href="javascript:void(0)" id="mf-assign-code">Assign Code</a> <img id="mf-loader" style="width:50px; display:none;" src="' . plugin_dir_url(__FILE__) . '../assets/img/loader.gif' . '">';
    }
}
add_shortcode('mf_assign_code','mf_assign_button');

Next, one of the functions from the code snippet above creates a shortcode that will display the assignment button. 

The other one handles all the data passing through AJAX.

Speaking of AJAX, here is the javascript code behind that:

jQuery(document).ready(function(){
jQuery('#mf-assign-code').click(function () {
jQuery('#mf-assign-code').hide();
        jQuery('#mf-loader').css('display','block');
        jQuery.post(
            ajaxurl,
            {
'action': 'mf_assign_ajax',
                'data':jQuery('#mf-assign-code').attr('partner')
            },
            function(){
                document.location.reload(true);
            }
        );
    });
});

function mfCopytoClipboard() {
/* Get the text field */
    var copyText = document.getElementById("mf_coupon_code");

    /* Select the text field */
    copyText.select();

    /* Copy the text inside the text field */
    document.execCommand("copy");
}

This javascript code sends the data from the front end to the back end through AJAX and also adds some copy/paste functionality to the code form.

Adding the functionality to your website

Since you have 2 shortcodes that are displaying our data in the front-end you have to use them in your pages.

In our case, our shortcodes would look like this:

 – Shows the current code of the user if the user has one

 – Shows the “Assign” button 

Final Result

All done! ?

Now let’s hear from you!

What kind of coupon codes are you offering through this integration?

Tell us in the comments section below.