Identifying retained business

[Alan_Edwards]

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.

[Alan_Edwards]

I found a solution:

• gather all my fiscal years from each order in a rollup in Accounts, and use a lookup to pull those all back into the Orders tab
• use another rollup in Accounts to get just the earliest FY from the orders using a MIN function, and use a lookup in Orders tab to get that value as well
• use formula to compare the order fiscal year against the earliest fiscal year. If they’re equal, that order was “New Business”
• use formula to search the array of all fiscal years for the current fiscal year - 1. If it returns a non-zero value it’s “Retained Business” from last year
• If an order isn’t “New Business” and isn’t “Retained”, then by process of elimination it’s “Recaptured” business from a previous year.