I’m trying to build in the ability to automatically flag order data in my Airtable CRM as one of three categories:
“new” - first time ever order for this customer
“retained” - the same customer placed an order last fiscal year too
“recaptured” - same customer placed an order at some point in the past, but not in the previous year.
I felt like I was pretty close to solving this when I used an uniquearray formula to create a field that combines all fiscal years in which an account has orders into one array, so that I can compare each individual order fiscal year to that array. However, since there’s no apparent way to intentionally sort that array from, say, earliest to latest or least to greatest, I have no way to reliably compare a given order’s fiscal year to the array.
I feel like I’m doing a bad job explaining this, but maybe someone will know what I’m talking about.