1 Reply Latest reply on Dec 2, 2019 5:57 PM by SuiteSean

    Saved search formula help

    Yusuf - Reading

      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
          SuiteSean

          Hi Yusuf - Reading,

           

          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.