About Zapier Community
New to our Community? Get to know more about our space and how to make the most of your time here.

Code of Conduct Community FAQ's Zapier Common Questions What to Expect

Get Started With Zapier
New to Zapier? We have got you covered with our Zapier 101 Resources.

Zapier Essentials

Get Support
Have a question? Let's get you an answer!

How Do I...? Troubleshooting Code & Webhooks Developer Zone

Other Help Resources
Wait, there's more! Check out these Community sourced spaces for additional resources.

Zapier Help Center Hire an Expert

Zapier Reading
All of our best learning resources can be found here.

Featured Articles Show & Tell AI Hub Zapier Tools Hub

Learning Groups
Excited to meet other builders? Check out our interest areas full of doers, dreamers, and in-betweeners.

User Groups

Other Learning Resources
Some of our favorite additional ways to upskill your Zapier knowledge.

Zapier Learn Zapier Blog Zapier Webinars

Product Updates
Check out what is new and upcoming with Zapier with our regular product updates.

Product Updates

Early Access Program
Want to join Early Access? Learn more here.

Question

Airtable Automation - Deleting Values in Records

  • 4 January 2023
  • 4 replies
  • 440 views
Jamie Reynolds

Hi Zapier Community 👋

I am struggling with a challenge that I would love your help with!

Context:

I co-founded the business PS Bridal to help brides find their perfect wedding dress, for free. We use Zapier to automate a lot of our internal processes - and I am trying to setup a new automated process for a platform we are about to launch to help brides rent their perfect wedding dress.

Brides will visit a marketplace where they can view dresses for rent, select a booking period and order the dress via our platform. The platform itself is built using the following:

  1. Front-end: Webflow
  2. Back-end: Zapier, Booqable, Airtable

Challenge:

The automation that I want to create will enable us to automatically update a dress’s rental period availability.

The flow is this:

  1. Bride books a dress on Webflow
  2. Booqable receives the booking
  3. Booqable triggers a Zap which updates the dress’s availability in Airtable
  4. The Zap triggers a sync between Airtable + Webflow’s CMS to update that dress’s availability
  5. The dress’s availability is updated on Webflow so that the dress is no longer available for the booked period

The step that I am struggling with in this flow is step 3, which is where I need your help with this Zap.

Zap:

Here is an overview of the steps in the Rental Booking Automation Zap:

  1. Trigger: Reserved Order in Booqable
  2. Action: Find the Record Name in Airtable that matches the Booqable Reserved Order Name
  3. Action: re-format the Date into a string, e.g. 01-03-2023 10:10:10 > 1st March 2023 at 10.10am
  4. Action: pull out keywords from the string, e.g. Mar
  5. Action: pull out key numbers from the string, e.g 23
  6. Action: combine the keyword and key number into a string, e.g. “Mar-23”
  7. Filter:
    1.  Check if the Dates Available column in Airtable contains the Date string “Mar-23”  
      1. IF YES: Zap continues
      2. IF NO: ZAP stops
  8. I want to delete the Date string “Mar-23” from the Dates Available column, which contains multiple values, which I believe are contained in an Array: e.g. Jan-23, Feb-23, Mar-23, Apr-23, May-23, etc.
    1. This is where I am struggling for a solution :)
Zapier Flow Overview

 

Here is a screenshot of the Airtable Table 1 that I am interacting with in Zapier. You can see that the Dates Available column is a lookup column in another Table 2. I have added an additional column into Table 1 that contains a formula which adds the string description of the Dates Available, as these just appear as IDs in Zapier - which makes it hard to complete step 7 of my flow.

 

Airtable Table 1

 

To solve this problem, I have tried:

  1. Using Javascript (which I have no experience in 😅) to try to remove the string from the array. This ended in disaster (see below screenshot):
    1. Adventures in the land of Javascript
    2. Failures in the land of Javascript
  2. Using Zapier’s spreadsheet function to look up the dress name in Table 2 and remove it from the Dates Available - i.e. remove A.M. Faulkner Dress from Mar-23. This also failed:
    1. Spreadsheet Style Formula

       

I am now well and truly stuck with this automation and the kind @nicksimard suggested that I make a post here. 

I have tried to thoroughly explain what I have done and the issue that I am facing, but please let me know if you have any questions! 🙂

All the best,

Jamie

This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

4 replies

Troy Tessalone
Userlevel 7
Badge +14

Hi @Jamie Reynolds 

Good question.

The screenshots from Airtable did not post, so please amend with those for context, thanks.

 

Add object brackets {} within the  array brackets [] like this:

output = [{array_3}]

 

 

TIP: You can save Zap Tasks by using 1 Code step instead of multiple Formatter steps.

Consider hiring a Zapier Expert to help with the Code: https://zapier.com/experts

⚡ Troy Tessalone - AutomationAce.com | Premier Certified Zapier Expert | #1 Zapier Community Contributor
Todd Harper
Userlevel 6
Badge +8

Hi @Jamie Reynolds

Code is definitely the way to go here. You can replace every step following the trigger with a single Code by Zapier step, including your Filter by Zapier step, each of your Filter by Zapier steps, and all of the various back and forth interactions with Airtable you’ll need to do. Airtable’s API reference is actually fairly easy to grasp with some light programming knowledge.

Having screenshots from both tables in your Airtable would be really helpful to provide further guidance.

For now, here’s some Javascript to get you started :)

// First, we convert the availableDates string from Airtable into an array.
const availableDates = inputData.availableDates.split(", ");


// Next, we convert the rentalDate into an easy to read string.
var rentalDate = new Date(inputData.rentalDate);
rentalDate = rentalDate.toString();


// Then we pull the month and day from the rental date and combine them together.
var rentalMonth = rentalDate.split(" ")[1]
var rentalDay = rentalDate.split(" ")[2]
var formattedDate = rentalMonth + "-" + rentalDay


// This loop determines whether the formattedDate is in your availableDates array.
// If there is a match, it removes it from the array.
// Otherwise, it does nothing, essentially mimicking your Filter by Zapier step.
for (var i = 0; i < availableDates.length; i++) {
  if (availableDates[i] == formattedDate) {
    // This code executes when the date is a match.
    availableDates.splice(i, 1);
    // Note: This does not automatically update your Airtable records.
    // You'll need to do that programatically by accessing Airtable's API.
    // Be sure to place all API calls above the word "break" below.
    // Assuming you do all data manipulation and API calls within this conditional statement, there will be no need for an output statement below, as you will simply update your Airtable base(s) from here.
    break;
  } else {
    //leave this blank to mimic the Filter by Zapier.
  }
}

 

nicksimard
Userlevel 7
Badge +11

Hey @Jamie Reynolds!

I think I’ve cracked it :)

UPDATE: As I was writing this response, Todd jumped in with some code 😂 Feel free to try his first, if you’d rather use fewer steps. At the very least, maybe my response taught you a coupe of things haha.

Here’s the setup I’ve got for the part of the Zap up through updating Airtable, since that’s where you were stuck:

Trigger: Booqable
Action: Airtable — Find Record
Action: Formatter — Format Date (Into the Mar-23 format)
Action: Formatter — Lookup Table (“Convert” the date into the Record ID)
Action: Formatter — Replace  (In the field that returns all the record IDs of ‘months available’ search for the one from the Lookup Table step then replace it with nothing, which deletes it)
Action: Airtable — Update Record (map the field that now contains all the existing records minus the one that’s in the Booqable step)

Quick GIF Tour

6267e8eef51f922f5577a04d44dc0056.gif


NOTE: You’ll have to factor in the edge case where a dress has ONLY that one month in it, in which case Airtable requires exactly three spaces to be added in order to clear it.

 

One way to do this would be to add a Count field in Airtable, then you have two Zaps. One for when the Count is 1 and another for when the Count is greater than 1:

 

What I described above would be for greater than 1. When the count is 1, presumably the only month in there is the one you want to remove. You could add a Filter like this as well (after the date format step):

 

Whew, ok. Are you able to run with that to see if it works for your use case? Please let me know if you have any questions as you try this out :)

Jamie Reynolds

Hey everyone,

My apologies for my slow reply - I had a personal tragedy in January which took some time to recover from. 

I will be looking into this again in the coming days.

Thank you soooooo much for your help in advance 🙏

All the best,

Jamie

Terms & Conditions