Count or Tally items from import

I am exporting orders from Woocommerce using Zapier into Airtable.

The Order from the customer is received into two separate cells like this;

[Apple,Orange,Pear] and [1,3,5]

I need Airtable to recognise that order is for; 1 x Apple 3 x Orange 5 x Pear

I also need to know how many I require for a certain City. For example if I receive the data like this;
[London][Apple,Orange,Pear][1,3,5]
[Manchester][Orange,Banana][2,5]
[Manchester][Apple][10]
[Birmingham][Orange,Pear][3,4]
[London][Pear,Orange][3,7]

I need to know;

  • London -
    Apple x 1
    Orange x 10
    Pear x 8

  • Manchester -
    Orange x 2
    Banana x 5
    Apple x 10

  • Birmingham -
    Orange x 3
    Pear x 4

- Total -
Apple x 11
Orange x 15
Pear x 12
Banana x 5

Please note that I will not be able to input the list of Cities and it would be Difficult to list all of the fruit. I would need the application to read the Items (Fruit) and Cities only by selecting it to read the contents of a cell.

Here I have provided 5 records per day but there could be up to 300. I am currently processing this manually.

Any help would be much appreciated. I hope. I’ve made sense.

Thank you

Hi @Aaron_Cummins,

Welcome to Airtable Community! :slight_smile:

The way I did this is:

  1. Use the Formatter, select Text to Line items

  2. This is not supported by Airtable so I had it pushed to Google Sheets and next step was pushing it to Airtable from Google Sheets.

Hope this helps :slight_smile:

BR,
Mo

1 Like

Thank you for your reply. I’m struggling to work out what you mean? Could you break down the steps please?

Thanks in advance

Sure.

Step 1: Setup a Google Sheet with the 3 columns. Cities, Fruit, Qty
Step 2: Make a Zap with Woocommerce as trigger
Step 3: Add Formatter by Zapier - Line Itemizer (Create / Append / Prepend)
In the Line Itemizer, follow the screenshot below


Step 4: Add a Google Sheets action - Create new row(S), you will find it says that it supports Line Items

Now populate the Google Sheet you created in Step 1 with the data you have from the Line Items, each in its respective column

Step 5: Create a new Zap, with Google Sheets as the trigger
Step 6: Add an action Create new record in Airtable

Hope this helps, feel free to come back if you have any question.

If this answers your question, please mark this as a Solution so others can see it and benefit from it.

BR,
Mo