Creating Smartlists with Sub Query Restriction using Smartlist Builder

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.

Code Example

 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.

• 000-6630-00
• 300-6530-00

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.

• PM Paid Transaction History File – Voucher Number  – Group By
• PM Paid Transaction History File – Vendor ID – Group By
• PM Paid Transaction History File – Document Type – Group By
• PM Paid Transaction History File – Document Date – Group By 
• PM Paid Transaction History File – Document Number – Group By
• PM Transaction Distribution History (3rd table shown above) – Voucher Number – Group By
• PM Transaction Distribution History (3rd table  shown above) – Distribution Sequence Number – Group By
• PM Transaction Distribution History (3rd table  shown above) – Distribution Account Index – Group By
• PM Transaction Distribution History (3rd table shown above) – Distribution Type – Group By
• PM Paid Transaction History File – Document Amount – Average
• PM Transaction Distribution History (3rd table  shown above) – Debit Amount – Average
• PM Transaction Distribution History (3rd table shown above) – Originating Debit Amount – Average
• PM Transaction Distribution History (3rd table shown above) – Credit Amount – Average
• PM Transaction Distribution History (3rd table shown above) – Originating Credit Amount – Average

This provides the same results that we would have obtained using the SQL query provided above.