saved search to pull date invoice was paid in full

Question

I've a transaction saved search in which I've various formula columns displaying invoice date, any related credits, and then actual payments after any term discounts taken. Now I need to add another column to display the date invoice was marked "Paid in Full" I'm using the formula which is not working: case when {systemnotes.newvalue} = 'paid in full' then {systemnotes.date} end I can't use 'date closed' because that's just displays the most recent payment date against an invoice and not the date it was fully applied for example an old credit memo. Any input is appreciated.

Answer 1

Oracle string comparisons are case sensitive. {systemnotes.newvalue} returns 'Paid In Full' - not 'paid in full' (note the Title Case). You can correct the comparison to use Title Case like this:

case when {systemnotes.newvalue} = 'Paid In Full' then {systemnotes.date} end

or you can coerce both sides to upper or lower case for a slightly more robust comparison:

case when UPPER({systemnotes.newvalue}) = UPPER('paid in full') then {systemnotes.date} end

I've tested both of these and they work for me.