Skip to main content

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.

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.

Reply


Terms & Conditions