2 Replies Latest reply on Dec 20, 2019 11:35 AM by Ravi Thota

# Saved search formula help

Hi,

I hope someone here can help me with a saved search formula

I am creating a saved search which shows bill and the date the bill was paid along with the GL account codes. i can't use Main line is true as I need the GL account codes.

There are scenarios where one bill has been partially applied to a credit note and the remaining is a bill payment, in my report I only want to see the line where a bill has been applied to bill payment and I don’t want to see an additional line where the same bill has been applied to the credit note.

But if the bill is fully applied to the credit note then I want to see that line of data.

Is there anyway I can add a formula in the criteria of the search, if yes then how do i do that?

 Name Inv Num Amount in (USD) Pay date Type Amount Paid Comments XYZ 6826 4,403.66 07/11/2019 Bill Credit 2688 I don't want to see this line XYZ 6826 4,403.66 21/10/2019 Bill Payment 1,715.66 I want to see this line ABC 1234 3,000.00 29/11/2019 Bill Credit 3,000.00 I want to see this line

Thanks

Yusuf

• ###### 1. Re: Saved search formula help

You can compare the amounts and types on the line in the criteria. I typically use a Formula(Numeric) with a case statement

CASE WHEN {amountpaid} = {amount} AND {type} = 'Bill Credit' THEN 1

WHEN {amountpaid} < {amount} AND {type} = 'Bill Payment' THEN 1 END

You will want to set the criteria for this formula to '1'

If you want to include the Main Line as well, you'll have to use Expressions so that one set of criteria is for the lines, and the other set of criteria is to include the main line.

• ###### 2. Re: Saved search formula help

Hi Yusuf,

Please try the saved search below.

Regards,

Ravi