Woocommerce: Extract certain line_items meta_data

Hi,
Thanks for all the efforts being put to come up with such a wonderful solution. We have been trying to use n8n to do some automation between Woocommerce and ERPNext. Out of the box, the new order trigger isn’t passing some of elements created by 3rd party plugins (booking activities), and also doesnt provide details for product variation (product attributes). We are trying to implement a work-around where ERPNext will trigger a call to webhook upon new entry in the (sales order item) to grab the missing elements and insert them into a custom field part of the sales order item (notes).

However, we are facing a challenge as the meta_data for the line_items has one field which contains the booking data as list in the json output below, we need to extract the Event data for every item_list where the key equals to bookacti_bookings (Event:Morning Tour - Friday, November 12th, 2021 8:00 AM → 11:00 AM x1).

        {
          "id": 203,
          "key": "bookacti_bookings",
          "value": "[{\"id\":154,\"type\":\"single\"}]",
          "display_key": "Booking",
          "display_value": "Booking ID:154Status:PendingEvent:Morning Tour - Friday, November 12th, 2021 8:00 AM → 11:00 AM x1"
        }

Also, for product variations, we are interested in the details of the product attributes Kayak Type and Duration as shown below.

      "meta_data": [
        {
          "id": 213,
          "key": "pa_kayak-type",
          "value": "single-kayak",
          "display_key": "Kayak Type",
          "display_value": "Single"
        },
        {
          "id": 214,
          "key": "pa_duration",
          "value": "1-hour",
          "display_key": "Duration",
          "display_value": "1 Hour"
        }
      ]

Provided below the json output, appreciate your guidance and support, thanks in advance…

{
  "id": 1234,
  "parent_id": 0,
  "status": "on-hold",
  "currency": "OMR",
  "version": "5.8.0",
  "prices_include_tax": false,
  "date_created": "2021-11-08T04:28:40",
  "date_modified": "2021-11-08T04:28:40",
  "discount_total": "0.000",
  "discount_tax": "0.000",
  "shipping_total": "0.000",
  "shipping_tax": "0.000",
  "cart_tax": "0.000",
  "total": "110.000",
  "total_tax": "0.000",
  "customer_id": 2,
  "order_key": "wc_order_xxxxxxxxx",
  "billing": {
    "first_name": "YOUS H",
    "last_name": "ALI",
    "company": "",
    "address_2": "",
    "city": "Muscat",
    "state": "MUSCAT",
    "postcode": "115",
    "country": "OM",
  },
  "shipping": {
    "first_name": "",
    "last_name": "",
    "company": "",
    "address_1": "",
    "address_2": "",
    "city": "",
    "state": "",
    "postcode": "",
    "country": "",
    "phone": ""
  },
  "payment_method": "bacs",
  "payment_method_title": "Direct bank transfer",
  "transaction_id": "",
  "date_completed": null,
  "date_paid": null,
  "cart_hash": "xxxxxxxxxxxxxxxxxxx",
  "number": "1234",
  "meta_data": [
    {
      "id": 12588,
      "key": "is_vat_exempt",
      "value": "no"
    },
    {
      "id": 12592,
      "key": "_wcpdf_invoice_settings",
      "value": {
        "enabled": "1",
        "attach_to_email_ids": {
          "new_order": "1",
          "customer_completed_order": "1",
          "customer_invoice": "1"
        },
        "display_shipping_address": "",
        "display_email": "1",
        "display_phone": "1",
        "display_customer_notes": "1",
        "display_date": "order_date",
        "display_number": "invoice_number",
        "number_format": {
          "prefix": "[invoice_year]-[invoice_month]- ",
          "suffix": "",
          "padding": ""
        },
        "reset_number_yearly": "1",
        "my_account_buttons": "available",
        "invoice_number_column": "1",
        "disable_free": "1",
        "use_latest_settings": "1",
        "paper_size": "a4",
        "font_subsetting": false,
        "header_logo": "666",
        "header_logo_height": "",
        "shop_name": {
          "default": "Oman LLC"
        },
        "shop_address": {
          "default": "Marina Muttrah, Sultanate of Oman"
        },
        "footer": {
          "default": "This booking is subject to our terms and conditions.  By reserving this activity or trip you agree to the terms and conditions detailed on our website (http:\/\/capitano.om\/terms-and-conditions)."
        },
        "extra_1": {
          "default": ""
        },
        "extra_2": {
          "default": ""
        },
        "extra_3": {
          "default": ""
        }
      }
    },
    {
      "id": 12593,
      "key": "_wcpdf_invoice_date",
      "value": "1636331320"
    },
    {
      "id": 12594,
      "key": "_wcpdf_invoice_date_formatted",
      "value": "2021-11-08 04:28:40"
    },
    {
      "id": 12595,
      "key": "_wcpdf_invoice_number",
      "value": "2021-11- 9"
    },
    {
      "id": 12596,
      "key": "_wcpdf_invoice_number_data",
      "value": {
        "number": 9,
        "formatted_number": "2021-11- 9",
        "prefix": "[invoice_year]-[invoice_month]- ",
        "suffix": "",
        "document_type": "invoice",
        "order_id": 1234,
        "padding": ""
      }
    },
    {
      "id": 12597,
      "key": "_new_order_email_sent",
      "value": "true"
    }
  ],
  "line_items": [
    {
      "id": 19,
      "name": "Al Khayran Tour",
      "product_id": 116,
      "variation_id": 0,
      "quantity": 1,
      "tax_class": "",
      "subtotal": "100.000",
      "subtotal_tax": "0.000",
      "total": "100.000",
      "total_tax": "0.000",
      "taxes": [],
      "meta_data": [
        {
          "id": 203,
          "key": "bookacti_bookings",
          "value": "[{\"id\":154,\"type\":\"single\"}]",
          "display_key": "Booking",
          "display_value": "Booking ID:154Status:PendingEvent:Morning Tour - Friday, November 12th, 2021 8:00 AM → 11:00 AM x1"
        }
      ],
      "sku": "CO-ADV-02",
      "price": 100,
      "parent_name": null
    },
    {
      "id": 20,
      "name": "Kayaking",
      "product_id": 726,
      "variation_id": 735,
      "quantity": 1,
      "tax_class": "",
      "subtotal": "10.000",
      "subtotal_tax": "0.000",
      "total": "10.000",
      "total_tax": "0.000",
      "taxes": [],
      "meta_data": [
        {
          "id": 213,
          "key": "pa_kayak-type",
          "value": "single-kayak",
          "display_key": "Kayak Type",
          "display_value": "Single"
        },
        {
          "id": 214,
          "key": "pa_duration",
          "value": "1-hour",
          "display_key": "Duration",
          "display_value": "1 Hour"
        }
      ],
      "sku": "CO-ACT-03-11",
      "price": 10,
      "parent_name": "Kayaking"
    }
  ],
  "tax_lines": [],
  "shipping_lines": [],
  "fee_lines": [],
  "coupon_lines": [],
  "refunds": [],
  "date_created_gmt": "2021-11-08T00:28:40",
  "date_modified_gmt": "2021-11-08T00:28:40",
  "date_completed_gmt": null,
  "date_paid_gmt": null,
  "currency_symbol": "OMR",
  "_links": {
    "self": [
      {
      }
    ],
    "collection": [
      {
      }
    ],
    "customer": [
      {
      }
    ]
  }
}

we need to extract the Event data for every item_list where the key equals to bookacti_bookings

What is the event data exactly?

You can do this with a function node, you can check line_items and check the metadata to see if there is one object with key: bookacti_bookings.

I can provide a sample function node that provides the output you are interested in getting If you give an example of the output you want.

@RicardoE105 Thanks for your support and help. Below is the logic and the sample output, hope you can guide me through it.

Note: The data is related to booking boat tours and activities (kayaking, Jet Skiing etc…) Event is a booked boat tour (product/service) which takes place on a specific date/time. The activities are booked for a duration, for specific type (rental).

Currently, we have Woocommerce sending new orders to ERPNext directly. However, some details are required for billing and also for operations staff. Once A new order created in ERPNext, ERPNext fires a webhook to n8n with the order number. In n8n we are trying to get fetch data from WC as per the details below and send an update request to ERPNext which has a field “Notes” next to each line item created in the order.

Get: Order No => "id": 1394
	"line_items":
	Get: "name": ABCD

	"meta_data":
	IF "key": "bookacti_bookings"

		Extract: "display_value":  
		Remove string -> Status:Pending <replace with white space> (e.g: "Booking ID:154 Event:Morning Tour - Friday, November 12th, 2021 8:00 AM → 11:00 AM x1")
		SET Item_notes (new field) => Extracted display_value
	
	ELSE
		Get: "name": ABCD
	"meta_data":

		Extract: Values display_key display_value for each meta_data item (concatenate with white space between, e.g: Kayak Type Single Duration 1 Hour)
		SET Item_notes (new field) => Extracted Values display_key display_value
	END IF
[
{
"id": 1394, // Order No
"line_items": [
{
"id": 1, // sequence
"name": "Al Khayran Tour",
"Item_notes": "Booking ID:154 Event:Morning Tour - Friday, November 12th, 2021 8:00 AM → 11:00 AM x1"
}
{
"id": 2,  // sequence
"name": "Kayaking",
"Item_notes": "Kayak Type Single Duration 1 Hour"
}
]
}
]

The function node below should do it. Also check the example workflow below the function node.

const lineItems = items[0].json.line_items

const id = items[0].json.id

const _lineItems = [];

for (const [index, lineItem] of lineItems.entries()) {
  if (lineItem.meta_data[0].key === 'bookacti_bookings') {
      _lineItems.push({
        id: index + 1,
        name: lineItem.name,
        Item_notes: lineItem.meta_data[0].display_value
      })
  } else {
    let note = '';
    for (const meta of lineItem.meta_data) {
       note+= `${meta.display_key} ${meta.display_value} `
    }
    _lineItems.push({
        id: index + 1,
        name: lineItem.name,
        Item_notes: note
    })
  }
}

return [
  {
    json: {
      id,
      line_items: _lineItems
    }
  }
]
Example workflow
{
  "nodes": [
    {
      "parameters": {},
      "name": "Start",
      "type": "n8n-nodes-base.start",
      "typeVersion": 1,
      "position": [
        200,
        220
      ]
    },
    {
      "parameters": {
        "functionCode": "return [\n  {\n    json: {\n  \"id\": 1234,\n  \"parent_id\": 0,\n  \"status\": \"on-hold\",\n  \"currency\": \"OMR\",\n  \"version\": \"5.8.0\",\n  \"prices_include_tax\": false,\n  \"date_created\": \"2021-11-08T04:28:40\",\n  \"date_modified\": \"2021-11-08T04:28:40\",\n  \"discount_total\": \"0.000\",\n  \"discount_tax\": \"0.000\",\n  \"shipping_total\": \"0.000\",\n  \"shipping_tax\": \"0.000\",\n  \"cart_tax\": \"0.000\",\n  \"total\": \"110.000\",\n  \"total_tax\": \"0.000\",\n  \"customer_id\": 2,\n  \"order_key\": \"wc_order_xxxxxxxxx\",\n  \"billing\": {\n    \"first_name\": \"YOUS H\",\n    \"last_name\": \"ALI\",\n    \"company\": \"\",\n    \"address_2\": \"\",\n    \"city\": \"Muscat\",\n    \"state\": \"MUSCAT\",\n    \"postcode\": \"115\",\n    \"country\": \"OM\",\n  },\n  \"shipping\": {\n    \"first_name\": \"\",\n    \"last_name\": \"\",\n    \"company\": \"\",\n    \"address_1\": \"\",\n    \"address_2\": \"\",\n    \"city\": \"\",\n    \"state\": \"\",\n    \"postcode\": \"\",\n    \"country\": \"\",\n    \"phone\": \"\"\n  },\n  \"payment_method\": \"bacs\",\n  \"payment_method_title\": \"Direct bank transfer\",\n  \"transaction_id\": \"\",\n  \"date_completed\": null,\n  \"date_paid\": null,\n  \"cart_hash\": \"xxxxxxxxxxxxxxxxxxx\",\n  \"number\": \"1234\",\n  \"meta_data\": [\n    {\n      \"id\": 12588,\n      \"key\": \"is_vat_exempt\",\n      \"value\": \"no\"\n    },\n    {\n      \"id\": 12592,\n      \"key\": \"_wcpdf_invoice_settings\",\n      \"value\": {\n        \"enabled\": \"1\",\n        \"attach_to_email_ids\": {\n          \"new_order\": \"1\",\n          \"customer_completed_order\": \"1\",\n          \"customer_invoice\": \"1\"\n        },\n        \"display_shipping_address\": \"\",\n        \"display_email\": \"1\",\n        \"display_phone\": \"1\",\n        \"display_customer_notes\": \"1\",\n        \"display_date\": \"order_date\",\n        \"display_number\": \"invoice_number\",\n        \"number_format\": {\n          \"prefix\": \"[invoice_year]-[invoice_month]- \",\n          \"suffix\": \"\",\n          \"padding\": \"\"\n        },\n        \"reset_number_yearly\": \"1\",\n        \"my_account_buttons\": \"available\",\n        \"invoice_number_column\": \"1\",\n        \"disable_free\": \"1\",\n        \"use_latest_settings\": \"1\",\n        \"paper_size\": \"a4\",\n        \"font_subsetting\": false,\n        \"header_logo\": \"666\",\n        \"header_logo_height\": \"\",\n        \"shop_name\": {\n          \"default\": \"Oman LLC\"\n        },\n        \"shop_address\": {\n          \"default\": \"Marina Muttrah, Sultanate of Oman\"\n        },\n        \"footer\": {\n          \"default\": \"This booking is subject to our terms and conditions.  By reserving this activity or trip you agree to the terms and conditions detailed on our website (http:\\/\\/capitano.om\\/terms-and-conditions).\"\n        },\n        \"extra_1\": {\n          \"default\": \"\"\n        },\n        \"extra_2\": {\n          \"default\": \"\"\n        },\n        \"extra_3\": {\n          \"default\": \"\"\n        }\n      }\n    },\n    {\n      \"id\": 12593,\n      \"key\": \"_wcpdf_invoice_date\",\n      \"value\": \"1636331320\"\n    },\n    {\n      \"id\": 12594,\n      \"key\": \"_wcpdf_invoice_date_formatted\",\n      \"value\": \"2021-11-08 04:28:40\"\n    },\n    {\n      \"id\": 12595,\n      \"key\": \"_wcpdf_invoice_number\",\n      \"value\": \"2021-11- 9\"\n    },\n    {\n      \"id\": 12596,\n      \"key\": \"_wcpdf_invoice_number_data\",\n      \"value\": {\n        \"number\": 9,\n        \"formatted_number\": \"2021-11- 9\",\n        \"prefix\": \"[invoice_year]-[invoice_month]- \",\n        \"suffix\": \"\",\n        \"document_type\": \"invoice\",\n        \"order_id\": 1234,\n        \"padding\": \"\"\n      }\n    },\n    {\n      \"id\": 12597,\n      \"key\": \"_new_order_email_sent\",\n      \"value\": \"true\"\n    }\n  ],\n  \"line_items\": [\n    {\n      \"id\": 19,\n      \"name\": \"Al Khayran Tour\",\n      \"product_id\": 116,\n      \"variation_id\": 0,\n      \"quantity\": 1,\n      \"tax_class\": \"\",\n      \"subtotal\": \"100.000\",\n      \"subtotal_tax\": \"0.000\",\n      \"total\": \"100.000\",\n      \"total_tax\": \"0.000\",\n      \"taxes\": [],\n      \"meta_data\": [\n        {\n          \"id\": 203,\n          \"key\": \"bookacti_bookings\",\n          \"value\": \"[{\\\"id\\\":154,\\\"type\\\":\\\"single\\\"}]\",\n          \"display_key\": \"Booking\",\n          \"display_value\": \"Booking ID:154Status:PendingEvent:Morning Tour - Friday, November 12th, 2021 8:00 AM → 11:00 AM x1\"\n        }\n      ],\n      \"sku\": \"CO-ADV-02\",\n      \"price\": 100,\n      \"parent_name\": null\n    },\n    {\n      \"id\": 20,\n      \"name\": \"Kayaking\",\n      \"product_id\": 726,\n      \"variation_id\": 735,\n      \"quantity\": 1,\n      \"tax_class\": \"\",\n      \"subtotal\": \"10.000\",\n      \"subtotal_tax\": \"0.000\",\n      \"total\": \"10.000\",\n      \"total_tax\": \"0.000\",\n      \"taxes\": [],\n      \"meta_data\": [\n        {\n          \"id\": 213,\n          \"key\": \"pa_kayak-type\",\n          \"value\": \"single-kayak\",\n          \"display_key\": \"Kayak Type\",\n          \"display_value\": \"Single\"\n        },\n        {\n          \"id\": 214,\n          \"key\": \"pa_duration\",\n          \"value\": \"1-hour\",\n          \"display_key\": \"Duration\",\n          \"display_value\": \"1 Hour\"\n        }\n      ],\n      \"sku\": \"CO-ACT-03-11\",\n      \"price\": 10,\n      \"parent_name\": \"Kayaking\"\n    }\n  ],\n  \"tax_lines\": [],\n  \"shipping_lines\": [],\n  \"fee_lines\": [],\n  \"coupon_lines\": [],\n  \"refunds\": [],\n  \"date_created_gmt\": \"2021-11-08T00:28:40\",\n  \"date_modified_gmt\": \"2021-11-08T00:28:40\",\n  \"date_completed_gmt\": null,\n  \"date_paid_gmt\": null,\n  \"currency_symbol\": \"OMR\",\n  \"_links\": {\n    \"self\": [\n      {\n      }\n    ],\n    \"collection\": [\n      {\n      }\n    ],\n    \"customer\": [\n      {\n      }\n    ]\n  }\n}\n  }\n]"
      },
      "name": "Function",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        490,
        220
      ],
      "notesInFlow": true,
      "notes": "Mockup data"
    },
    {
      "parameters": {
        "functionCode": "const lineItems = items[0].json.line_items\n\nconst id = items[0].json.id\n\nconst _lineItems = [];\n\nfor (const [index, lineItem] of lineItems.entries()) {\n  if (lineItem.meta_data[0].key === 'bookacti_bookings') {\n      _lineItems.push({\n        id: index + 1,\n        name: lineItem.name,\n        Item_notes: lineItem.meta_data[0].display_value\n      })\n  } else {\n    let note = '';\n    for (const meta of lineItem.meta_data) {\n       note+= `${meta.display_key} ${meta.display_value} `\n    }\n    _lineItems.push({\n        id: index + 1,\n        name: lineItem.name,\n        Item_notes: note\n    })\n  }\n}\n\nreturn [\n  {\n    json: {\n      id,\n      line_items: _lineItems\n    }\n  }\n]"
      },
      "name": "Function1",
      "type": "n8n-nodes-base.function",
      "typeVersion": 1,
      "position": [
        750,
        220
      ]
    }
  ],
  "connections": {
    "Start": {
      "main": [
        [
          {
            "node": "Function",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Function": {
      "main": [
        [
          {
            "node": "Function1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}
2 Likes

@RicardoE105 Thanks for your valuable inputs and help. I will go through it and let you know the outcome, thanks again…