July, 2012

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

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.

(cross-posting from http://blogs.technet.com/b/lystavlen/archive/2012/07/12/office-365-partner-getting-started-guide.aspx)

If you are a Microsoft Partner, the following guide from Jesper Osgaard is an excellent introduction to establishing a practice around Office 365.

Office 365 is having a tremendous momentum. As a Microsoft Partner with customers looking at Office 365 this is the guide for you. It includes links to important e-learning, documentation, kits and other material intended to help you get started with your Office 365 practice.

You can access the guide here.

(Post Courtesy Sivakumar Venkataraman)

Sometimes, when we book invoices in payables, we might need to distribute the expenses into multiple GL accounts based on business requirements. In most of the situations, a specific purchases account gets defaulted from the vendor (or) from the company posting account setup and the users manually add additional expense lines in the distribution window at the time of transaction entry and apportion the expense to the various expense GL accounts.

This can be quickened, especially if we are able to identify the most common expense heads which get posted into at the time of the payables transaction. Once the accounts are defined, you can enter the various purchases accounts at the Vendor Account Maintenance window, by clicking on the button near the purchases account description as shown below.

When the button is clicked, the Additional Vendor Accounts window is opened where we can enter the additional purchases accounts needed. You can also define these additional purchases accounts at the vendor class level as well. We can also specify which accounts should default on the transaction distribution entry window as well. These additional accounts which have been marked to default will automatically display on the distribution entry window as shown below.

The amounts default to 0 for these lines. By default the entire expense amount gets defaulted under one specific purchases account. The user can apportion the amounts to these accounts if necessary (or) leave them as 0 and continue posting the invoice without apportioning. If we leave the additional purchases account as 0, the system ignores those lines during the posting process and only picks the accounts which have an amount associated to it.

This is really a time saving feature for many of the clients who may be doing this process manually day in and day out.