Archive for the ‘Smartlist Builder’ Category

Smartlist Builder Example – Sub Query Restriction Prototype

Hi all

Recently, there was a discussion in the forum, where there was a request to provide a restriction on a custom smartlist, which does the same as the SQL query 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

Basically the requirement was to display all the distribution lines for vouchers which have a specific account (or) accounts in the distribution.

Ideally, for such scenarios, a SQL query would be the best option, since there is no way to specify a restriction such as a resultant in a subquery. 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.

I just decided to play around with Smartlist Builder, based on discussions with Mariano, and see if we there is a way to provide a similar functionality using Smartlist Builder.

What I did was basically as shown below. I created an INNER JOIN between the PM Distribution History File and the PM Paid Transaction History File with the Voucher Number and Document Type. Then I added 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

image image

Note: The restriction have 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.

Then I created a LEFT OUTER JOIN between the PM Transaction History File and the PM Distribution History File with the Voucher Number and the Document Type. And I displayed the distribution details (Account Number, Debit Amount, Credit Amount) from this distribution table. This provided the same results that we would have obtained using the SQL query provided above.

Then with some invaluable help and advice from my fellow MVP Mariano Gomez, I have added the options explained below, which helped me eradicate the duplicate issue which I was facing if the account was used more than once on the distribution. Thanks Mariano. You have always been a great guide for me and many others on the community!

Then I clicked on Options and selected the option Summary Smartlist and changed the Summary Type for the following fields as explained below.

  • PM Paid Transaction History File – Voucher ID – 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

image

You can download the import file for Smartlist Builder here.

Hope this helps the community…

Until next post!

November 1, 2011 · veeyeskay · 2 Comments
Tags: , , , , , , ,  · Posted in: Dynamics, Great Plains, Smartlist Builder Total Views: 1,570

Restrictions on multiple values in Smartlist Builder

Folks

Recently there was a request which came up in the news groups on the restrictions in smartlist. The requirement was to add a filter to the smartlist builder object on a range of values, and the important fact was that it was not a continuous range.

When I was believing there was no option available in smartlist builder to achieve the same and the best way to achieve the same was by creating a custom SQL view with the filter and using the view to create a smartlist builder object, Devon Southall posted a response which was very helpful in this scenario, and I wanted to share it with you all, since I am sure it would benefit all.

Under the restriction options, we have a couple of options like “Is Equal to One of List” and “Is Not Equal to One of List”, as shown below.

image

When one of these options are picked, the system allows us to add the various values which needs to be filtered in into a list box as shown below.

image

So we have an option to restrict the records matching these values (or) to restrict the records that do not match these values in the list. This was a very helpful note from Devon and it definitely was a wonderful tip on the Smartlist Builder restriction capabilities, and so I decided to post this article. Thanks Devon.

I am sure it would be useful for you folks as well. Until next post…

March 13, 2010 · veeyeskay · 2 Comments
Tags: , ,  · Posted in: Dynamics, Great Plains, Smartlist Builder Total Views: 2,024

Smartlist Builder Objects using SQL Tables and Views

Whenever we create a smartlist builder object using SQL tables and views, we need to be careful when exporting the same and importing it on to a test environment (or) the production environment.

There are a few things which you need to consider when u create a smartlist builder object using SQL server tables and views. When you export the smartlist builder object to an XML file, the database reference is exported as well, into a tag which looks like <NAME>’SQL Database Name'</NAME>. This will actually store the database which was referenced to create the smartlist object. So when this Smartlist object is imported into any other machine which contains a different database name, it would give permission errors, when the imported smartlist object, even though you have granted the necessary SQL permissions to the users. To avoid this, there are 2 options which you can follow.

1. If you are importing this to a single company installation, you can edit the above tag in the XML file and change the value to the appropriate database name of your environment.

2. If you are importing this into a multi-company installation, you can import the XML file and open the object in Smartlist Builder. Then go to Options and check the option called Multi-Company Smartlists and select the companies you need to include in the smartlist. This will fetch the data from all these companies into the smartlist. And at the SQL security for each user, grant the access to the appropriate company/companies to the users and they would be able to see the data from the appropriate company when they open smartlist. :-)

Till the next post, bye for now…

December 23, 2008 · veeyeskay · One Comment
Posted in: Dynamics, Great Plains, Smartlist Builder Total Views: 3,492