Automating the start and stop of Microsoft Dynamics GP logs with the Support Debugging Tool - Part 2

Yesterday, we tackled the first part of this Support Debugging Tool project, showing how to implement the logging trigger portion. To recap, the application logging files (DEXSQL.LOG and Dexterity Script Log) will begin to record when the Hold checkbox on the Vendor Maintenance window is marked. However, registering the logging trigger will only occur when the Vendor Maintenance form is opened, and unregister, when the Vendor Maintenance form is closed. This will provide an effective set of actions to be recorded as opposed to logging every single action that occurs within the application.

Now that we have implemented the logging portion, we need the two non-logging triggers: the trigger that will start the logging trigger, and the one that will stop it.

The Start Trigger

We will start by setting up the non-logging trigger that will activate the VEND_HOLD_CHG logging trigger (which in turn will begin logging only when the Hold checkbox is marked). We will want to attach this trigger to the Vendor Maintenance form PRE script. We want the logging trigger to register once the Microsoft Dynamics GP code has run, hence we will startup the logging trigger after the original event. Unlike the logging trigger, this trigger start automatically upon the user launching and login into Microsoft Dynamics GP.

ST_VENDHOLD_LOG non-logging trigger - Resource tab

We will move to the Script tab and use a Helper function to assist with adding the code that will start up the logging trigger. By clicking on the Helper button, the Insert Helper Function window displays, from where we can choose the Start an Automatic Mode Trigger ID option in the drop-down list.

Insert Helper Function window

Then we can move to select the logging trigger ID, VEND_HOLD_CHG created previously.

Again, the Support Debugging Tool does most of the sanScript coding needed for this purpose by calling the built-in API procedure MBS_Trigger_Start to start up our VEND_HOLD_CHG trigger. Very convenient!

ST_VENDHOLD_LOG non-logging trigger - Script tab

Next we need to setup the non-logging trigger to disable the logging trigger.

The Stop Trigger

For this case, and instead of trying to replicate all these setups, we can take advantage of the Duplicate button of the Automatic Debugger Mode setup window to create a copy of the start up trigger.

Duplicate Button

Finally, we adjust the non-logging trigger to fire after the Vendor Maintenance form post script has executed, and change its script to call the MBS_Trigger_Stop Support Debugging Tool API function to un-register the VEND_HOLD_CHG trigger, effectively stopping all logging.

EN_VENDHOLD_LOG non-logging trigger

Now is time to turn on our non-logging triggers (which are set to start up automatically on login in into the Microsoft Dynamics GP application).

Turning on non-logging automatic start only triggers

A look the Automatic Debugger Mode status window shows our non-logging triggers waiting for the Vendor Maintenance window to open.

Automatic Debugger Mode Status window

If we open the Vendor Maintenance window we will see our trigger VEND_HOLD_CHG trigger is automatically registered (along with a secondary trigger that captures the original value of the field prior to being selected so it can be restored if needed). The Support Debugging Tool main window will change the color and prompt of the Automatic Debugger Mode button. When the Hold checkbox is marked, the logging trigger activates the logs and displays the desktop alert.

And all of this occurred without the end-user's intervention. In addition, the DEXSQL.LOG and Script Log files will only contain all events that occurred while the window was opened and records were manipulated. Once the Vendor Maintenance window was closed, the logs were suspended.

I have attached the configuration file to be imported with the Support Debugging Tool's Configuration Export/Import window. Once you import the XML file just activate the non-logging triggers as indicated above and you should be good to go.

Vendor Hold triggers - VEND_HOLD.zip

Discover the power of the Support Debugging Tool.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Automating the start and stop of Microsoft Dynamics GP logs with the Support Debugging Tool - Part 1

If you haven't noticed, it is - unofficially, of course - Support Debugging Tool week. With the fairly recent release of build 15 and a revision of that build, the tool has just gotten better with heaps of new features and improvements over existing ones! And I am exploring some really cool stuff that can be done with the product. Yesterday I showed how you can use the Automatic Debugger Mode's non-logging trigger capabilities to Default "Include Totals and Deposits" in the Sales Transfer Entry window

Today, I will touch on the topic of Automatic Debugger Mode and non-logging triggers again. However, this time I will show how you can use non-logging triggers in combination with logging triggers to capture vital application logs upon the occurrence of some Microsoft Dynamics GP application event without user intervention.

This may sound a little esoteric, but the concept is simple. For this example we will use the Vendor Maintenance window, a very familiar window to most of you who interact with Microsoft Dynamics GP on a daily basis.

Vendor Maintenance

For this example, we will register two non-logging triggers on the Vendor Maintenance form: the first non-logging trigger will start up a logging trigger that initiate application events logging - DEXSQL.LOG and Dexterity Script Log for this example - when the Hold checkbox is activated. The second non-logging trigger will stop the logging trigger upon closing the Vendor Maintenance form.

Think of the advantages of this for a second... imagine a user who has been experiencing a random issue for quite some time in a window and the data being processed in that window. It would be fantastic if you could turn on and turn off Microsoft Dynamics GP logs upon certain event (or events) happening on that window without the user having to be aware or have the presence of mind to do this himself/herself. This certainly would make the process of troubleshooting quite simple, less intrusive, and more precise!

So let's take a look at what is required:

1. A logging trigger that activates our DEXSQL.LOG and Dexterity Script Log when the hold checbox is marked.

2. A non-logging trigger that will register the above logging trigger when the Vendor Maintenance form is opened.

3. A non-logging trigger that will un-register the logging trigger in number 1 when the Vendor Maintenance form is closed.

The Logging Trigger

We will start by setting up the logging trigger that will activate only when the Hold checkbox is marked. The trigger type in this case is a Focus Event and will only fire when the field changes, that is, when is marked - conceivably, you could add code that deals with the event of unmarking the checkbox. We want any action to begin once the Microsoft Dynamics GP code has run on that field, hence we will initiate our logs after the original event. Note also, this trigger will not start automatically upon the user launching and login into Microsoft Dynamics GP, since we only want this to happen when the Vendor Maintenance form is opened - and stop when it is closed.

VEND_HOLD_CHG trigger - Resource tab

On the Actions tab, we will just send a desktop alert to the end user by choosing the respective option.

VEND_HOLD_CHG trigger - Actions tab

The beauty of the Support Debugging Tool is, you really need little in the form of Dexterity development knowledge and probably just an understanding of basic programmatic constructs to follow along and complement the helper functions. See, while you were fiddling with the setup options for the trigger in the Resource tab, the Support Debugging Tool was putting together some code for you based on your selections.

VEND_HOLD_CHG trigger - Script tab

Since we want the logs to be captured when the Hold checkbox is marked then the helper function seems just adequate. This means, no need to really have to add more code - unless, of course, there's something else you would like to achieve beyond what's already given in the helper code.  Remember, the code is Dexterity sanScript code.

Finally, for this logging trigger, we want to, well, log something. For that we go to the Options tab.

VEND_HOLD_CHG logging trigger - Options tab

In this tab, we will want to choose the DEXSQL.LOG and the Dexterity Script Log. If you suspect performance issues, you can activate the Dexterity Script Profiler - see How to read a Dexterity Script Profile to solve Performance Issues over at Developing for Dynamics GP for more information on this.

That's it! This easy! Our logging trigger is ready and now we will just need the non-logging triggers to start and stop it - in Dexterity parlance, register and unregister it.

Tomorrow, I will walk you through setting up the non-logging triggers that will start up this one and show some more cool helper functions. You will also have links to download the configuration files for this SDT project. For now, go and grab the Support Debugging Tool if you haven't done so yet.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Defaulting "Include Totals and Deposits" in Sales Transfer Entry with the Support Debugging Tool

More times than I care to count I have been called in to fix records left behind because the Include Totals and Deposits option was not marked when transferring a sales document from an existing order to an invoice. The problem is, it is so easy to forget to mark that box if you are doing this for hundreds of transactions fractured in multiple batches, especially at the end of the week when invoices need to be submitted to customers, prior to the FedEx truck arriving at 4:00 PM.

Sales Transfer Documents window

While this "fix" has been provided as a Modifier with VBA project before, I thought, why not really prop this up a bit with a Support Debugging Tool non-logging trigger and take advantage of the Automatic Debugger Mode functionality? Well, I have done just that!

The first thing we need to do is setup the trigger itself. In this case, we want to select a Focus Event trigger that will fire exactly on a field change condition. The field in question is the '(L) Order To Invoice CB' checkbox field. You can use the lookup button next to the Form Name field on the Resource tab to open the Support Debugging Tool's Resource Explorer window and locate the field and select it.

Trigger Setup

Since this will be a non-logging trigger, we will want to check the Do not activate Logging Mode option on the window. And to make sure the trigger always starts up with Microsoft Dynamics GP, we will want to check the Start Trigger Automatically on Login option as well.

Second, we can choose to have the Support Debugging Tool perform certain actions when the event fires - the event being when the checkbox is marked. If you happen to be running build 15 of the SDT, you can have a message display as a desktop alert on the lower right corner of your screen, similar to when you receive a Microsoft Outlook message.

For the purpose of this trigger, we will not take any actions when the trigger fires.

Third, we must add the processing script. The Support Debugging Tool does a really good job at providing a helper to build upon. So all we need to do is add the little bit to process the Include Totals and Deposits checkbox field, after the Transfer to Invoice checkbox has been marked. This is the final script:

Trigger processing script

Finally, since this is a non-logging trigger, the Options tab will show all logging options disabled. However, you can still choose a date range for this trigger to take effect.

Options tab

Since the goal is to have this trigger available at all times, there's no need to add a start and end date. It's now time to save the trigger and enable it.

We can choose to turn on our Non Logging Automatic Start Only triggers. Since our SOP_XFER trigger has been marked as a non-logging automatic trigger we should be able to see it in the Automatic Debugger Mode Status window.

Automatic Debugger Mode Status window

This is really all there is to it! The trigger should now cause the Include Totals and Deposits checkbox to activate automatically when we choose the Orders to Invoices checkbox.

I have attached the configuration file to be imported with the Support Debugging Tool's Configuration Export/Import window. Once you import the XML file just activate your trigger as indicated above and you should be good to go. No more forgetting to mark Include Totals and Deposits when transferring orders to invoices.

Include Totals and Deposits trigger - SOP_XFER.zip


The Support Debugging Tool's non-logging triggers functionality is a very powerful way to deploy simple scripts that can take care of every day headaches. All you need is a bit of creativity and the tool itself - see David Musgrave's article Support Debugging Tool Build 15 released for more on the latest Support Debugging Tool build.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Reconciling unchanged bank statements

Just recently, I worked on a case with a partner, whose client had a checkbook setup. For the past serveral months, the bank account associated to the checkbook has had no transaction activity. The client still receives a bank statement each month with no transactions.

When the client would try to enter the cutoff date information into the Reconcile Bank Statment window, even though the difference is zero, the system is not letting them reconcile the statement.

Reconcile Bank Statement window

They keep receiving a message that they need to mark the items they want to clear.

Select Bank Transctions window - error when attempting to reconcile

As a result of not being able to complete the reconcile, the Last Reconciled Date field is not being updated on the Checkbook Maintenance window.

Checkbook Maintenance window

To overcome this issue, we had the client enter both an interest income and an other expense adjustment for a penny ($0.01), as shown below:

Reconcile Bank Adjustments

Once we returned to the Reconcile Bank Statements window and clicked on the Reconcile button, the process went through. Since Microsoft Dynamics GP does not post zero balance transactions to the same account in the General Ledger, then we were able to effectively not affect the GL. The added bonus, of course, and the problem needed to be solved - updating the last reconciled date on the checkbook - was taken care of with this workaround.

Reconciled Checkbook

Of course, we could have made the changes directly in SQL by updating the Checkbook Master table (CM00100), but that would have left no audit trail of the reconciliation for the accounting department. As for those pennies... they are just that, pennies. The adjustments were documented with notes that explained clearly that they served just as a workaround so auditors would not throw a fit.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

SSRS: The deployment has exceeded the maximum request length allowed by the target server

Earlier today, I was deploying SQL Server Reporting Services (SSRS) reports for a client who just upgraded from version 9 to version 2010 R2 on Microsoft SQL Server 2008 R2. Upon beginning the deployment, the following error came up:

maxRequestLength error deploying BI reports

The error and it's resolution are pretty clear and called for setting the maxRequestLength attribute to "20690" in SSRS's web.config file.

Web.config

The SSRS web.config file can typically be found under the %programfiles%\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\Report Server, for a Microsoft SQL Server 2008 R2 SSRS installtion done in Native mode. If you have other types of deployments, for example, SharePoint integrated, it will be necessary to consult with your system administrator.

You can then proceed to use Visual Studio or, simply, Notepad to edit the web.config file, locating the httpRuntime element of the XML document.

web.config - httpRuntime section

The final edit of this section should look like this, with the maxRequestLength attribute added.

web.config - Edited httpRuntime section with maxRequestLength parameter

Once the web.config file was saved, I reinitiated the deployment and all was well. The maxRequestLength attribute specifies the maximum upload size for a file into the SSRS reports library in kilobytes. For this specific instance, 20,690 seems to be the magical number.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.intellpartners.com/

SmartList Builder and creating Calculated Fields with Extender data

Just recently, I ran into a case where the partner was creating a SmartList Builder calculated field using data from the RM Open table (RM20101) via an Left Join table operation with the Extender Window Field Numbers. In fact, this is a very typical scenario for a lot of deployments where Extender is used, especially when you cannot use the standard Extender functionality to integrate with out-of-the-box smartlists.

The original SLB calculated field look something like this:

Extender Calculated Field

When the SLB smartlist was deployed, a number of results came back as zero for the records where there was no entry in the Extender Window Field Numbers table, even when the Sales Amount and Current Trx Amount fields had a value in the RM Open File table.

Paying a bit more attention to the issue made me think of how LEFT OUTER JOINs are processed by the Microsoft SQL Server query engine. This is best illustrated with the following example:

RM Open				Extender
Customer Number	Document Number	Sales Amount	Current Transaction Amount	PT UD Key	PT UD Number	Total
AARONFIT001	INV3223	200.00	40.00	INV3223	5.00	5.00
ADAMPARK001	INV1020	100.00	20.00	NULL	NULL	NULL

Note that if Extender data was not entered for INV1020, a left outer join query would produce a NULL value as a result of the join operation. To overcome this situation, we applied the T-SQL ISNULL() function to the Extender field in SmartList Builder. Since SmartList Builder uses pass-through SQL to build each portion of the SELECT statement used to retrieve the records, then this should work just fine. The final calculated field is as follows:

Remember, Extender is a valuable tool to capture additional data and enhances the value of SmartList Builder when combined together to deliver reports. Pay special attention when creating calculated fields that rely on information from Extender tables in left join scenarios.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Microsoft's upcoming products available for download

A bit away from the Microsoft Dynamics GP world, but at the same time so close, here are some very good news on the upcoming release of Microsoft SQL Server "Denali", Office 2010 and SharePoint, and Internet Explorer.

Download SQL Server Code Name "Denali" CTP3

SQL Server Code Name "Denali" Community Technology Preview
(CTP3) provides the foundation for the cloud-ready information
platform, and will help customers unlock insights across the
organization and quickly build solutions that extend data across
on-premises and public cloud.

Office 2010 and SharePoint 2010 Service Pack 1 Now Available

Service Pack 1 is now available for the 2010 family of products, including Office, SharePoint, Project Server, FAST, Search Server, and Groove Server. For more on deployment, see these articles: Apply Office 2010 Service Pack 1 and Deploy Service Pack 1 for Project Server 2010. Also, download the Service Pack 1 for SharePoint Foundation 2010 and SharePoint Server 2010 White Paper.

Internet Explorer 10: Platform Preview 2

The latest preview build of Internet Explorer 10, Platform Preview 2,
is now available for download. With the second Platform Preview,
developers can start working with several site-ready HTML5
technologies.

Remember... should you decide to try any of this code, do so in a test environment.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

SSRS: GL Trial Balance Summary report returns no data

This one comes courtesy of my friend Steve Sieber at McGladrey.

After installing Microsoft Dynamics GP 2010 R2 and deploying the SQL Server Reporting Services reports, you will encounter an issue when printing the GL Trial Balance Summary SRS report located under Financial.

1. Launch Report Manager and click on the company for which you would like to run the report (the issue can also be reproduced in the Fabrikam (TWO) company database). Click on Financial | Trial Balance Summary, enter all the parameters and options for the report the click on View Report, the following is returned:

GL Trial Balance Summary - SSRS

As you can see, even though the correct parameters are selected, the report returns no records.

2. If the GL Trial Balance Summary report is executed from GP with the same parameters, the report correctly delivers the expected records and result:

GL Trial Balance Summary - GP

From a technical perspective, the GL Trial Balance Summary SSRS report executes the dbo.seeGLPrintSRSTrialBalance stored procedure. The issue appears to be that the #GLTBDTemp temp table does not get populated with the records needed to render the report. You can test the stored procedure by executing the following statement from SQL Server Management Studio against any company database.

exec seeglPrintSRSTrialBalance 0,0,0,'000-0000-00','999-9999-99','01/01/2017','12/31/2017',2017,0,1,1

This issue has been reproduced by Microsoft Support and they are currently researching the problem for a solution. 

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

More Microsoft Dynamics GP Word Templates Articles

If you have been keeping up with this blog, you will remember that sometimes around October of 2010 I posted a series of articles related to Microsoft Dynamics GP Word Templates - see Microsoft Dynamics GP 2010 Word Templates summary for more information. These articles focused on "how to's" and troubleshooting aspects of Word Templates.

Now the Dynamics Support and Services Team Blog has decided to launch a new set of video-articles detailing more common aspects of working with Word Templates. You can now visit the Community Team blog for the release article and the schedule of when these videos will be out. The good news is the first two are out!

Take a look at:

Adding the Developer tab in Microsoft Word after installing the Microsoft Dynamics GP Add-in for Microsoft Word - Video

Helpful tips when modifying Word Templates in Microsoft Dynamics GP 2010 - Video

Please visit the Dynamics GP Support and Services Blog for these videos and to find a collection of very new and interesting articles.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Violation of PRIMARY KEY constraint 'PKSY60100'

Just recently, I assisted a partner with an issue they were having creating a new company in Microsoft Dynamics GP 10 - though, I supposed the same could happen with any other version. In the process of creating the company record, almost at the end of all the routines executed by Dynamics Utilities they were getting the error:

Violation of PRIMARY KEY constraint 'PKSY60100'. Cannot insert duplicate key in object 'dbo.SY60100'

KB Article 871699  suggests the problem could be that the DYNAMICS database is associated with a database owner (dbo) other than DYNSA. After running the sp_helpdb system stored procedure, it was determined that the database owner of the DYNAMICS database (and other company databases) was indeed 'sa'. Knowing this obviously helps, and the solution is as simple as changing the database owner back to DYNSA.

The partner then ran the sp_changedbowner system stored procedure to reset the database owner to DYNSA and got the following error:

Msg 15110, Level 16, State 1, Line 1
The proposed new database owner is already a user or aliased in the database

Having gotten this error, we proceeded to drop the DYNSA from the DYNAMICS database as follows:

USE DYNAMICS;
GO
DROP USER DYNSA;
GO

Having dropped the user from the database, we needed to re-add DYNSA as the database owner of the DYNAMICS database. This time, I decided to try the new ALTER AUTHORIZATION statement as the customer is running Microsoft SQL Server 2008 R2, as sp_changedbowner will be deprecated from SQL Server sometimes soon.

ALTER AUTHORIZATION ON DATABASE::DYNAMICS TO DYNSA;

Once we executed this command, we restarted the company creation process in Utilities and the error was no longer.

If you find yourself in a similar situation then this should definitely help.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/