Skip to main content
Solved

Parsing Data from Webhook

  • November 23, 2022
  • 8 replies
  • 82 views
Good_Ink
Forum|alt.badge.img+7
  • Good_Ink
  • Participating Frequently
  • 8 replies

We use a webhook from Shopify to capture order data in Airtable. When the webhook comes in, we create a record in Airtable with pertinent order data. I want to parse out the line items in the order into another table for use in creating a packing slip.

Here is sample data: Airtable - MA Test
The Data table represents the data as it comes in from the webhook.
The Desired Output table represents the ideal output. This is where I need help, creating an automation/script to parse out that data into new rows.

Thanks for your help!

Best answer by TheTimeSavingCo

I’ve put something together here that should do what you’re looking for

It currently only handles 4 line items, but you can make it handle as many as you want by just modifying the formula

Works by using formula fields to put the line item name and quantity together, then using an automation to paste that into a linked field to the Desired Output table

The Desired Output table in turn has formula fields to separate the line item name and quantity out

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

Lemme know if you have any problems setting it up yourself (Or you can also hire me to set it up for you too!)

8 replies

ScottWorld
Forum|alt.badge.img+35
  • ScottWorld
  • Genius
  • 9808 replies
  • November 23, 2022

Welcome to the community, @Good_Ink!

I don’t know JavaScript, so somebody else would need to chime in below to help you with writing a script for this.

However, for a no-code way of doing this that doesn’t require any scripting at all, you can simply use Make’s advanced automations & integrations to handle all of this for you.

You can either use a Make webhook to receive your Shopify data, or you can use Make's native Shopify modules to receive your Shopify data.

In either approach, you can loop through your Shopify line items to create the desired records in Airtable.

If you’ve never used Make before, I’ve assembled a bunch of Make training resources in this thread. For example, here is one of the ways that you could instantly trigger a Make automation from Airtable. 

I also give live demonstrations of how to use Make in many of my Airtable podcast appearances. For example, in this video, I show how to work with Airtable arrays in Make.

Hope this helps!

If you’d like to hire the best Airtable consultant to help you with anything Airtable-related, please feel free to contact me through my website: Airtable consultant — ScottWorld

TheTimeSavingCo
Forum|alt.badge.img+31
  • TheTimeSavingCo
  • Brainy
  • 6457 replies
  • Answer
  • November 24, 2022

I’ve put something together here that should do what you’re looking for

It currently only handles 4 line items, but you can make it handle as many as you want by just modifying the formula

Works by using formula fields to put the line item name and quantity together, then using an automation to paste that into a linked field to the Desired Output table

The Desired Output table in turn has formula fields to separate the line item name and quantity out

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

Lemme know if you have any problems setting it up yourself (Or you can also hire me to set it up for you too!)

Adam, Airtable Consultant - thetimesaving.company
Good_Ink
Forum|alt.badge.img+7
  • Good_Ink
  • Author
  • Participating Frequently
  • 8 replies
  • December 20, 2022

I’ve put something together here that should do what you’re looking for

It currently only handles 4 line items, but you can make it handle as many as you want by just modifying the formula

Works by using formula fields to put the line item name and quantity together, then using an automation to paste that into a linked field to the Desired Output table

The Desired Output table in turn has formula fields to separate the line item name and quantity out

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button.

Lemme know if you have any problems setting it up yourself (Or you can also hire me to set it up for you too!)


Thank you for spending time working on this! I apologize that it took me so long to get back to you. This is nearly perfect for our needs. As every order can vary in size, I'm trying to figure out a way to make the formula dynamic. Any ideas in that regard is appreciated but your efforts thus far are much appreciated.

TheTimeSavingCo
Forum|alt.badge.img+31
  • TheTimeSavingCo
  • Brainy
  • 6457 replies
  • December 21, 2022

Thank you for spending time working on this! I apologize that it took me so long to get back to you. This is nearly perfect for our needs. As every order can vary in size, I'm trying to figure out a way to make the formula dynamic. Any ideas in that regard is appreciated but your efforts thus far are much appreciated.


No worries.  It's built to handle up to 4 line items but should be able to handle dozens, the only limiting factor is the formula fields character limit

I've updated the base to handle 10 line items now and you should be able to recognize the pattern for which value you need to change, you'll just need to extend it to whatever upper bound you're comfortable with

Adam, Airtable Consultant - thetimesaving.company
Good_Ink
Forum|alt.badge.img+7
  • Good_Ink
  • Author
  • Participating Frequently
  • 8 replies
  • December 21, 2022

No worries.  It's built to handle up to 4 line items but should be able to handle dozens, the only limiting factor is the formula fields character limit

I've updated the base to handle 10 line items now and you should be able to recognize the pattern for which value you need to change, you'll just need to extend it to whatever upper bound you're comfortable with


I did expand the formula to accept more line items as well but then noticed a large order of over 100 line items. I am writing a script that will hopefully handle this situation at scale. I will post what I come up with. Thanks again for your help.

Good_Ink
Forum|alt.badge.img+7
  • Good_Ink
  • Author
  • Participating Frequently
  • 8 replies
  • August 3, 2023

I finally got around to writing the code to handle this situation, hopefully it can help someone else.

Assumes you are using the "Run Script" automation action and that you have tables "Data" and "Test." Also change the input variables for your use.

let config = input.config(); let table = base.getTable("Data"); let nextTable = base.getTable("Test"); let items = config.items; let orderNumber = config.orderNumber; let id = config.recordID; let inputItems = items; let inputQty = config.quantity; // Define the regular expression pattern for capturing items in the list let regex = /[^,\s][^\,]*[^,\s]*/g; // Use the match() function to find all occurrences of the pattern in the input string let capturedItems = inputItems.match(regex); let capturedQty = inputQty.match(regex); console.log(capturedItems); for (let i = 0; i<capturedItems.length; i++) { await nextTable.createRecordsAsync([ { fields: { "Order #": orderNumber, "Item": capturedItems[i], "Quantity": capturedQty[i], }, }, ]) }

 

L
Forum|alt.badge.img+18
  • Leslie_Burke
  • Inspiring
  • 55 replies
  • April 4, 2024

I finally got around to writing the code to handle this situation, hopefully it can help someone else.

Assumes you are using the "Run Script" automation action and that you have tables "Data" and "Test." Also change the input variables for your use.

let config = input.config(); let table = base.getTable("Data"); let nextTable = base.getTable("Test"); let items = config.items; let orderNumber = config.orderNumber; let id = config.recordID; let inputItems = items; let inputQty = config.quantity; // Define the regular expression pattern for capturing items in the list let regex = /[^,\s][^\,]*[^,\s]*/g; // Use the match() function to find all occurrences of the pattern in the input string let capturedItems = inputItems.match(regex); let capturedQty = inputQty.match(regex); console.log(capturedItems); for (let i = 0; i<capturedItems.length; i++) { await nextTable.createRecordsAsync([ { fields: { "Order #": orderNumber, "Item": capturedItems[i], "Quantity": capturedQty[i], }, }, ]) }

 


@Good_Ink I was using your code above to do something similar to what you describe, but it threw an error. Any ideas on how to fix it?

Good_Ink
Forum|alt.badge.img+7
  • Good_Ink
  • Author
  • Participating Frequently
  • 8 replies
  • April 9, 2024

@Good_Ink I was using your code above to do something similar to what you describe, but it threw an error. Any ideas on how to fix it?


Hmmm, not sure. I'm assuming you have an input variable setup for items?


Terms & ConditionsAccessibility statement