Hi, I am trying to show the date that bill was put On-hold (PAYMENT HOLD) The following is my formula on Formula (Text) but I get ERROR: Invalid Expression CASE WHEN {paymenthold} = 'T' THEN {systemnotes.date} ELSE 0 END I used Yes instead of T, but the same error. Also I can't see Payment Hold in system notes, although Answer Id: 41595 says System Notes logs when the Payment Hold checkbox is marked or unmarked. Any ideas? Even if I replace {systemnotes.date} with 'NY' I get the same error.

Your formula (date) expects to return a date; you cannot return 0. You need to change the

ELSE

or remove it.

@Sandii Thanks for this. Do you mean to remove "0"?

Well

0

is not text, so the formula should bomb any time the ELSE is triggered.

Thanks. I'll try a text

Removing the ELSE or returning NULL instead of some generic text might work

I'm not sure if Else can be removed from a case statement?

It definitely can @Merk

@Sandii @Tristan Day I managed to get this to work. But I'm not sure if it pulls through the date of the last change of the bill, or the date of the Payment On Hold. I had to make some changes to the formula. I need to change something on the bill tomorrow and run the saved search and check if date changes on Results Summary Type = Maximum CASE WHEN {paymenthold} = 'T' THEN (TO_CHAR({systemnotes.date}, 'DD/MM/YYYY')) END