Wednesday, December 31, 2008

MSDynamicsWorld releases top 10 stories of 2008

The independent "All things Dynamics" news and resources outlet, MSDynamicsWorld.com has released its top 10 stories of 2008 and I am pleased to announce that one of my articles --Maximize your investment in Dynamics during uncertain economic times -- made it in that roaster due to the traffic and acceptance it has received among the Dynamics user community during the course of the year. If you are always looking for more than just the technical or functional stuff, be sure to check MSDynamicsWorld.com for comprehensive news and articles about all things Dynamics.

Until next post!

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

Disclaimer
I am a member of the MSDynamicsWorld Editiorial Advisory Board, but in no way influenced the selection of my article for the top 10 stories of 2008.

Monday, December 29, 2008

Mark Polino on Deleting Extender Windows


Fellow MVP Mark Polino has important information for you if you happen to be working with Extender windows. Make sure you read his article on the subject BEFORE you attempt to remove that Extender window you have been working with for a few months now.

Until next post!

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

2009 Payroll Tax Update for Microsoft Dynamics GP 8.0

Dynamics GP consultants outta be some of the most dedicated professionals on the face of the planet. In the past days, many GP 8.0 users have been wondering how to get 2009 tax updates applied to their systems. What was more discouraging is the fact that Microsoft had released a not-too-promising memo about the availability of the 2008 year end and 2009 tax updates.

Nonetheless, my friend Doug Pitcher over at Rose Business Solutions, now has an answer for everyone of you still on GP 8.0. He had been playing around with the 2009 tax update SQL Scripts for 10 and noticed they could work on GP 8.0, so just stop by and download the scripts from his site. Great job Doug!

Until next post!

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

Friday, December 26, 2008

Getting started with SharePoint Development


As Microsoft continues to launch products on the Windows SharePoint Services (WSS 3.0) and Microsoft Office SharePoint Services (MOSS 2007) platforms, namely PerformancePoint and Workflow, and as they continue to enhance the integration between Dynamics GP and these applications, I thought it would be important to point out some good SharePoint resources, in particular Paul Andrew's blog.

Paul is a Microsoft Technical Product Manager for the SharePoint Developer platform and he has compiled a good Getting Started with SharePoint Development article with tons of links to other resources and a good step-by-step approach on how to become a pro.
I started learning about SharePoint development techniques about a month ago as part of a project that I am working on and Paul's article has been vital in providing a roadmap to becoming proficient. In addition, I highly recommend this free Microsoft Learning clinic course: Clinic 5045: Inside Look at Developing with Microsoft® Windows® SharePoint® Services 3.0 found here

Please take a look at all Paul's other articles as well, since they provide important news and updates about everything SharePoint.

Until next post!

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

How to roll down changes to SOP's Requested Ship Date to line items and purchase orders

Business Situation

A sales order entry clerk has been instructed to change all requested ship dates on a 100-line item sales order. This sales order is also linked to a purchase order for the 100 line items. However, the clerk has discovered that Dynamics GP will not allow these changes to take effect by changing only the Requested Ship Date on the transaction header and must go into each line item to accomodate the for the new request. In addition, the clerk must communicate to the company's buyer that all line items on the PO must be changed to meet the new Required Date.

Solution

It would be much easier sometimes if certain Dynamics GP functionality was available out-of-the-box. However, that would leave a lot of us without a job :-).

Throughout the application, we get so many messages about rolling down changes across multiple records, but SOP seems to be one of those modules where rolling down changes to line items don't seem to be a choice. Take for example Requested Ship Date field. Over and over, I have seem so many newsgroup posts asking for this feature. There are typically two approaches: a) you can create a SQL Server trigger to address the changes when the Requested Ship Date is updated from the SOP document header, or b) you can create a VBA customization that will ask to rolldown the changes, and if the user acknowledges, then run a stored procedure to make these changes happen.















I like both approaches, however approach (b) provides ample flexibility and allows for an interactive user experience. In this article, I will address option (a) and will follow up -- in a second article -- with option (b).

So let's take a look at our SQL Server trigger first.


CREATE TRIGGER dbo.sopRollDownReqShipDate ON SOP10100 AFTER UPDATE AS
IF UPDATE(ReqShipDate)
BEGIN
BEGIN TRAN

-- roll down to all items
UPDATE A WITH (ROWLOCK) SET A.ReqShipDate = I.ReqShipDate
FROM SOP10200 A INNER JOIN INSERTED I ON (A.SOPNUMBE = I.SOPNUMBE) AND (A.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) -- only orders and invoices

-- updated the Required Date column for linked POs
UPDATE A WITH (ROWLOCK) SET A.REQDATE = I.ReqShipDate
FROM POP10100 A INNER JOIN SOP60100 B ON (A.PONUMBER = B.PONUMBER)
INNER JOIN INSERTED I ON (B.SOPNUMBE = I.SOPNUMBE) AND (B.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) -- only orders and invoices

-- updated the Required Date column for the line items on linked POs
UPDATE A WITH (ROWLOCK) SET A.REQDATE = I.ReqShipDate
FROM POP10110 A INNER JOIN SOP60100 B ON (A.PONUMBER = B.PONUMBER) AND (A.ORD = B.ORD)
INNER JOIN INSERTED I ON (B.SOPNUMBE = I.SOPNUMBE) AND (B.SOPTYPE = I.SOPTYPE) AND (I.SOPTYPE IN (2, 3)) -- only orders and invoices

IF @@ERROR <> 0
COMMIT TRAN
ELSE
ROLLBACK TRAN
END
GO


Let study our trigger for a bit. By using a BEGIN TRAN and checking for errors before committing out transaction, with SQL Server's @@ERROR global variable, we are ensuring that our trigger will work as a single block of code. This is, we are only committing all changes if the updates were all successful.

By performing an UPDATE A WITH (ROWLOCK), we are allowing our trigger exclusive access to the rows involved in the update. This will prevent other changes from taking place on these rows while our trigger attempts to change the records.

Finally, our DML trigger makes use of the INSERTED special table to account for all header records being modified at once. You may say, well sales orders and invoices are updated one at a time in GP. This is true, but if you have an integration that insert records in bulk as part of a transaction, say for example orders coming from a CRM system through a BizTalk orchestration, you may need to address all these orders as one transaction block instead of individually. Also, working with INSERTED prevents the use of SQL Server cursors and allow the use of a set-based approach to our implementation.

Please stay tuned for the follow up post!

Until next post!

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

Wednesday, December 24, 2008

Season Greetings to all the Microsoft Dynamics GP community!


As many of you get ready to celebrate Christmas, Hanukkah, Kwanzaa, or simply relax in the company of your families and friends, I would like to take this opportunity to wish you the best throughout these festivities on behalf of my wife Marina and children.

Thanks for all the great comments I received and support to my blog. It keeps me going and working to bring you the some of the best content you will find about all things Dynamics GP.

You can always count on 2009 bringing new and exciting articles with innovative approaches for using Microsoft Dynamics GP and getting the best out of your investment.

Until next post!

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

Victoria Yudin compiles Microsoft Dynamics GP resources page


This article should suite me really well! I for one, spend a lot of time trying to find the same Dynamics GP pages over and over: in PartnerSource, in CustomerSource, in the Knowledgebase... It is quite frustrating when you have to get back at these pages more than once and have not bookmarked them or add them to your favorites. Fellow MVP Victoria Yudin must have been experiencing the same issue, but she decided to take action! Victoria has now created a Resources page on her blog pointing to the most typical links: systems requirements, printer compatibility list, installation guides, user guides, and much more.

If you feel lost, then this is the best place to get started.

Until next post!

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

Monday, December 22, 2008

URGENT: 2008 Year End Update and Microsoft Great Plains 8.0


The questions have started to flood the newsgroups and apparently many users and customers are not aware (and perhaps, were not made aware by their partners) that there will be no Year End Update pack for Microsoft Great Plains 8.0. According to a support bulletin from the Product Lifecycle page last modified on April 1, 2008:

"Mainstream Support for Microsoft Dynamics GP 8.0 will end October 13, 2009 (previously October 9, 2007). Microsoft will provide a 2007 Year End Regulatory/Tax update. (U.S. and Canadian tax updates will be provided through October 31, 2008.) No additional tax or regulatory updates will be provided for 2008 Year End as well as 2009."

The complete article can be accessed as follows:

PartnerSource - Click Here
CustomerSource - Click Here

What to do now?

There are a few options:

1. Upgrade to Microsoft Dynamics GP 10.0 - For this you may contact your Microsoft partner to establish the viability of this option -- you can contact me or any Maximum Global Business representative for pricing on upgrades to GP 10. Also, take a look at my Upgrade Path article for options if moving in this direction.

2. Contact Microsoft Support and plea your case. Typically, once a product has completed its cycle there will not be any updates to it, however, the customer is always right! and that may just prove to be your case.

Until next post!

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

URGENT: Applying 2008 Year End updates for Microsoft Dynamics GP 9.0 and 10.0


Many GP administrators and partners have been experiencing issues after applying the Year End updates. These issues may range from corrupted reports dictionaries, apparent posting errors, and system crashes.

My 2 cents on the issue and how to prevent them from happenning: since these Year End updates are inclusive of service pack 4 and Service Pack 3 for Dynamics GP 9.0 and Dynamics GP 10.0, respectively, you must follow all procedures outlined for a service pack installation, especially as it concerns modified reports and modified forms for Dynamics GP and third party products:

1) MAKE SURE TO EXPORT ALL CUSTOMIZATIONS (Forms, Reports, VBA Customizations, References) TO A PACKAGE FILE (.Package), UNDER TOOLS > CUSTOMIZE > CUSTOMIZATION MAINTENANCE IN GP 9.0 AND MICROSOFT DYNAMICS GP > TOOLS > CUSTOMIZE > CUSTOMIZATION MAINTENANCE IN GP 10.0.

2) REMOVE ALL EXISTING MODIFIED REPORTS DICTIONARIES AND MODIFIED FORMS DICTIONARIES FROM THEIR CURRENT LOCATION. TO BE ON THE SAFE SIDE, MOVE THESE DICTIONARIES TO ANOTHER LOCATION ON OR OFF YOUR SERVER.

3) BACKUP DYNAMICS AND ALL COMPANY DATABASES PRIORT TO APPLYING THE YEAR END UPDATE PATCH. STORE THESE BACKUPS IN A QUICKLY ACCESSIBLE LOCATION.

4) APPLY THE YEAR END UPDATE PATCHES FOLLOWING ALL INSTRUCTIONS OUTLINED BY THE INSTALLATION NOTES AND RUN DYNAMICS UTILITIES TO PERFORM ANY UPDATE AND VERIFY ALL DATABASES.

5) REIMPORT ALL YOUR CUSTOMIZATIONS FROM THE PACKAGE FILE EXPORTED IN STEP 1.

Until next post!

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

Web Services, Visual Studio Tools and Dexterity partner-only developer workshops


Over at Developing for Dynamics GP, my good friend Scott Stephenson with Microsoft Dynamics GP Tools Support has released a schedule with upcoming developer workshops. These workshops are partner-only workshops and will be held in the month of February. If you or your organization are interested in these classes, Scott has published dates and prices. Please click here for more information.


Until next post!

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

Mark Polino on Updating Budget Lines in Project Accounting


Fellow MVP Mark Polino, continues his journey into the new Project Accounting features released with Microsoft Dynamics GP 10. This time, Mark takes a look at Updating Budget Lines in his all too famous Weekly Dynamic column. The ability to quickly update budget lines across multiple cost categories is certainly to benefit any company using Project Accounting in industries such as Light Construction, Installers (Field Service), Professional Services, among others. So make sure to read this article and all the previous ones posted by Mark and don't forget to let him know what you think or what you would like to see covered in the future.

Until next post!

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

How to determine if a report or a form has been modified

Business Situation

How many times have you walked into a GP implementation done by a previous VAR and cannot establish what changes have been done on a report, if any? Or how many times have you come across forms and reports dictionaries with tons of objects and cannot tell by simply looking at these if they have been changed or not? Or have you made some changes to a report a few years aback and now cannot remember what these changes were? I get this question every once in a while and finally someone was keen enough to post it on the Dynamics GP public newsgroup.

Solution

Let me start by saying that ALL modified forms and reports should ALWAYS be backed up in the form of package files, and that ALL these package files should be stored in a source code control repository -- for example, Visual Source Safe -- and versioned if all possible, with notes on all changes done from version to version. However, this is not always possible, especially if the company happens to be a small company with limited technical and software development resources -- unless of course, the business happens to do software development :-) ).

In order to establish what changes have been done to a form or a report, without having a source control repository, you can use old fashioned Microsoft Word... well, I will be using Microsoft Word 2007 for this example. In addition, we will use a slightly modified version of the SOP Blank Invoice report.






















*Click on image to enlarge

1) Export the modified report to a package file. Go to Microsoft Dynamics GP > Tools > Customize > Customization Maintenance. Highlight the SOP Blank Invoice report and click on Export. Save the file as SOPBlankInvoice_Modified.package


















*Click on image to enlarge


2) From a workstation not pointing to the REPORTS.DIC dictionary file containing the modified report or from a standalone copy of GP, say for example the one you carry on your laptop, print the SOP Blank Invoice report to screen. Go to Transactions > Sales > Sales Transactions and choose an invoice. Print to screen. Click on Modify to open the report with Report Writer. Once the report is shown in the Report Layout window, return to Dynamics GP.















*Report with no customizations. Click on image to enlarge


3) Repeat step 1 on your standalone environment, this time saving the file as SOPBlankInvoice_Original.package. Move the SOPBlankInvoice_Modified.package file to the same directory with the SOPBlankInvoice_Original.package file. This will both files easily accessible.


















*Click on image to enlarge

4) Open Microsoft Word 2007. Click on the Review menu item.




*Click on image to enlarge

5) Click on the Compare option. Open your SOPBlankInvoice_Original.package in the Original document column, open SOPBlankInvoice_Modified.Package in the Revised document. Changes can be labeled with markers to allow for easy identification. Word will run the comparison and highlight any changes between the two documents














*Click on image to enlarge

Remember: it's not about the tools, it's about how you use these tools to meet your needs. I hope this article provides a mechanism to quickly and accurately identify changes between original reports and customized reports and help you get a headstart when working at customers and customizations you have not created -- or may have and don't recall :-) .

Until next post!

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

Sunday, December 21, 2008

Welcome Leslie Vail, MVP to the blogosphere!


Finally! Leslie has decided to come out of hiding with her new Dynamics Confessor Blogspot. For those of you not familiar with Leslie, she is a Microsoft Dynamics GP Certified Trainer and a long standing consultant. She has authored the Quickbooks Movers Guide and co-authored the all to famous Confessions of a Dynamics GP Consultant published by Accolade Publications. Stop at her site and let her know what topics you would like to see on her site. However, I am sure she will be putting up some good stuff pretty soon.

Until next post!

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

Saturday, December 20, 2008

Developing for Dynamics GP - Weekly Summary


With David Musgrave out on vacations it would seem the articles would cease. Not the case at all! David made sure he left enough scheduled published articles so he did not have to get on the computer during his cruise throughout the South Pacific, perhaps enduring some hard knocks from Jennifer in the process. So let's see what David left for us.

If you are using Dexterity 10 (build 10.00.0320) Source Code Control with either the Generic Provider or Visual Source Safe, be aware of a bug when checking in multiple resources at once to the code repository. Click here to find out more about this issue and how to resolve it.

On the topic of Source Code Control, David explores the proper use of Index Files. An index file will help with keeping resource IDs in check, more so when you have more than one developer working on your Dexterity product. Make sure you check this article, since it also includes links to published KB articles on the subject.

This one evokes some nostalgic moments. We are also so used to the fancy interfaces: SQL Server Management Studio, Query Analyzer, even Excel. There was a moment in time when working with data in earlier versions of Dynamics GP, especially those running on Ctree and Btrieve, was a royal pain in the rear and identifying and correcting data issues and table corruptions wasn't as simply as it is today. There was DBrowser, then Dynamic Data Browser (DDB.EXE). David offers some history and provides a real life application of the tool. If you are a consultant and don't have this tool in your toolbox... you get my point!

Support Debugging Tool comes back with a 1-2 combination punch. This new build (build 10) includes the all-so-awesome Security Profiler and the all-too-cool Screeshot features. Please read David's Release Notes and download the tool. Check my SDT redux article for more info as well.

Some things may appear to be obvious sometimes, but I have also found people asking about access to CustomerSource and PartnerSource. My old friend, Scott Stephenson from Microsoft Dynamics GP Tools Support in Fargo, and David, both provide some of the criteria required for accessing any of these two Microsoft secure sites. Click here to read more.

Until next post!

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

Thursday, December 18, 2008

Support Debugging Tool Redux

Let me start by saying, if you are not using Support Debugging Tool you really are making things more difficult for yourself than you should!

Back in August, I was part of a selected group of beta testers and was introduced to the tool, and have since writen a number of articles about it, including my First Look article, which also got featured in MSDynamicsWorld.com. Back then, I presented some of the coolest features I had had the opportunity to explore in detail. This time, David Musgrave does it again with this revised version. Let me say it again just in case: if you are not using Support Debugging Tool you are truly making things more difficult than they should for yourself.

This build of Support Debugging Tool has been enhance with a number of performance improvements and now introduces two new features I believe will blow you away: Security Profiler and Screenshot capturing.

Security Profiler

Security Profiler is by far the most notorious new feature in Support Debugging Tool. Short of talking, this feature has been enhanced to provide detail information about security issues as they are happening and can be setup to trap [security] errors automatically and instantly when they occur. In addition, users now have the ability to export the security log to an XML document that can be uploaded by administrators for troubleshooting purposes -- no more guessing what security roles and security tasks are out of wack!

As the security log is built, Security Profiler identifies the state of the object being accessed, this is, whether the object is original, modified, or alternate modified. This also improves the chance of detecting whether the user is seing the right object to begin with.














* Click on image to enlarge

Once the security log is built to the point of an error condition, the user can then export the log to an XML file that can be used by an administrator to troubleshoot the problem. Once the administrator imports the file, he or she can pretty much replay the sequence of actions leading up to the security error condition.

Now get this!

Through each step, the administrator can right-click -- you read correctly! Right-click! -- on the object to obtain detail security information. The Security Information window is the brain of the Security Profiler and breaks down the security tasks and roles associated with the current user, and further more, provides information at the system level of all the tasks and roles with access to the object.





















* Click on image to enlarge

Long gone are the days trying to figure out what roles and tasks are associated to what objects. You can also double-click on a role or a task to obtain more information. This will open up standard GP security windows.

Screenshot Capturing

Support Debugging Tool adds a new shortcut for those of you who love to find interesting shortcuts in GP, that is CTRL + S. Screenshot Capturing is a new powerful tool included as part of this new build of Support Debugging Tool. It does as the name suggests, with a twist: it allows the user to incorporate in an email valuable troubleshooting information such as a copy of the DEX.INI settings file, the DYNAMICS.SET launch file, and a complete System Summary of the user's workstation. How is this important? Ever wonder if the user is pointing to the correct REPORTS.DIC or FORMS.DIC dictionary files? Ever wonder as an administrator if the user is missing a customization? Ever wonder if the user machines is missing some GP service pack? All these questions are answered with this feature.


















The email is jammed pack with information for administrators and will definately take the guessing game out of troubleshooting. One note though, Support Debugging Tool will not be able to capture screenshot images for modal dialogs and error conditions, these will have to be captured through traditional print screen methods and submitted in addition to, or as additional attachments to your email.


















* Click on image to enlarge

Please visit David Musgrave's blog, Developing for Dynamics GP, for his release notes on this new exiting build of Support Debugging Tool.

Downloads

Support Debugging Tool Build 10 is available from PartnerSource. Please use the following links to obtain a copy or contact your Microsoft Partner for more information.

Support Debugging Tool for Microsoft Great Plains 8.0
Support Debugging Tool for Microsoft Dynamics GP 9.0
Support Debugging Tool for Microsoft Dynamics GP 10.0

Until next post!

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

Deck the halls with Report Writer

Business Situation

The holidays are around the corner and your organization is looking to send season greetings on each invoice to all customers. However, the traditional Microsoft Dynamics GP text comment is all too dull and does not reflect the image or the character of your organization. Your marketing team has spent some good hours designing a holiday banner and would like to see it on the invoices at all cost.















Lets take a look at some cool ways to deck the halls with Report Writer.

Solution

For this example, we will work with the SOP Blank Invoice Form report.

1) Open the Sales Transaction Entry window, select an invoice and click on the print button. When the options window is displayed, select the Blank form type and then print to screen.





















* Click on image to enlarge

2) Press CTRL + F9 on your keyboard to modify the current report. This will open Report Writer with the report in the layout window.

3) We will now add a picture to the Picture library to include on our invoice. In Report Writer, go to the Resources menu and select Pictures. This will open the Picture Definition window.












NOTE: Due to Dexterity limitations in color management, you will need to reduce the number of colors of your graphic image to 16 colors (4 bits per pixel). The file size must be less than 32KB which corresponds to the size limitation imposed by Dexterity.

4) Click the New button to enter a picture name, say Season_Greetings. Click inside the gray area, and paste the image from the clipboard with CTRL+V. Click OK to save the image.



















NOTE: The picture must be loaded with a graphics editor, like Paint for example, and have been captured with the Copy feature of that editor. This will send the image to the clipboard.

5) To add the image to the report layout, click the Picture tool button in the Toolbox window. This will activate the visual cue indicator.






















6) Click on the area of the report where the picture will be placed, for example on the footer. Size according to your display needs.










7) Return to Dynamics GP and grant security to your modified report. If the report has been previously modified, you are done!

8) Execute the report and verify it meets the visual requirements and complies with your company expectations.

RELATED ARTICLES:

How to get large amounts of text to look good on a report - by David Musgrave @ Developing for Dynamics GP.

Until next post!

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

Wednesday, December 17, 2008

Microsoft SQL Server codename "Kilimanjaro"


Oh my goodness! Just when I was getting comfortable with SQL Server 2008...The toys, for us SQL Server developers and practicioners, are seemingly becoming endless. Microsoft SQL Server codename 'Kilimanjaro' (my gut feeling tells me this will become SQL Server 2010) will be released in its Community Technology Preview (CTP) version by mid-January '09, with a duration of 4 to 5 weeks.

Kilimanjaro is jammed-packed with improvements to the BI engine and during the demo, it performed a 1-trillion row query in seconds!

Kilimanjaro was first unveiled at the Microsoft Business Intelligence Conference in Seattle this past October. During the conference, Microsoft committed to provide easy-to-use analysis tools in Kilimanjaro, described under their code name, "Gemini."

According to a Microsoft announcement, the Gemini tools "will enable information workers to slice and dice data and create their own BI applications and assets to share and collaborate on from within the familiar, everyday Microsoft Office productivity tools they already use."

If you want your organization to be considered for this private CTP, click here and good luck as seats are limited.

Other Resources

'Kilimanjaro' SQL Server CTP To Begin in January - VirtualizationReview.com
Kilimanjaro Registration Site - SQL PASS
Microsoft Empowers Enterprises to Think Bigger About Business Intelligence - Microsoft PressPass
Microsoft Exec Lays Out Enterprise Strategy at Barclays Event - RedmondMag.com

Until next post!

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

Tuesday, December 16, 2008

Victoria Yudin on Reporting on Extender Data


Fellow MVP Victoria Yudin, goes to great lengths to explain how to report on Extender data. Extender can be a valuable time saving customization tool, but data storage can be confusing even to the pros. Remember that Extender stores data in separate tables according to data types and the only way compile the data is by creating Extender Views.

Also don't miss out on her previous article about Extender and SmartList.

Victoria provides great detail on how to accomplish this with screenshots that will help you understand the process from beginning to end. More importantly, let her know what you think about her work by dropping her a comment.

Until next post!

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

Monday, December 15, 2008

How to setup "No payments, no interests until June 1, 2009" payment term in Microsoft Dynamics GP

Business Situation

The current economic climate is forcing businesses to be a bit more creative. Some of these companies are resorting to No interest, no payments terms that allow customers to walk out the stores with the merchandise and begin payments at a future date. But, how is a company using Microsoft Dynamics GP going to deal with this type of payment term? What if the promotion is only being ran for the month of December only? What if all invoice generated in December are all due simultaneously on June 1, 2009 regardless of the date the invoice was generated?

Solution Implementation

Since invoices from SOP and RM flow to the RM Open table (dbo.RM20101) after they are posted, it is safe to say we can add a SQL Server trigger to overcome the hurdle. However, we must setup a payment term ID that will serve as an identifier for our trigger to take action. Follow these steps to implement the solution to this problem:

1) Setup a new payment term called JUNE012009. Open the Payment Terms Setup window under Microsoft Dynamics GP > Tools > Setup > Company > Payment Terms. Fill in the window, as follows:












*Click on image to enlarge

2) With the payment term ready to go, we can now proceed to write our SQL Server trigger on the RM Open table (dbo.RM20101). Run the following T-SQL script against your company database.

trigger dbo.trPaymentTerm
/* Created by Mariano Gomez, MVP
Code is delivered "AS IS". No warranties expressed or implied
*/
CREATE TRIGGER dbo.trPaymentTerm ON dbo.RM20101 AFTER INSERT
AS

BEGIN TRAN

UPDATE A
WITH (ROWLOCK) SET A.DUEDATE = '2009-06-01'
FROM dbo.RM20101 A INNER JOIN INSERTED I ON (A.DEX_ROW_ID = I.DEX_ROW_ID)
WHERE (I.PYMTRMID = 'JUN012009') AND I.DOCDATE BETWEEN '2008-12-01' AND '2008-12-31'

IF @@ERROR = 0
COMMIT TRAN
ELSE
ROLLBACK TRAN

GO
A few things to highlight are, the UPDATE statement in the trigger uses the DEX_ROW_ID to match the records inserted against the records already committed in the table. This is a valid use of the DEX_ROW_ID column. For more information on the DEX_ROW_ID column please check my previous article on the subject.

It is also worth noting that the WHERE clause contains all the rules we originally set out to obey, that is, we would apply the payment term for any document that had the 'JUN012009' payment term assigned, but also, the documents must be created during the month of December. This is important, since we need to prevent our trigger from firing for months other than December.

An enhancement could be introduced in the trigger to raise an error on the Dynamics GP user interface (using the RAISERROR statement), for any invoice document that does not meet the criteria.

Hope you find this post useful and I would like to hear your comments.

Until next post!

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

Thursday, December 11, 2008

The DEX_ROW_ID column

From time to time the question comes up in newsgroups, informal conversations between developers, and surprise phone calls. The questions come in many flavors:

  • What is the DEX_ROW_ID?
  • Why do all Dynamics GP's SQL Server tables have a DEX_ROW_ID?
  • How is the DEX_ROW_ID related to IDENTITY columns in SQL Server tables?
  • Can I build reports using the DEX_ROW_ID column?

To start unreeling these questions it is best to start with two key concepts: active and passive record locks.

Active Locks

An active lock allows other users to read a table record, but not make any changes or delete the record. Active locking ensures that the user who has the active lock is the only user who can make changes or delete the record. If other users try to delete or change the record, a table-sharing error will occur. An active lock is applied each time a record is read using the Dexterity change or edit table statements with the lock keyword included.

Passive Locks

A passive lock allows other users to access the record. Other users can delete the record or make changes to it. Passive locking ensures that other users accessing the record can be made aware that the record has been deleted or that the contents of the record have changed. A passive lock is applied each time a record is read using the change or edit table statement.

What is the DEX_ROW_ID?

As part of Dexterity's table definition requirements, active locking must be enabled on a per-table basis by marking the Allow Active Locking option in the table’s Table Definition window.

For both active and passive locking to work properly in a Dexterity-based application, such as Microsoft Dynamics GP, every SQL table must include a column that is used to track the identity of individual records being locked. This column is the DEX_ROW_ID.

Why do all Dynamics GP's SQL Server tables have a DEX_ROW_ID?

When Dexterity's Runtime Engine creates a table, the DEX_ROW_ID column is added to each table created automatically. In addition, this column is hidden to the table definition within Dexterity application.

If tables are created through a method other than allowing the Dexterity runtime engine to create them, then the DEX_ROW_ID column must be added via SQL Server Management Studio or using the Transact-SQL (T-SQL) ALTER TABLE statement, as follows:



ALTER TABLE dbo.TableName ADD DEX_ROW_ID INT IDENTITY;
GO



Dexterity uses the DEX_ROW_ID column internally. Developers need not to manipulate the column at all.

If tables are created outside of the Dexterity environment and do not include the DEX_ROW_ID column in each of your tables, active locking will not function properly for that table.

How is the DEX_ROW_ID related to IDENTITY columns in SQL Server tables?

From a pure SQL Server perspective, the DEX_ROW_ID is an INT type column with the IDENTITY property assigned to it. This enables the developer to specify both an identity number for the first row inserted into the table (Identity Seed property) and an increment (Identity Increment property) to be added to the seed to determine successive identity numbers. When values are inserted into a table that has an identifier column, SQL Server automatically generates the next identity value by adding the increment to the seed. When you add identifier columns to existing tables, the identity numbers are added to the existing rows of the table, with the seed and increment values applied in the order that those rows were originally inserted. Identity numbers are also generated for any new rows that are added. You cannot modify an existing table column to add the IDENTITY property.

Identity columns and their values are managed at the database level and are not controlled by any applications built in Dexterity.

Can I build reports using the DEX_ROW_ID column?

While there is nothing preventing report developers from using the DEX_ROW_ID column, it is strongly recommended to avoid using the values as a method to uniquely identify any given piece of information.

Tables with frequent delete operations will generate gaps between DEX_ROW_ID column values. Deleted DEX_ROW_ID values are not reused.

In addition, during a Dynamics GP upgrade, if a table upgrade changes, the upgrade routines will host the data in a temporary table, the original table will be dropped and recreated, and in the process the DEX_ROW_ID column will be re-seeded. When data is brought back into the upgraded table, the rows will be assigned a new DEX_ROW_ID value as inserted.

Summary

The DEX_ROW_ID column is only useful to Dexterity applications as it provides a way to manage active and passive locking of records, but should no be relied upon for report development, or external processes.

Other Resources

Dynamics GP Blogster - Know thy common table operations with Dexterity - Part I.
Microsoft Developers Network - SQL Server Books Online

Until next post!

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

Tuesday, December 9, 2008

New article on MSDynamicsWorld.com: Frank Hamelly on "Loving your purchasing accrual account"


With this suggestive title, fellow MVP Frank Hamelly explains in great detail, over at MSDynamicsWorld.com, the concept of purchasing accruals and how to take the pain out of reconciling the purchasing accrual account.


Not being an accountant myself, I had to read through this article as it truly details the steps needed to get to the bottom of it all. I would say, you are nuts not to go read Frank's article before trying a reconciliation on your own.

DISCLAIMER: I am a member of the MSDynamicsWorld.com Editorial Review Board and a regular Dynamics GP columnist... now go on and read!

Until next post!

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

Monday, December 8, 2008

David Musgrave on Setting Tab Sequence

The TAB key is often used to navigate sequentially through multiple controls on a Dexterity window. The order in which you move through the controls depends on their Tab Sequence Index value, not their position on the window. In this article, David provides you with the method and techniques used to set up a required tab sequence on a window. Remember that this is not only a "Dexterity thing" and that it is required when adding fields or changing how you jump from field to field in Modifier.

Until next post!

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

Mark Polino on Reconciling ACH Batches

You outta know you are doing something well when fellow MVP Mark Polino references you in one of his Weekly Dynamic articles. This time Mark speaks about a method I devised to reconcile ACH batches in a newsgroup exchange with a Dynamics GP user.

Thanks Mark, truly appreciate it!



Until next post!

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

All about SOP distributions

Microsoft MVP Victoria Yudin seems to be everywhere in these days. If you have ever wondered how GP calculates all these pesky SOP invoice accounting distributions, wonder no more! Victoria decodes in a very friendly and illustrative manner how GP decides the fate of the accounting distributions to be posted to General Ledger when an invoice is posted in the Sales Order Processing module.


If I may add one more thing about the SOP distribution types, this field is used to categorize the distributions for the purpose of verifying the distributions on a document and can be framed in one of these 23 types:


1 = SALES
2 = RECV
3 = CASH
4 = TAKEN
5 = AVAIL
6 = TRADE
7 = FREIGHT
8 = MISC
9 = TAXES
10 = MARK
11 = COMMEXP
12 = COMMPAY
13 = OTHER
14 = COGS
15 = INV
16 = RETURN
17 = IN USE
18 = IN SERVICE
19 = DAMAGED
20 = UNIT
21 = DEPOSITS
22 = ROUND
23 = REBATE


The corresponding numeric value is stored in the SOP_Distribution_WORK_HIST table (SOP10102).

Until next post!

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

Smartlist Builder Security for SQL Server Views

On a recent Microsoft Dynamics GP newsgroup post, a user created a Smartlist Builder based on a SQL Server view. After granting permissions to the DYNGRP database role, the Smartlist was still not accessible to the end user. After testing a few things, fellow MVP Victoria Yudin was able to resolve the problem by assigning a specific Smarlist Builder security task to the user's Dynamics GP role.

Recreating the issue

The following steps are needed to recreate the issue.

1) Run the following statement against your SQL Server company database. This simple view will show all contact information on sales documents that have been purchased from suppliers.




CREATE VIEW dbo.vwPurchasedSOPDocuments AS
SELECT a.sopnumbe, a.docdate, a.custnmbr, a.custname, a.cntcprsn, c.ponumber, c.vendorid, c.vendname, c.docdate as podate, c.contact
FROM SOP10100 a INNER JOIN SOP60100 b on (a.SOPNUMBE = b.SOPNUMBE) and (a.SOPTYPE = b.SOPTYPE)
INNER JOIN POP10100 c on b.PONUMBER = c.PONUMBER
GO

GRANT SELECT ON dbo.vwPurchasedSOPDocuments TO DYNGRP
GO



2) Login to GP as SQL Server's system administrative user (sa) and open Smartlist Builder SQL Table Security window and grant access to the created object. Choose the Views radio button to display the recently created view. Click OK to continue.


















3) Setup a new Smartlist object as follows:

















NOTE: Use the SOPNUMBE and PONUMBER fields as keys to the SmartList.

4) Save and open Smartlist to build the newly created object.











5) For this demonstration, grant access to the newly created Smartlist by assigning security to the DEFAULTUSER security task. Go to Microsoft Dynamics GP > Tools > Setup > System > Security Tasks.


















6)Switch users. Select a user with DEFAULTUSER security task and log on as that user into GP. For this sample, I will be using LESSONUSER1 in Fabrikam.

7) Open Smartlist and try to launch the Smarlist previously created. The user (LESSONUSER1) will obtain the following error:
















While all the above procedures to setup security priviledges to the newly created Smartlist are standard and would seem accurate, there is one more step that was omitted throughout. For an end user to have access to Smartlist Builder objects created from views, it is necessary to grant one more access to the corresponding security task, as follows:

1) Log into Dynamics GP as SQL Server systems administrator (sa).

2) Open the Security Task Setup window and choose the task ID. In the case of this example I selected DEFAULTUSER. Under Product, choose SmartList Builder; under Type, choose SmartList Builder Permissions; and under Series, choose SmartList Builder. Mark View SmartLists with SQL Tables in the Access List pane. Click Save to continue.


















3) Log on as the user in question (LESSONUSER1), then attempt to open to launch the newly created SmartList. At this stage, the security issue should have been resolved and the user is now able to view the Smartlist.

Good catch Victoria!

Until next post!

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

Tuesday, December 2, 2008

Welcome Frank Hamelly, MVP to the blogosphere!


Well, I believe my friend and fellow MVP, Frank Hamelly, has succumbed to the daily pressures I inflict on him and have turned out to the blogosphere as many of us have done throughout this year alone. Not only has he setup tent over at GP2TheMax, but he has written his first article on the all too confusing subject of Received not Invoice. Please join me in welcoming Frank, but more importantly, please stop by his new blog, read his articles, and enter a comment (or two, or three, or...) about his work.

One interesting fact about Frank: he is an aviation fanatic with an already astonishing 300 hours of flight. His already familiar picture was taken at the Empire State Aerosciencies Museum in Schenectady, NY.

Until next post!

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

Monday, December 1, 2008

Convergence 2009 registrations now open



Convergence 2009 registrations are now open. Come visit the Crescent City and spend a great time meeting peers, partners, and others who share your ever growing interest in the Microsoft Dynamics GP application and technologies.

For your convenience, I have reposted the links that will allow you to register according to your registration needs:



Sign up before 11:59pm PST on Jan. 14, 2009 and enjoy a $300 early registration discount.

Until next post!

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

SQL-mania series at Developing for Dynamics GP



David Musgrave unleashes his toolbox of useful SQL scripts in his traditional Developing for Dynamics GP blog. David will address this topic in a series of articles that you must be sure to check out.




The articles will be released in the following order:

Mon, 12/01/2008: spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database

Wed, 12/03/2008: Releasing Stuck Batches and Transactions without exiting all Users

Fri, 12/05/2008: Identifying Duplicate Transactions

The links will be available on or after the posting date according to your time zone. While you enjoy these scripts, be sure to check my own collection:


These are just a few I have written over the years and that you may certainly find useful throughout your daily operations.

Until next post!

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