Friday, July 31, 2009

Getting Started with VST: WinForms and Controls - Part 1

If you have been around Microsoft Dexterity long enough, by now you are already aware that Dex is not an object oriented development environment, and rather supports a concept called object-based development. In the traditional sense, Dexterity does not allow you to define classes and derive objects from those classes. However, someone figured out that a Dexterity form can act as a container or class, with the Scripts and Functions tabs allowing you to define the contructors, destructors and methods for the class. In addition, adding fields to a window and setting the proper values for those fields allows developers to set the state of an object. Given the nature of the form and how the scripts on that form are called, and the values for fields on windows are set, forms can simulate classes with public and private methods in traditional object oriented programming terms.

Thank goodness, you don't need to do all this in Visual Studio! VST allows you to define a WinForm, or in simple terms, a Windows Form. Forms in VST cannot be grouped as you would normally do in Dexterity, so in that sense, they behave like typical Dexterity window, but they still provide all the functionality, including the look and feel due to the inherited properties created with the Dexterity Bridge assembly.

Customer Hobbies WinForm

Our example today, will show how to add the equivalent of a Dexterity form trigger, known in VST as a menu handler event. Typically, form triggers add an entry to the Additional menu on a Dexterity form. Today, we will add a Customer Hobbies form and will then add a menu handler event to the Customer Maintenance form to call our form.

1) Open Visual Studio. Go to File > New > Project to create a new Visual Studio project.

2) Find the Dynamics GP in the Project Types pane, then choose Microsoft Dynamics GP Add-In from the installed templates pane.

3) Enter a name for the project. We will be naming our project GPWinForm.

4) Click OK to continue

5) Make sure your GPAddIn.cs code looks like this:

GPAddIn.cs

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Dexterity.Bridge;
using Microsoft.Dexterity.Applications;
using Microsoft.Dexterity.Applications.DynamicsDictionary;

namespace GPWinForm
{
public class GPAddIn : IDexterityAddIn
{
// IDexterityAddIn interface

public void Initialize()
{

}
}
}

6) We will first define our WinForm. In Solution Explorer, right-click on the project name and navigate to Add > New Item as shown in the figure below.



7) In the Add New Item window we will highlight Microsoft Dynamics GP Form and name our form rmCustomerHobbies.cs. Click on Add to continue.



8) Here comes VST into play! When the form is defined, you will notice a set of providers at the bottom of the design window, as shown below.



These providers allow WinForms and certain controls to inherit properties unique to Dexterity windows and controls. In the example, our WinForm is given a Control area and a Status area by default. To change the window title, locate the Text property in the Properties window and edit to Customer Hobbies.

9) Now, let's add some controls -- you will need to resize your window accordingly. We will add a few prompts (known as labels in Visual Studio) and a few strings (known as text boxes in Visual Studio). First our text boxes:

a) Click on the TextBox control in the Toolbox. We will add the CustomerNumber field. Set the properties for the field as follow:


b) Now, let's add the CustomerName field. We can follow the same steps used in (a). At the end your window should look like this:


In traditional Dexterity style, these two fields will inherit the values displayed in the Customer Maintenance window when a record is selected and the Customer Hobbies window is selected.

10) Now we can add the labels that will identify our fields when the window is opened. We will add the Customer ID and Customer Name labels, but most importantly, show how you can link fields to their prompts as well, just like in Dexterity.

a) Click on the Label control in the Toolbox. Now place the label in from of the CustomerNumber field. Size the label accordingly and align accordingly. Set the label properties as follow:



NOTE: Unlike Dexterity, label controls are referenciable via code, hence the Name property of the control.

b) Repeat step (a) for the Customer Name label and set the propeties as well.


11) Now let's proceed to link the fields to their prompts. One of the properties unique to VST label controls is the LinkField on dexLabelProvider. This property will display a drop-down list that will allow you to select the corresponding window field for the label, as shown below:


Once a label is linked to a textbox, you will see the traditional underline that accompanies all Dexterity prompts.



12) Following the same guidelines, we will add 4 more text boxes, rmCustomerSports, rmCustomerSportsTeam, rmCustomerLeisureActivity, and rmCustomerRestaurant, and the corresponding labels to indicate the customer's hobbies. Note that the Enabled property for these new controls must be set to True to allow the controls to receive data. We will also use an empty label from the Toolbox to create the separator between the window's static section and the data entry section, a la Dexterity. Your final window should look like this -- don't worry if you did not get the same results, I have uploaded the solution for your benefit.


Part 2 of the "WinForms and Controls" series will discuss adding the typical buttons to the screen, like the Ok button, the window help button and the window notes buttons. In addition to the code needed to produce the menu handler event.

Downloads

GPWinForm Solution - click here.

Until next post!

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

Updates to this article
08/07/2009 - Fixed broken link to solution download.

Wednesday, July 29, 2009

New Article on MSDynamicsWorld: "Implementation Advocates"

" By definition... I could not take either the customer's side or the partner's side in my assessment"

My new article on MSDynamicsWorld is up! In this article, I discuss a specific case that happened a few months aback in which I had a chance not to play the devil's advocate, but rather the devils' advocate -- If you are familiar with the English language grammar, you probably know the distinction between devil's and devils'. Please read the article as it contains important information about my professional experiences as a consultant working with Microsoft Dynamics GP customers and partners and gives an insight into what's happening in the implementation world.

Until next post!

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

Tuesday, July 28, 2009

Microsoft Dynamics GP in Russian - Microsoft Dynamics Г.П. на русском языке

For the past few months I took it up on myself to produce a translated Russian version of Dynamics GP. What began as fun little project in response to seeing a lot of GP information in Russian on Partnersource, is now showing some remarkable results. I have managed to translate 45% of the string resources and adjust the screens accordingly aided by numerous accounting software books I ordered from Moscow -- and my wife who is from Moscow herself :-) -- to get the proper terms and meaning in place.

Here is a sample of some of the screens (more to come),

Dynamics GP Login Window



Account Maintenance Window



Customer Maintenance Window



Unlike Chinese or Korean, the Cyrilic alphabet is not a double-byte alphabet and can easily be managed by Dexterity. However, this project was met with its own set of challenges when I started the translation process, for example, how to get Microsoft Windows to display Cyrilic characters to begin with. I sorted this out by installing Dynamics GP on a server with a default Russian locale configured. The reason this is important, as I discovered, is because ANSI characters have different values depending on the Windows locale. This way, as strings were translated in my Notepad text file, Dexterity would be able to read and display them in their native format (as long as they were not double byte characters).

For more information on the translation process I used, please take a look at Dave Musgrave's Translating Dexterity Applications Series of articles.

I will provide more information about the progress of this fun project and, hey!, may even consider partnering opportunities. After all, as you all know Dynamics AX is the only Microsoft product being distributed in Eastern Europe.

Until next post!

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

Monday, July 27, 2009

Dynamics MVP recognised for contribution to Support Debugging Tool!

The Microsoft MVP Award Program Blog has a post up on my efforts and contributions to build 11 of Support Debugging Tool. The article highlights my input to the email engine now available in SDT. To read the full article, click here.

Until next post!

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

Friday, July 24, 2009

Getting started with VST: "Hello World!" - The Video

I wanted to provide a video update of the "Hello World!" project as the feedback and reception of the series has been tremendous. I am currently working on a second article to demonstrate data access and storage and the use of WinForms and will have it out before next Wednesday.

In the mean time, enjoy parts 1, 2, and 3 of the "Hello World!" project!

video

video

video

Videos are best viewed in Internet Explorer at 200% of the current size.

Until next post!

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

Wednesday, July 22, 2009

1st Online Conference for Great Plains Arabia

Fellow Microsoft Dynamics GP MVP Monzer Osama is organizing the 1st Online Conference for Great Plains Arabia community - http://www.GP4Arab.com/forum. Monzer is the founder and moderator of this leading GP community in the Middle East of more than 2,000 partners and users. Monzer elaborates, "I will talk about how to connect Microsoft Excel to Microsoft SQL Server externally, and using Pivot tables and Pivot Charts as a reporting and analysis tools , then I will explain how that data will [refresh] automatically."

Sessions will be conducted in a workshop format, making the agenda very hands-on. If you are interested in joining the event, here is the session information:


Event link: http://gp4arab.eventbrite.com/
Date: Wednesday, July 29, 2009
Time: 6:00 PM - 7:00 PM (Saudi Arabia Time zone GMT+0300)



Monzer will attempt to present this session in English at a later date.

Until next post!

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

Tuesday, July 21, 2009

Getting started with VST: "Hello World!" project

Adventures of a Microsoft Dexterity Developer


Okay, so we did not get this far to bring up a "Hello World" message, but I figured that's a pretty standard thing to do in the software development world to introduce a tool or language to software developers, so I am not going to pass upon the chance!

Creating our first Visual Studio Tools project... with a twist!

For this project, we want to be able to register two events that will allow us to display the messages "Getting ready to say Hello World!" and "Hello World" messages after login is successful and before and after the Toolbar form is loaded. Usually, this method of trigger registration is used by Dexterity developers to run code after log in, so I figured I would replicate something we are most familiar with.

Topics being covered

  • Creating a VST project
  • Events registration
  • Running your code before and after login
  • Building and Deploying VST project
Let's get started!

1) Open Visual Studio. Go to File > New > Project to create a new Visual Studio project.

2) Find the Dynamics GP in the Project Types pane, then choose Microsoft Dynamics GP Add-In from the installed templates pane.

3) Enter a name for the project. We will be naming our project GPHelloWorld.

4) Click OK to continue.



NOTE: As indicated, I will be using C# as my development language, however, the steps to initiate the project for VB.NET should be the same.

5) Upon clicking OK, Visual Studio will proceed to initialize our project for us. The first thing you will see is the Initialize() method in the GPAddIn class. The GPAddIn is an implementation of the IDexterityAddIn interface. Sufficient to say, the Initialize() method is equivalent to the global Startup script in a Dexterity integrating application, serving as entry point to the add-in project.

GPAddIn.cs

using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.Dexterity.Bridge;
using Microsoft.Dexterity.Applications;

namespace GPHelloWorld
{
public class GPAddIn : IDexterityAddIn
{
// IDexterityAddIn interface
public void Initialize()
{

}
}
}

NOTE the Solution Explorer window will show the references to the primary assembly responsible for exposing Dynamics GP resources: the Microsoft.Dexterity.Bridge assembly. As well, you can find all picture resources that will make our VST controls look awefully similar to the standard controls available to integrating Dexterity applications.



6) Now, we will proceed to register the event on the Toolbar form. We will display a message "Getting ready to say Hello World!" before the toolbar form is loaded and one that says "Hello World!" after the toolbar is loaded. You can then decide what event works best for your individual needs.

GPAddIn.cs


using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using Microsoft.Dexterity.Bridge;
using Microsoft.Dexterity.Applications;
using Microsoft.Dexterity.Applications.DynamicsDictionary;


namespace GPHelloWorld
{
public class GPAddIn : IDexterityAddIn
{
// IDexterityAddIn interface
public void Initialize()
{
ToolbarForm toolbar;
toolbar = Dynamics.Forms.Toolbar;

// add registration before the form open event
toolbar.OpenBeforeOriginal += new System.ComponentModel.CancelEventHandler(toolbar_OpenBeforeOriginal);

// add registration after the form open event
toolbar.OpenAfterOriginal += new EventHandler(toolbar_OpenAfterOriginal);
}

void toolbar_OpenBeforeOriginal(object sender, System.ComponentModel.CancelEventArgs e)
{
MessageBox.Show("Getting ready to say Hello World!");
}

void toolbar_OpenAfterOriginal(object sender, EventArgs e)
{
MessageBox.Show("Hello World!");

}
}
}

A couple things to highlight in the code:

a) Like references in Dexterity, VST allows you to create variables that point to specific dictionary resources. The definitions for the various dictionary resources are found in an additional namespace available in the application assembly, Microsoft.Dexterity.Applications.DynamicsDictionary.


using Microsoft.Dexterity.Applications.DynamicsDictionary;


Once the namespace is added, you can then proceed to declare a variable that will represent the Toolbar form.


public void Initialize()
{
ToolbarForm toolbar;
toolbar = Dynamics.Forms.Toolbar;
.
.
}


b) An event registration is the Visual Studio equivalent of a Dexterity trigger. As such, they must be included in the Initialize() method, added automatically when the project was created. The Initialize() method is the equivalent of the Startup() script in a Dexterity integrating application. To register the events that will be executed before and after the Toolbar form is loaded, the following code was added,


// add registration before the form open event
toolbar.OpenBeforeOriginal += new System.ComponentModel.CancelEventHandler(toolbar_OpenBeforeOriginal);

// add registration after the form open event
toolbar.OpenAfterOriginal += new EventHandler(toolbar_OpenAfterOriginal);

If you noticed when you typed the += character sequence, you were prompted to press the TAB key twice! Intellisense automatically completed the code and added the overload methods for each event handler. Now, that's a time saver! Imagine if we had this type of capability when adding the trigger registration scripts in Dexterity!

c) The rest was just cosmetics.. I added the MessageBox.Show() function to display our two messages in each overload method. The MessageBox is part of the System.Windows.Forms namespace which must be added to the using section of the application.


void toolbar_OpenBeforeOriginal(object sender, System.ComponentModel.CancelEventArgs e)

{
MessageBox.Show("Getting ready to say Hello World!");
}

void toolbar_OpenAfterOriginal(object sender, EventArgs e)
{
MessageBox.Show("Hello World!");
}

Simple enough!

7) Building the solution is done via the Build menu, but before, we can set our final assembly name by right-clicking on the GPHelloWorld project in the Solution Explorer window and selecting the Properties option.



8) Build the solution. You should be able to find your new assembly in the Bin\Debug folder of the solution directory, typically under C:\Users\yourUser\Documents\Visual Studio 2008\Projects\GPHelloWorld\GPHelloWorld\bin\Debug.

Copy the resulting project assembly to the AddIns folder under your Microsoft Dynamics GP installation directory.

Testing the solution

Open Microsoft Dynamics GP to see the results. Before the Toolbar form is loaded, we will receive the first message:



Once the Toolbar form is loaded, our next message will be displayed, as follows:


I guess this is not bad for a first try, is it?

While integrating solutions tend to be a lot more complex than our Hello World application, the principles will be the same. For our next installment, I will be contructing a WinForm and showing common ways to store and retrieve data in a VST solution.

Downloads

HelloWorld Solution C# - Click here
HelloWorld Solution VB.NET - Click here

Until next post!

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

Dynamics GP v11 VPC image soon to be released

"A beta VPC image for GP11.0 will likely be available in the winter of 2009/2010"

In reading carefull through the latest blog article posted by the Microsoft Dynamics GP Product Management & Marketing team, I found, burried at the end, likely timeframes for the availability of "v11" beta and General Availability demo images. Ben Courin, author of the article, says:

"In the August/September timeframe, we will be releasing a differencing disk with the Microsoft Dynamics GP 10.0 SP4. No additional improvements, just the SP install. We will then take a few weeks off from building images, and then turn our focus to the Microsoft Dynamics GP 11.0 VPC/Technical Demonstration toolkit. This project will be built from scratch/from the ground up. It will NOT be an upgrade of the current Microsoft Dynamics GP 10.0 series of vpc’s. A beta VPC image for GP11.0 will likely be available in the winter of 2009/2010 and then a GA image will likely be available at Convergence 2010."

While the article does not specify the RTM timeframe, one can guess that it will probably be on or before Convergence 2010 in Atlanta, GA. For now, partners can download the new and fully loaded Dynamics GP v10 SP3 VPC image.

Until next post!

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

Monday, July 20, 2009

Getting started with Visual Studio Tools for Microsoft Dynamics GP -- Adventures of a Microsoft Dexterity developer

For the longest time I had been trapped in my own Microsoft Dexterity development shell and could not see why anyone would want to integrate to Dynamics GP with anything that is not Dexterity -- I am not referring to simple customizations here! After all, Dexterity is (and will continue being for years to come!) the native development environment of Dynamics GP. So, the next obvious question in my mind was, why couldn't .NET developers learn Dexterity? After all, we were here first! But, then I decided to spin the question around, why couldn't Dexterity developers learn .NET? Now, that's a challenge I like!!

In the next 2 weeks I will publish a set of articles showing Dexterity developers that it is possible to make a relatively smooth transition to .NET development with Visual Studio Tools for Microsoft Dynamics GP -- by far, the longest name for any Microsoft product, so we will shorten that to VST.

The similarities

What you will find as a Microsoft Dexterity developer transitioning to VST is that you can do a lot of the things you are able to do when building integrating applications: an integrating application can access the forms, windows, window fields, tables, and table fields in each dictionary. They can also access global variables, commands, procedures, and functions defined in each dictionary. So from a Dexterity developer's perspective you will be able to write the equivalent of triggers -- known as event registrations in VST -- on any resource in GP's dictionary or a third party dictionary. You will be able invoke existing global procedures and functions, form procedures, etc.

In addition, VST provides the ability to develop WinForms. WinForms included in an integrating application can use capabilities provided by VST to match the appearance of the core application widows. The appearance of the window as well as the various controls in the window can be customized, just like windows created in a Dexterity application. In fact, you can also set the famous Control Area for which Dexterity windows are widely known. Finally, with the latest release of VST, you can now access Dynamics GP tables without using any data access technology, like ADO.NET for example.

As I move through the articles, I will reference the equivalent Dexterity terminology where needed to ensure you keep track of the similarities.

The Differences

From a pure architecture perspective, Dexterity resources reside in a dictionary. With VST your compiled code will reside in an assembly. An assembly is a fundamental building block of any .NET Framework application. From a coding perspective, Visual Basic.NET (VB.NET) and C# (pronounced C-sharp) will serve as replacements to Sanscript.

In summary, assembly is to dictionary as Visual Studio is to Dexterity as C# or VB.NET are to Sanscript.

What you will need

The following are the ingredients for this series of articles:

1) A valid copy of Visual Studio 2008
2) An installed copy of Microsoft Dynamics GP v10 with the Fabrikam company
3) Microsoft .NET Framework 2.0 or grater.
4) Intermediate to advance knowledge of Microsoft Dexterity
5) Click here to download Visual Studio Tools SDK SP3 for Microsoft Dynamics GP 10.0. The SDK contains the components to build integrations. Once downloaded proceed to install.

What to expect from these articles

A big disclaimer goes out to all of you Dexterity developers who will hopefully follow these articles:

1) This is not a proclamation of independence from Dexterity, rather a way to show you how to achieve some of the same integration capabilities with VST. Dexterity is a good skill to have and in today's world will keep you rightfully and gainfully employed. After all, we are a specie in way to extinction and like Bigfoot (if you believe in such thing), very hard to find. But like all species in way to extinction, if you don't reinvent yourself or learn other skills you probably won't make it.

2) These articles will not show you how to code in C# or VB.NET, rather the integration mechanisms available with VST and how they are similar to what you currently do in Dexterity. As with any new programming language, you must have fairly good programming skills. The rest comes with a lot of reading, trying, frustrating yourself, and trying again, ah... and a good search engine -- similar to how you learned Dexterity in the first place.

3) All the code for these articles will be written in C# like any world class commercially available application -- no offense to VB.NET developers intended here -- however, I will make a big effort to insert VB code snippets along with the C# code.

Please stay tuned for the series... the first article goes out tomorrow!

Series Links

Jul 21, 2009. Getting Started with VST: "Hello World!" project - Click here
Jul 24, 2009. Getting Started with VST: "Hello World!" - The video - Click here
Jul 31, 2009. Getting Started with VST: WinForms and Controls - Part 1 - Click here
Aug 07, 2009. Getting Started with VST: WinForms and Controls - Part 2 - Click here

Until next post!

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

Thursday, July 16, 2009

"End of Month + Net Days" payment terms due date calculation

Folks across the pond use payment terms and due dates that are not traditionally close the ones we are familiar with on this side of the hemisphere. "End of Month + Net Days" (EOM+ND) is a typical case.

In EOM+ND payment terms, an invoice becomes due a number of net days after the last day of the month for the invoice date. For example, if an invoice date is July 16 and we are on a payment term of EOM plus 45 net days, the invoice will not be due until September 14 -- or 45 days from July 31.

Of course, you cannot manage this type of payment term request in GP, not out-of-the-box anyways, which most of the time will require a customization to deal with the issue.

In this article, I will examine setting up two SQL Server triggers: one on the PM Transaction Open File (dbo.PM20000) and another on the RM Open File (dbo.RM20101) tables. The triggers will use the Net Days field in the Payment Terms Master (dbo.SY03300) table to calculate the net days after the end of month to assign the invoice due date.

So here are the triggers:

trigger pmEOMPlusNet

-- Created by Mariano Gomez, MVP
-- No warranties expressed or implied
CREATE TRIGGER pmEOMPlusNet ON dbo.PM20000 AFTER INSERT
AS
BEGIN TRANSACTION;

BEGIN TRY
UPDATE A SET A.DUEDATE =
DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))
FROM PM20000 A
INNER JOIN INSERTED I ON (A.VCHRNMBR = I.VCHRNMBR) AND (A.DOCTYPE = I.DOCTYPE)
LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)
WHERE (I.DOCTYPE = 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
, ERROR_SEVERITY() AS ErrorSeverity
, ERROR_STATE() AS ErrorState
, ERROR_PROCEDURE() AS ErrorProcedure
, ERROR_LINE() AS ErrorLine
, ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO


trigger rmEOMPlusNet

-- Created by Mariano Gomez, MVP
-- No warranties expressed or implied
CREATE TRIGGER rmEOMPlusNet ON dbo.RM20101 AFTER INSERT
AS
BEGIN TRANSACTION;

BEGIN TRY
UPDATE A SET A.DUEDATE =
DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))
FROM RM20101 A
INNER JOIN INSERTED I ON (A.CUSTNMBR = I.CUSTNMBR) AND (A.DOCNUMBR = I.DOCNUMBR)
AND (A.RMDTYPAL = I.RMDTYPAL)
LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)
WHERE (I.RMDTYPAL= 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')
END TRY
BEGIN CATCH
SELECT ERROR_NUMBER() AS ErrorNumber
, ERROR_SEVERITY() AS ErrorSeverity
, ERROR_STATE() AS ErrorState
, ERROR_PROCEDURE() AS ErrorProcedure
, ERROR_LINE() AS ErrorLine
, ERROR_MESSAGE() AS ErrorMessage;

IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
COMMIT TRANSACTION;
GO


Setting up the payment term in Dynamics GP

Open the Payment Terms Setup window (MSDGP > Tools > Setup > Company > Payment Terms) and configure the payment term as shown below:


NOTE: You can still setup discount and discount types for the payment term, but these will be calculated based on the document date. If you need these to apply based on EOM as well, you will need to change the above triggers to reflect the discount calculation based on EOM as well.

Finally, once setup, these payment terms can be used from SOP and POP. Just keep in mind that the due dates will not be calculated while the transactions are stored in a batch, but rather when posted.

Until next post!

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

Wednesday, July 8, 2009

SQL Server Management Studio Standard Reports

If you have worked with SQL Server Management Studio (SSMS) in either Microsoft SQL Server 2005 or Microsoft SQL Server 2008, you may have inadvertly overlooked one of its key features: Standard Reports.

Standard Reports are Reporting Services (SSRS) reports that can provide all sort of statuses and and information about the database engine and its management components and well as the databases themselves in real time -- the reports are refreshable! When executed, the reports are embedded in tabs within a new SSMS tab. As a consultant, I find these reports particularly useful when attempting to establish the health of a Microsoft Dynamics GP SQL Server installation. I can immediately relay critical SQL Server performance information to my clients and suggest preventative or corrective actions to mitigate the issues, saving them money in the process.

So lets take a look at the available reports by node...

NodeReport
ServerServer Dashboard
ServerConfiguration Changes History
ServerSchema Changes History
ServerScheduler Health
ServerMemory Consumption
ServerActivity – All Blocking Transactions
ServerActivity – All Cursors
ServerActivity – Top Cursors
ServerActivity – All Sessions
ServerActivity – Top Sessions
ServerActivity – Dormant Sessions
ServerActivity – Top Connections
ServerTop Transactions by Age
ServerTop Transactions by Blocked Transactions Count
ServerTop Transactions by Locks Count
ServerPerformance – Batch Execution Statistics
ServerPerformance – Object Execution Statistics
ServerPerformance – Top Queries by Average CPU Time
ServerPerformance – Top Queries by Average IO
ServerPerformance – Top Queries by Total CPU Time
ServerPerformance – Top Queries by Total IO
ServerServer Broker Statistics
ServerTransaction Log Shipping Status
DatabaseDisk Usage
DatabaseDisk Usage by Top Tables
DatabaseDisk Usage by Table
DatabaseDisk Usage by Partition
DatabaseBackup and Restore Events
DatabaseAll Transactions
DatabaseAll Blocking Transactions
DatabaseTop Transactions by Age
DatabaseTop Transactions by Blocked Transactions Count
DatabaseTop Transactions by Locks Count
DatabaseResource Locking Statistics by Object
DatabaseObject Execution Statistics
DatabaseDatabase Consistency History
DatabaseIndex Usage Statistics
DatabaseIndex Physical Statistics
DatabaseSchema Changes History
DatabaseUser Statistics
DatabaseActive Full-Text Catalogs
LoginsLogin Statistics
LoginsLogin Failures
LoginsResource Locking Statistics by Logins
ManagementTasks
ManagementNumber of Errors
NotificationServices General
SQL Server AgentJob Steps Execution History
SQL Server AgentTop Jobs


To access a specific report, just right-click on the desired node then choose Reports > Standard Reports, select the desired report. The following is an example of the navigation to the Server node reports.



Lets take a look at some sample standard reports...

Server Dashboard report (server > Reports > Standard Reports > Server Dashboard)


This report provides detailed configuration information including, but not limited to the SQL Server startup time, product version and edition, server collation, the number of processors in used by the SQL Server instance, CPU usage by database, and number of active databases.

Disk Usage by Top Tables report (database > Reports > Standard Reports > Disk Usage by Top Tables)



This reports shows vital table information such as the number of records in the table, the amount of disk space reserved for the table, spaced occupied by data and indexes, and the unused space. This information can be used to plan for disk space optimization and establish whether it will be necessary to increment the number of partitions or relocate the databases.

Backup and Restore Events report



This one is got to be one of the most important database level reports as it shows statistics about the backups completed on a specific database: average time, size of backups, whether the database backup was complete or differential, etc.

I hope you like this SQL Server gem and start to explore these reports. There is valuable information that can be used to administer your Dynamics GP and overall SQL Server environment.

Until next post!

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

Tuesday, July 7, 2009

Microsoft Dynamics GP Database Maintenance Utility

How many times have you performed an upgrade just to realize with the first posting that some stored procedure is missing or did not -- for some misterious reason -- get created, yet the upgrade completed with no errors? This is especially true when upgrading from several versions aback, when the upgrade path is not too clear, or you have forgotten to apply that critical service pack that would avoided some of the missing objects issues you are now experiencing.

I recently came across a user who wanted to recreate all SmartList Builder objects because they suspected something was wrong with the installation, however, they did not want to have to use the backend to recreate all the stored procs used by SLB, because of the "fair of missing something or endup damaging something else"... you know what they say... the client is always right! (not always, but that's the topic of another article).

After scratching my head for a few moments, the answer came in the form of the new Microsoft Dynamics GP Database Maintenance Utility, but first some history...

Before there was the SQL Maintenance utility, back in the old days, the former Great Plains Software provided reams of text files containing scripts to recreate any missing or damaged system and/or company database object. These scripts could be found on the installation CDs and could be accessed via the old SQL Server Query Analyzer tool. All you needed to do was to open one of script files, do a search for the desired stored procedure, copy and paste into Query Analyzer, and execute the script against the appropriate database. This sounded simple enough until system administrators (we will not call them users for fear of retaliation :-) ) began corrupting the script files and introducing their own code... It was funny, because Great Plains Support used to asked as part of their support script if you had modified X or Y stored procedure for the problem you were calling for. When Microsoft took over, came the MSIs and the script files were no longer provided. Now what? There comes SQL Maintenance, however, the set of utilities provided with SQL Maintenance did not allow you to recreate stored procedures, functions, or table triggers. These still had to be obtained from Microsoft. I guess, in the end they figured out it wasn't worth the hazzle and delivered Database Maintenance Utility.

So, lets take a look.

To recreate database objects, you will need to:

1) Launch Database Maintenance Utility. Go to Start > Programs > Microsoft Dynamics > GP 10.0 > Database Maintenance.


Select your server and select an authentication method. Fortunately, Database Maintenance can use Windows Trusted Authentication. Click Next to continue.

2) Choose the company you are going to perform the updates on. My personal preference is to choose ALL databases if you suspect a damage on a stored procedure, function or trigger. Why? Chances are, the stored procedure may be damaged across the board.


Click Next to continue.

3) Select the product for which you will like to rebuild its objects. For this example, I will use SmartList Builder.


Click Next to continue.

4) Mark the objects you would like to recreate. You have the choice of Stored Procedures (includes UDFs too), table Triggers, and Views.


Note that for Tables and table Auto Stored procedures you must use the SQL Maintenance option available within Microsoft Dynamics GP. Click Next to continue.

5) Confirm your selection.


Click on Next to continue.

6) The Utility will run through a progress bar and will return a status of the operations when completed.




If recreating stored procedures, the Database Maintenance Utility will re-grant permissions to all database users in the DYNGRP role, so no need to run the GRANT.SQL utility script.

Hopefully, you use this powerful tool as a first option in recreating any missing or damaged database object, before you call Microsoft or open that support case.

Until next post!

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

How to calculate the number of days a vendor check takes to clear?

I am always amazed at the things you can do with Microsoft Dynamics GP. Just recently, a user pointed out that the Days for Checks to Clear field on the Vendor Credit Summary window was always blank and that they needed this field to update automatically.



According to the help file, the field is defined as "View or change the average number of days that a check you issue to the vendor usually takes to clear your bank.", in other words, you can manually update it if you need to track this data.

The user also pointed out that they performed daily bank reconciles and that the Cleared Date column in the CM Transaction (dbo.CM20200) table was reflective of the actual check cleared date in the bank and that they would like to use this field to update the Days for Checks to Clear field in the Vendor Credit Summary window. After all, the date value of the Cleared Date column only gets updated when the check is marked as cleared during a checkbook reconciliation process and does not necessarily reflect the actual date the check cleared the bank, but with daily bank reconciles this should not be an issue, because the would be one of the same.

So, if you perform daily bank reconciliations and would like to know the average days a vendor check takes to clear your bank, you could use the following T-SQL query to update this column value:


-- Created by Mariano Gomez, MVP
WITH AVGCTE(VENDORID, AVGDAYS) AS (
SELECT A.CMLinkID, AVG(DATEDIFF(DD, A.TRXDATE, A.clearedate)) AS AVGDAYS
FROM CM20200 A
GROUP BY A.CMLinkID
)
UPDATE PM00201 SET DYCHTCLR = AVGDAYS
FROM PM00201 INNER JOIN AVGCTE ON (PM00201.VENDORID = AVGCTE.VENDORID)

Now what to do with the script? You can schedule it as part of a SQL Server job that will run daily to update this value for you! This will allow you to have accurate average days for each vendor and in turn will allow you to make better purchasing decisions to preserve your hard earned cash.

Until next post!

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

Wednesday, July 1, 2009

One more year as Dynamics GP MVP

A big "Thank You!" to the folks at Microsoft's Most Valuable Professional (MVP) Award Program and to Melissa Travers, my MVP lead for 1 more year of this prestigious award...that's right, 1 more year! It's truly an honor to serve (and continue serving) the Dynamics GP community.

For those of you who have asked about the program, the Microsoft MVP Award Program recognizes and thanks outstanding members of technical communities for their community participation and willingness to help others. The MVP Award is given to exceptional technical community leaders who foster the free and objective exchange of knowledge by actively sharing their real-world expertise with technology users. The MVP Award celebrates the most active community members from around the world who provide invaluable online and offline expertise that enriches the community experience and makes a difference in technical communities that feature Microsoft products

MVPs are not Microsoft employees, nor do we receive any compensation for helping others.

Click here for a list of current Dynamics GP MVPs.

Until next post!

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