This post explains how to create custom smartlists with subquery restrictions using Smartlist Builder, without using SQL queries (i.e.) using Dynamics GP Tables and Joins inside Smartlist Builder
This post explains how to create custom smartlists with subquery restrictions using Smartlist Builder, without using SQL queries (i.e.) using Dynamics GP Tables and Joins inside Smartlist Builder
Account Number |
Description |
Debit |
Credit |
000-2100-00 |
Accounts Payable |
$0.00 |
$1500.00 |
000-6010-00 |
Advertising Expenses |
$1400.00 |
$0.00 |
000-6630-00 |
Suspense Expenses |
$100.00 |
$0.00 |
The request is to extract the specific voucher and all its distribution lines if the miscellaneous expenses account was used. This is ideally to analyze the complete distribution in the voucher so that the need for the suspense account can be analyzed.
Ideally, for such scenarios, a SQL query would be the best option. The SQL query typically looks as what is shown below.
SELECT * FROM dbo.PM30600
WHERE VCHRNMBR IN ( SELECT VCHRNMBR
FROM dbo.PM30600
WHERE DSTINDX IN ( 167, 256 ) )
AND dbo.PM30600.DOCTYPE = 1
ORDER BY dbo.PM30600.VCHRNMBR
We can make use of the “Is Equal To One of List” option and provide a subset of vouchers which need to be returned, but it is not the efficient method of achieving the same, since the filter in the SLB definition needs to be changed every time we run the query to get the appropriate results, which does not make sense. This article explores more into how this functionality can be achieved using Smartlist Builder.
First create an Equals JOIN (there is not Inner Join option in SLB, use Equals) between the PM Distribution History File and the PM Paid Transaction History File with the Voucher Number and Document Type. Then add a restriction on the Distribution Account to be one of the following accounts as seen in the screen shot below.
Note: The restriction has to be applied on the distribution table which has an inner join with the transaction history table and NOT on the one which has the left outer join with the transaction history table.
Now create a LEFT OUTER JOIN between the PM Transaction History File and the PM Distribution History File with the Voucher Number and the Document Type. Display the distribution details (Account Number, Debit Amount and Credit Amount) from this distribution table. Click on Options and select the option Summary Smartlist and change the Summary Type for the following fields as explained below.
This provides the same results that we would have obtained using the SQL query provided above.