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
(Post Courtesy Sivakumar Venkataraman)
Smartlist Builder is a great tool to create custom smartlists. We can create smartlists using Dynamics GP tables (or) using SQL objects. However, there have been many requests from customers and partners to retrieve the data from smartlist using a sub query filter. An example of such a request is to pull out all payables transactions with the complete distributions, where specific expense accounts are used. Assuming a voucher has the following distributions.
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
SELECT * FROM dbo.PM30600
WHERE VCHRNMBR IN ( SELECT VCHRNMBR
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.