0

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.

Imran
  • 93
  • 2
  • 15

2 Answers2

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.

Krypton
  • 4,394
  • 2
  • 9
  • 13
1

Why not just use the Date Closed? (closedate)

dcrs
  • 294
  • 1
  • 4
  • Date Closed is sometimes mistaken as the date of payment application which changes the status of an invoice from Open to Paid in Full. Date Closed is the most recent date of payment made regardless of when it was applied. A. Date Closed = 12/14/2016 not 01/30/2017 01/29/2017 - Invoice $20.00 12/14/2016 - Advance Payment $20.00 01/30/2017 - Application of Advance Payment B. Date Closed = 01/30/2014 01/29/2017 Invoice = $40.00 01/30/2017 - Payment = $20.00 12/31/2016 - Advance Payment =$20.00 02/14/2017 - Application of Advance Payment – Imran Jul 30 '18 at 21:25