Create a Saved search which Displays Vendor Transactions Posted to Certain Account Types per Vendor

Business Use Case:

1. Vendor Purchases posted to Expense, Other Expense, Other Asset and Fixed Asset types of accounts are recorded in a Vendor Bill, Vendor Credit and Journal Entries.

2. To summarize the search results in 2 columns  namely:  vendor name  and total transaction amounts in absolute amount.

3. On the saved search results , the transactions posted to Expense and Other Expense account types show negative sign and the ones posted to Other Asset and Fixed Asset account types show positive sign so totalling them will give incorrect results.

4. Since the criteria is based on certain Account types, we need to get the Line items of the transactions Bills and Bill Credits. The line items do not have the Vendor name since Vendor name is located on the main line (header) of the transactions which is posted to the Accounts Payable type of account.

5. To show the Vendor Name on the Bills and Bill Credits we need to use the field Vendor fields : Name and the vendor name on the Journal Entries shows up on the Name field.

Solution:

Steps to create the Search:

1. Navigate to Lists > Search > Saved Searches > New

2. Click Transaction

3.  Under Criteria tab > Standard subtab > add the following fields:

--Type =  is any of Bill, Bill Credit, Journal
--Account Type= is any of Expense, Fixed Asset, Other Asset, Other Expense
--Click on the Use Expressions box:
--Add:  (
Add : field "Formula (Text)"  Vendor: Name or {vendor.entityid} = is not empty set And/Or column to OR
Summary Type = Group
--Add : field > Formula (Text) {name} = is not empty set And/Or to And

4. On the Results tab > Columns subtab > add the following fields:

--Date
--Type
--Vendor: Name
--Name
--Number
--Amount
--Formula (Text) =  case when {name} is null then {vendor.entityid} else {name} end
          Sumamry Type = Group
--Formula (Currency) = is ABS({amount})
          Summary Type = Sum

**Only those fields with summary type will be the Columns of the search.

5. Name the search
6. Click on Save and Run