MVP Frank Hamelly on Account Rollups and Organizing SmartLists

After a long writing hiatus, MVP Frank Hamelly on his gp2themax blog explains how to setup the often overlooked Accounts Rollup feature which he describes as “not as tasty as fruit rollups, but equally satisfying”.

Frank also explains how to use Microsoft Dynamics GP’s Navigation Pane to organize SmartLists for the end-users. His narrative is based on a requirement posed by the client at an actual project we both had the opportunity to work and collaborate on.

Please be sure to read Frank’s articles as you will find a lot of interesting things from his personal experience.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to create a smartlist to show invoices without a physical PDF document

Not too long ago, I answered a pretty interesting question on the Dynamics GP newsgroup. The user’s company images signed delivery tickets and store these as PDF documents on a shared location on their network. The PDF document is named after the corresponding invoice number in Dynamics GP, for example, if the invoice number is INV010001, the corresponding image of the delivery ticket would be stored as INV010001.PDF

The user wanted to know if it was possible to create a smartlist to show invoices without a scanned PDF image of the delivery ticket.

Solution

When you think of this problem from the eyes of a user, it seems almost impossible to write a SmartList that would produce the results wanted by the user. But when you breakdown the problem, the real issue is, how do we create a SQL Server view that is able to show whether a file exists for a specific Dynamics GP invoice document.

The solution is to create a SQL Server user-defined function (UDF) that is able to return whether a file exists or not. Then we can incorporate this UDF into our view query. Since views can be addressed from SmartList Builder then we are good to go.

First the UDF.

dbo.IsFileExist

-- Created by Mariano Gomez, MVP

-- Check if the UDF exists and dropIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].   [IsFileExist]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))

   DROP FUNCTION dbo.IsFileExistGO

-- Create the UDFCREATE FUNCTION dbo.IsFileExist(@Path VARCHAR(200), @FileName VARCHAR(200))RETURNS INTAS  BEGIN    DECLARE @FullPath VARCHAR(200);    DECLARE @FileExists INT;

    -- Check for the back-slash at the end of the path string    IF RIGHT(RTRIM(@Path), 1)  '\'      SET @Path = RTRIM(@Path) + '\';

    -- concat the path with the file name    SET @FullPath = RTRIM(@Path) + RTRIM(@FileName);

    -- run the xp_fileexist system stored proc to retrieve wheter the file exists or not    EXEC master.dbo.xp_fileexist @FullPath, @FileExists OUT;    RETURN @FileExists;  END;GO

GRANT EXECUTE ON dbo.IsFileExist TO DYNGRPGO

The above UDF uses the undocumented system stored procedure xp_fileexist which, when executed in its native form, will display information about the file passed in as a parameter. For example:

exec master..xp_fileexist 'c:\boot.ini

… produces the following results:

File Exists File is a Directory Parent Directory Exists----------- ------------------- -----------------------1           0                   1

(1 row(s) affected)

Now that we have the UDF function in place, we can prepare the view to be used with SmartList Builder.

dbo.vShowSOPAttachments

-- Created by Mariano Gomez, MVPCREATE VIEW dbo.vShowSOPAttachments AS  WITH SOP_CTE (SOPNUMBE, SOPPDF) AS (    SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP10100    UNION ALL    SELECT SOPNUMBE, RTRIM(SOPNUMBE) + '.PDF' FROM SOP30200  )  SELECT SOPNUMBE, SOPPDF, dbo.IsFileExist('C:\', SOPPDF) AS [File Exists] FROM SOP_CTE;GO

GRANT SELECT ON dbo.vShowSOPAttachments TO DYNGRP

Note the call to the dbo.IsFileExists UDF. With the view in place you can follow standard SmartList Builder procedures to implement and deploy the view. For information managing security and changes to SmartList Builder smartlist click here.

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Changing SQL Server views for SmartList Builder smartlists

One of the valuable tools that Microsoft has released is SmartList Builder for Dynamics GP. The concept is very simple: if the standard out-of-the-box Smartlist does not cut it for you, well, you build your own! Furthermore, SmartList Builder does allow for some nifty stuff, such as working with Microsoft SQL Server views and (custom) tables. Custom tables are not the focus of this article.

If you can’t get your smartlist done with all the SmartList Builder query building capabilities, then create your own views of the data in Microsoft SQL Server, grant access to them in SQL Server, then, grant security to them in SLB, and use them.

But what if after deploying your smartlists, users request more columns of information that you did not include in your original SQL Server views, and now you need to make those changes and affect your previous work of art?

Solution

The solution may be easier than you think, but please do not delete your existing SmartList Builder and recreate all that work again! It’s not necessary! These simple steps will illustrate how to change your SQL Server views and make these changes affect your existing SmartList Builder objects.

1) First, create your SQL Server view (If you already have a view skip to step 5), i.e:

/* Mariano Gomez, MVP   Sample code to illustrate changes in SQL Server view-based smartlist*/IF OBJECT_ID('dbo.querySomething') IS NOT NULL   DROP VIEW dbo.querySomethingGO

CREATE VIEW dbo.querySomething ASSELECT JRNENTRY, TRXDATE, ACTINDX, DEBITAMT from GL20000GO

GRANT SELECT ON dbo.querySomething TO DYNGRPGO

NOTE: This is a trivial example to illustrate the solution. Views should and must be used when you have exhausted all possibilities in SmartList Builder.

2) Grant security to the new object in SmartList Builder in GP. Go to Microsoft Dynamics GP > Tools > SmartList Builder > Security > SQL Table Security

3) Create a smartlist with the new view, marked all columns in the Default list

4) Build the smartlist by going to Microsoft Dynamics GP > SmartList. Click on Yes to build SmartList.

5) Now, go back to SQL Server Management Studio and add a new column to the view, as follows:

/* Mariano Gomez, MVP   Sample code to illustrate changes in SQL Server view-based smartlist*/ALTER view [dbo].[sampleGLforSLB] ASSELECT JRNENTRY, TRXDATE, ACTINDX, DEBITAMT, CRDTAMNT from GL20000GO

NOTE: In this case I added the CRDTAMNT (credit amount) column.

6) Go back to SmartList Builder, open the smartlist and click on the Edit Selected Table button to open the Add SQL Table window.

All that’s needed is to click on the Save button, nothing else! Surprisingly, this is not documented anywhere in the manuals. Now the CRDTAMNT column shows up in the fields list on the right pane. Then click on the Default check mark to add to the default list of columns to be displayed, or manually add the column in your smartlist after rebuilding with the changes.

7) Now, click on the Save button in the SmartList Builder window.

Open SmartList to build the modified smartlist with the new changes.

Hope these simple steps help you with modifying your views and getting your SmartList Builders to work smoothly with the changes.

Until next post!

MG.-
Mariano Gomez, MIS, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Developing for Dynamics GP Weekly Summary

After a long absence, my friend David Musgrave is slowly regaining his writing pulse. Please check out some of his cool articles this week, but first and foremost let him know what you think about them. I always encourage everyone to test and play with some of the code we provide in these articles. They are a good way to get acquainted with Microsoft Dynamics GP functionality. So let’s get on with the articles:

1. Microsoft Dynamics GP 10.0 Service Packs, Hotfixes and Payroll Compliance/Year End Updates May Damage the Modified Reports and Forms. After a few of these errors circulating on the newsgroups, it has been confirmed by Microsoft development team. The payroll year end update will in fact damage your modified forms and reports. I have a theory on this one: since the YE update is also inclusive of service pack 3, it will be necessary to take all the precautions layed out when applying a service pack, that is, export ALL your customizations to a package file BEFORE applying any patches. Read more here.

2. Microsoft Dynamics™ GP 10.0 Bootcamp (Australia). Now, if you ever wanted to go to a Dynamics GP training class, won’t it be a dream to do so in the land down under? Now, that’s what I call a Dynamics GP training with style. Read more here.

3. eOne.Dynamics.GP.ExcelBuilder.Engine.dll Exception. If you have received this error after applying Microsoft Dynamics GP 10 Service Pack 2 and above and you are currently using SmartList Builder and Excel Builder, make sure you take a look at this article and the fix by reading more here.

4. Hybrid – Adding Named Printers control to Reports using VBA. Wondering how to get Named Printers to work with unsupported reports? David answers this one with a cool VBA customization. Best of all, you can download sample code! To play with this code, click here.

5. WorldMaps Tracking Added. As if my uber friend wasn’t all that uber geek already, he adds a cool hits tracker on his blog with the cool WorldMaps beta product from Structure Too Big. I have to confess, I will be adding this one pretty soon to my blog! I find it fascinating to know where your readers are concentrated, which helps when developing content for the blog. For more info, click here.

6. Modifier – Reading and Writing Data with ADO Example. It does not matter how many of these we do, I always find Modifier with VBA fascinating and a very good alternative to Dexterity customizations when possible. Now, don’t get me wrong. I love Dexterity, but I also love the portability offered by VBA customizations too. To read more, click here.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Victoria Yudin on Adding Extender Data to SmartList

Wow! So you have this fancy customization in Extender, but now you are stucked with figuring out a way to get the data out to SmartList. Sweat it no more! Fellow MVP, Victoria Yudin has a great article on SmartList and Extender. She also took the guessing work out of the picture by adding tons of screenshots and step by step instructions with a detailed example.

One note: these same techniques can be applied to reports, so you solve this one and you also have the reports going for you.

Please visit Victoria’s blog and let her know what you think about her post.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to create a Go To link to the Apply Sales Document window in SmartList Builder

A few days aback, my buddy Jim Harris at TurboChef was having an issue with creating a Go To in SLB that would link open credits and return documents displayed in SmartList to the Apply Sales Documents window in GP where he could effectively apply documents that had not been applied to invoices, etc.

I thought, how complicated could that be? You open SLB, you setup your SmartList fields then, setup your restrictions for RM Document Type-All equal or greater than 7, and add the Go To by matching the Customer Number field on the window to the Customer Number in the RM Open Table, the Document Type on the window with the RM Document Type-All field in the table, and the Document Number in the window withe the Document Number in the table, a few clicks to close and save, build and… not so quick!

At first the Apply Sales Document window filled in the Customer Number and Document Number, but screamed at the Document Type field. I figured, my restriction was wrong, so I went back in and check on the restrictions, reviewed the SQL check on my Go To fields to make sure I was matching the values and running the field scripts, a few clicks to close and save, build and… not so quick!

I got the same result as before and was starting to wonder now if I had had a long day and was not thinking straight. So I went to the kitchen, got some coffee and went back to work on the problem.

After a few hit-and-miss attempts the light bulb went off! Drop-down lists are enumerations of the items listed in them! In the case of the Apply Sales Document window, 1 – Credit Memo, 2 – Returns, 3 – Payments, which do not correspond to the actual document types in RM (7 — Credit Memo, 8 — Returns and 9 — Payments).

This meant that I would need to create a calculated field that would pickup the RM Document Type-All value from the RM Open table and translate it into the corresponding enumaration in the Drop-Down list in the Apply Sales Document window. The resulting calculation looked something like this:

case {RM Open File:RM Document Type-All} when 7 then 1 when 8 then 2 when 9 then 3 end

That’s correct! It looks and behaves just like the CASE…END statement in SQL Server!

This now meant that in the Go To window, I would have to reference my calculated field instead of the RM Document Type-All field when matching the Document Type value.

From this point on a few clicks to close and save, build and… Voila!

Until next post!

MG.-
Mariano Gomez, MIS, MVP, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Bug: SmartLists Builder not Formatting SQL Correctly in Microsoft Dynamics GP 10

It appears that SLB cannot format the SQL query required to extract data after adding a few restrictions. Specifically, any query requiring more than one restriction will ommit the AND operator. This has been documented under Problem report 47625: Restrictions don’t work if have more than one on a SmartList which is currently scheduled to be resolved in v10 SP3.

“If you want it fixed earlier in a hotfix, I would suggest that you log a support case and reference the bug number. The more customers who request the fix, the higher the priority will be“, said David Musgrave, Microsoft Escallation Engineer in a Microsoft Dynamics GP community post.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/