I´m running a transaction search for items in SO, and I need to add a column to obtain the approval date for the SO (in detail view)
I tried:
CASE WHEN {systemnotes.field} = 'Estado del documento' and {systemnotes.newvalue} = 'Ejecución de la orden pendiente' THEN {systemnotes.date} ELSE NULL END
(field name is in Spanish and is "document status" and the new value is "pending fulfillment")
But there are cases with some sales Orders that get changed back to "pending approval" and then back to "pending fulfillment" so this statement returns multiple dates.
I cannot use the solution to summarize this field and get the maximum (as I have read in some forums), I need to get this at the detail level and get only one result per SO with the latest approval date.
Can I get the maximum directly in the formula?
somenthing like:
MAX (CASE WHEN {systemnotes.field} = 'Estado del documento' and {systemnotes.newvalue} = 'Ejecución de la orden pendiente' THEN {systemnotes.date} ELSE NULL END)
:P