is it possible to use an aggregate function in workflow to drive the value of a field? I'm trying to set a status of a customer based on their most recent transaction date. i'm trying something like this (not 100% sure it's right)

CASE WHEN {transaction.type} = 'Sales Order' THEN ROUND({today} - (MAX({transaction.trandate})KEEP(DENSE_RANK LAST ORDER BY {transaction.trandate})) END

. I was trying this in search to see if i could get it to work first before moving to workflow. i know it can be scripted, but i was starting with workflow for maintainability in organization. this isn't complete yet either, essentially if the most recent transaction is within 30 days, then set field value to "super active" else if 31-60 then set to "active" else 61-90 then "passive"... etc. or am i better off just scripting this? thanks!

just learned this one, but try the

Date of Last Order

or

Date of Last Sale

filter

is that ultimately available in workflow formula? i'm avoiding filters and the "when ordered by" in searches because i can't do that in a workflow formula.

more as a condition for your workflow

i forgot about the date of last order which seems great, but i can't reference that field via the formula section for workflow set field value action. so i guess it's either scripted or 5 different searches to set 1 value (since we have 5 values). i feel like a script is better for this in that case.