- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 04:41 AM
Hello everyone, first time in this forum but I’ve been using Airtable for a while.
I have an ecommerce and when there is a new order I get this information in my “Purchased items” (cant change this).
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Sans gravure
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure
I would like to extract some info for ecah product, for example the count, productSlug, productName, and put it in other cells.
Is this possible?
Thank you very much!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 06:01 AM
Before I feel comfortable advising you, I’ll need a little bit more information.
How do you get this information - is it presently arriving in a table named “Purchased items” as one big text field?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 06:21 AM
Hello Bill,
No, this ir arriving (thanks to zapier) in a fild called Order details.
What I want to do is basically separate the different products and knowing how much of each the customer bought.
I also can get this information in this format (simpler I think):
rollin-102-bleu (rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable) x 1 = 1 € ~~ rollin-102-bleu (rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure) x 1 = 1 € ~~ Taxes: 0 € ~~ TOTAL: 2 €
Here there is 1 products
- rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable x1
- rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure x1
Thank you!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 08:57 AM
Yep - I understand. You need a string parser that understands the data format being sent into that field. To make an outcome like this -
Quantity
MID({Order details}, FIND("count: ", {Order details}) + 7, FIND("productName:", {Order details}) - (FIND("count: ", {Order details}) + 7))
Product Name
MID({Order details}, FIND("productName: ", {Order details}) + 13, FIND("productSlug:", {Order details}) - (FIND("productName: ", {Order details}) + 13))
Product Slug
MID({Order details}, FIND("productSlug: ", {Order details}) + 13, FIND("rowTotal:", {Order details}) - (FIND("productSlug: ", {Order details}) + 13))
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 09:06 AM
OMG this is amazing! Thank you so much!
Is it possible to break in 2/3/4 or whatever this hole thing before applying the formulas you gave me?
I get all this as a whole
"count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Sans gravure
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure"
Is is possible to break and put it in a different cell everytime there is a new word “count”?
This is already amazing thank you!
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 09:08 AM
I have no idea what this means. Clarify?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 09:11 AM
Sure,
each time the word “count” appears, it means that i have a new unique product.
my cell can contain several time this word, if the client buys mire than one unique product.
In this case, they bought 2 unique products one time each:
PRODUCT 1
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable
PRODUCT 2
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Sans gravure
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure
But if they buy 3 unique products, i can have something like this (always in the same cell):
PRODUCT 1
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable
PRODUCT 2
count: 1
productName: 102
productSlug: rollin-102-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Sans gravure
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-sans-gravure
PRODUCT 3
count: 1
productName: 340
productSlug: rollin-340-bleu
rowTotal: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantImage:
variantName: 102 Gravure personnalisée (+20€): Avec gravure or véritable
variantPrice: {u’unit’: u’EUR’, u’value’: 120, u’string’: u’1\xa0\u20ac’}
variantSlug: rollin-102-bleu-gravure-personnalisee-20eu-avec-gravure-or-veritable
For me to use your formulas I have to divide this in 3 different cells, am I right?
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 09:26 AM
Yes, either cells or separate records.
Ideally, each product purchased should be a separate transaction record with an identifier (such as invoice number) to allow you to group-by for a given order.
In any case, the data flowing in from your transaction system is doing a poor job of arranging the data for consistent parsing and management.
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 09:30 AM
Can I separate this with a formula?
I know…
- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Nov 21, 2019 09:35 AM
Possibly, but this would be very difficult and complex if at all possible.
If it were me faced with this little nightmare, I would not use formulas - I would create an integration layer that handles all parsing to create clean data in Airtable that also conforms to your data management requirements.