Well, one possibility would be to use linked records to track orders, roll up each customer’s consolidated orders, and extract date values from the rolled-up orders. Or, to rephrase that in a manner that makes actual sense
- In your
eCustomers] table, create a linked-record field called {Orders} that links to an eOrders] table.
- Each record in
eOrders] should contain a {Date} field and an {Item} field (obviously :winking_face: ).
- Also create in
eOrders] a formula field called something like {EncodedOrder}. It should be a fixed-length field combining, say, a 3-digit item code with a 6-digit date code and a couple of separator characters. Assuming an intermediate {ItemCode} field that handles mapping items to item codes, the formula for {EncodedOrder} might be
IF(
AND(
{ItemCode},
{Date}
),
{ItemCode}&
'|'&
DATETIME_FORMAT(
{Date},
'YYMMDD'
)
)
Define in eCustomers] a rollup field called {OrderHistory}. Use the following as an aggregation formula (that is, an Airtable formula pasted into the field where an aggregation function would normally go):
'|'&ARRAYJOIN(values,'|')&'|'
From {OrderHistory}, you can extract a lot of information. For instance, to get the date of the initial purchase of any item, use this formula:
DATETIME_PARSE(
MID(
{Order History},
FIND(
'|'&{ItemCode}&'|',
{OrderHistory}
)+5,
6
),
'YYMMDD'
)
To find out how many times the customer ordered a special item, try
(LEN(
'|'&{OrderHistory}&'|'
)-LEN(
SUBSTITUTE(
'|'&{OrderHistory}&'|',
'|'&{ItemCode}&'|',
''
)
)
)/5
Caveats: Assumes Orders are placed in sequence.
Well, one possibility would be to use linked records to track orders, roll up each customer’s consolidated orders, and extract date values from the rolled-up orders. Or, to rephrase that in a manner that makes actual sense
- In your
eCustomers] table, create a linked-record field called {Orders} that links to an eOrders] table.
- Each record in
eOrders] should contain a {Date} field and an {Item} field (obviously :winking_face: ).
- Also create in
eOrders] a formula field called something like {EncodedOrder}. It should be a fixed-length field combining, say, a 3-digit item code with a 6-digit date code and a couple of separator characters. Assuming an intermediate {ItemCode} field that handles mapping items to item codes, the formula for {EncodedOrder} might be
IF(
AND(
{ItemCode},
{Date}
),
{ItemCode}&
'|'&
DATETIME_FORMAT(
{Date},
'YYMMDD'
)
)
Define in eCustomers] a rollup field called {OrderHistory}. Use the following as an aggregation formula (that is, an Airtable formula pasted into the field where an aggregation function would normally go):
'|'&ARRAYJOIN(values,'|')&'|'
From {OrderHistory}, you can extract a lot of information. For instance, to get the date of the initial purchase of any item, use this formula:
DATETIME_PARSE(
MID(
{Order History},
FIND(
'|'&{ItemCode}&'|',
{OrderHistory}
)+5,
6
),
'YYMMDD'
)
To find out how many times the customer ordered a special item, try
(LEN(
'|'&{OrderHistory}&'|'
)-LEN(
SUBSTITUTE(
'|'&{OrderHistory}&'|',
'|'&{ItemCode}&'|',
''
)
)
)/5
Caveats: Assumes Orders are placed in sequence.
Thank you, @W_Vann_Hall.
I’m going to best testing this today and I’ll let you know who it turns out!
@W_Vann_Hall
I see what you were going for here, but this is not getting me to the final step I want. I want to be able to see each items and its first order date on a separate line, so I can calculate against the first order date of that item.
