Tuesday, April 16, 2019

Create a Search Showing a List of Bills or Journals with the Corresponding Credits or Payments Applied Net of Discount Availed

Here are the steps in creating the Saved Search:

-Navigate to Reports >Saved Searches >All Saved Searches >New

-Select Search Type = Transaction

-Enter the desired title of the search under the Search Title field

-Navigate to Criteria tab>Standard subtab>Filter column and add the following filters:

a. Type = is any of Bill Payment, Bill Credit, Journal

b. Account Type = is Accounts Payable

c. Formula (Numeric) | Formula: case when {line} = 2 then 1 else 0 end | Formula (Numeric): not equal to |Value: 1

-Navigate to Results tab>Columns subtab>Field column and add the following fields:

a. Applied To Transaction Fields…>Date

b. Applied To Transaction Fields…>Type

c. . Applied To Transaction Fields…> Document Number

d. . Applied To Transaction Fields…>Amount |Custom Label: Billed Amount

e. Date |Custom Label: Date Paid/Credited

f. Type

g. Document Number

h. Name

i. Formula (Currency) | Formula: case when nvl({amountpaid},0) < nvl({appliedtotransaction.amountpaid},0) then nvl({amountpaid},0) else nvl({appliedtotransaction.amountpaid},0) end |Custom Label: Amount Paid/Credited Net of Discount Availed

-Navigate to Results tab:

a. Sort By: Applied To Transaction: Date

b. Then By: Applied To Transaction: Document Number

-Navigate to Available Filters tab>Filter column and add the Field: Applied to Transaction Fields…>Date then mark the box under the Show in Footer column next to field added.

-Click Save & Run button

Note: if you want to filter a specific date range relating to bills, you can specify the period covered under the From and To fields that will appear in the bottom portion of the Search Results.

No comments:

Post a Comment