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
[Customers] table, create a linked-record field called {Orders} that links to an [Orders] table.
- Each record in
[Orders] should contain a {Date} field and an {Item} field (obviously :winking_face: ).
- Also create in
[Orders] 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 [Customers] 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.
