Extracting line items from data using formulas

Topic Labels: Formulas
438 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

Hello, we sell flowers online and use Shopify, we ask the customer for some extra information with each order, the date they’d like the flowers delivered and their card message. When we get the Shopify data (Line Items Properties Value) through to Airtable it comes through in the following format [Day][Comma][Date][Month][Comma][Year][Pipe Key][Comma][Card Message] (e.g. Friday, 22 April, 2022|,Happy Birthday, Love you xxx) splitting THIS up is relatively easy although it took some working out, (Note: commas can’t be used reliably to split up the data as customers often use commas in their card messages) so to do this I have the Line Items Properties Value data in a regular text field and use the following formula:

Delivery Date:

LEFT({Line Items Properties Value}, FIND("|", {Line Items Properties Value}) -1)

Card Message:

RIGHT({Line Items Properties Value}, LEN({Line Items Properties Value}) - FIND("|", {Line Items Properties Value}) - 1)

This works well and without any problems, however where I get lost and would love some help is when customers order multiple products, Shopify still sends through the data as one big comma separated list - [Day][Comma][Date][Month][Comma][Year][Pipe Key][Comma][Card Message 1][Comma][Day][Comma][Date][Month][Comma][Year][Pipe Key][Comma][Card Message 2] (e.g. Sunday, 19 June, 2022|,Happy Birthday,Monday, 20 June, 2022|,Happy Anniversary). Not only that but customers can order up to 5 products at a time, meaning that initial format can repeat up to 5 times.

Any help or guidance is greatly appreciated.

0 Replies 0