Min() Order Date of a Product Group

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

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

4. 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,'|')&'|'
   

5. 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.

IF(
	AND(
		{ItemCode},
		{Date}
		),
	{ItemCode}&
	  '|'&
	  DATETIME_FORMAT(
		{Date},
		'YYMMDD'
		)
	)

DATETIME_PARSE(
    MID(
		{Order History},
		FIND(
			'|'&{ItemCode}&'|',
			{OrderHistory}
			)+5,
		6
		),
	'YYMMDD'
	)

(LEN(
	'|'&{OrderHistory}&'|'
	)-LEN(
		SUBSTITUTE(
			'|'&{OrderHistory}&'|',
			'|'&{ItemCode}&'|',
			''
			)
		)
	)/5

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.