The Dynamics GP Blogster

This is a very common question among Dynamics GP users. In fact, it is a very common situation that can puzzle even seasoned consultants. So here is the mystery debunked. Every Dynamics GP next document number in every module is generally gathered from a setup table. Usually, this table column is called Next Number. For example, GL will have a Next Journal Number; POP will have a Next PO Number and so on, depending on the module and the transaction. To focus on one thing and one thing only, I will use POP and the next purchase order document number. In multi-user PO entry environments, for example, if user A is working on PO 10, user B is working PO 11, and user C is working on PO 12, but user B cancels, void, or delete their PO, when user A attempts to enter another PO, they will still end up with 13. It will appear to user A that the PO numbers are being skipped randomly. But this is clearly not the case we are discussing here, and rather the normal application's behavior. In pri...

The Microsoft MVP Award Program has featured Dynamics GP MVPs Mark Polino over at DynamicAccounting and Mariano Gomez (that would be me!) on their blog page for their articles published on MSDynamicsWorld.com . On a personal note, this is truly encouraging and gratifying -- and I take the attribute to speak for Mark as well -- since Mark and I work very hard each day to bring you some of the best content you will ever find on all things Dynamics GP, that's our promise to you! However, doing it while wearing the Microsoft Most Valuable Professional (MVP) badge is even more special, because you can rest assured of the quality of the information you are receiving. My thanks to the David Gumpert and Adam Berezin at MSDynamicsWorld for allowing us to publish on their site, but more so, thanks to all of you readers who made it possible in the first place. Until next post! MG.- Mariano Gomez, MVP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Problem You attempt to install a Microsoft Dynamics GP add-on product and you receive one of the following messages: "The following SQL statement produced an error: sql_statement "; or "The following SQL instruction produced an error: sql_statement " Followed by: "ERROR [Microsoft][SQL Native Client][SQL Server]Invalid object name: object_name " or "ERROR [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name: object_name " Cause This error is due to an incorrect version number in your DYNAMICS.dbo.DB_Upgrade table for the add-on in question. Possibly, the add-on was installed and removed a few versions back and all SQL Server related objects dropped. When Dynamics Utilities attempts to upgrade the objects for the inexisting add-on, to your current product versions you will receive the errors indicated above. Solution If this is a new install of the add-on and it has not been used before, the best course of action would be to alter ...

This article will focus on techniques that can save your life when developing in Dexterity and especially when integrating with third party applications. All too often, the only methods discussed for accessing third party data are the use of triggers and/or pass-through SQL in Dexterity. While these methods are very efficient, they rely heavily on the developer's ability to implement error handling at the application client side. Also, the amount of code needed perform these actions (to retrieve/save data) can be overwhealming at times. One of my preferred methods is the use of SQL Server stored procedures. Stored procedure calls allow developers to separate things like data entry validation from actual business logic that deals with data saving and retrieval. It's fairly simple to fix a stored procedure that's already in a production environment versus the Dexterity code as a whole, since the latter involves redeploying chunk files. We all know too well how cumbersome this...

Your Developing for Dynamics GP weekly summary is here! I have received possitive feedback from many of you on this weekly review because it presents some of the best articles available on the web on Dynamics GP development and integration techniques. This week's summary is no different! So let's get straight into it. Finding out how to call an existing report . In this article, David Musgrave reviews two techniques for calling and executing existing Dynamics GP reports from custom code. While I am not able to speak for Visual Studio Tools, these techniques are very useful when these calls are performed from Dexterity Sanscript. As an additional resource to his article, you may want to read my articles on Support Debugging Tool and DEX.INI . Sending Emails with Collaboration Data Objects (CDO) and Dexterity . It's always good when any of my articles get an entry on Developing for Dynamics GP. David takes a look at the technique I used on one of my projects to submit email...

For all of you who follow me on MSDynamicsWorld.com, I have released a new article on Implementation Project Team blunders . Let's face it! Most of us Dynamics GP consultants become heroes with our customers by remediating failed implementations. This is how I progressed from being a developer to a consultant. In this article, I categorize project teams based on some companies' views of how a project should be run. Until next post! MG.- Mariano Gomez, MIS, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

One of the valuable tools that Microsoft has released is SmartList Builder for Dynamics GP. The concept is very simple: if the standard out-of-the-box Smartlist does not cut it for you, well, you build your own! Furthermore, SmartList Builder does allow for some nifty stuff, such as working with Microsoft SQL Server views and (custom) tables. Custom tables are not the focus of this article. If you can't get your smartlist done with all the SmartList Builder query building capabilities, then create your own views of the data in Microsoft SQL Server, grant access to them in SQL Server, then, grant security to them in SLB, and use them. But what if after deploying your smartlists, users request more columns of information that you did not include in your original SQL Server views, and now you need to make those changes and affect your previous work of art? Solution The solution may be easier than you think, but please do not delete your existing SmartList Builder and recreate all th...

Not even I can put out so many articles at once! Fellow MVP, Mark Polino has innundated the MSDynamicsWorld news outlet with two very compeling articles: Taming the SmartList Sprawl . This is got to be the worst nightmare for any Dynamics GP systems administrator. Hundreds, sometimes thousands of custom smartlists that no one seems to know who created them, what they do (well this one can be obvious), how aften they are needed, etc. Mark has a few great tips on how to avoid the sprawl. "Sometimes SmartList can be too much of a good thing" Top 10 reasons to attend Convergence . If you are still trying to convince your boss as to why you need to go to Convergence, or if you are the boss and you are still trying to decide who you want to tag along with you, this article is for you. By the way, see you there. "It's all about ROI" Hope you enjoy these articles as much as I did, but make sure you leave your comments for Mark. Until next post! MG.- Mariano Gomez, MVP M...

Psssst! If you haven't registered for Convergence 2009 , here is the deal: let your boss know (or if you are the boss) tomorrow is the last day for early registration -- extended from it's original due date of 11:59pm PST on Jan. 14, 2009. Early Registration discount date has been extended to January 21, 2009. Save $300 by registering early, secure your favorite hotels while they are still available and take advantage of better airline rates. Here are the links again: Register as an Existing Customer or Partner Register as a Prospect Customer or Partner Register a Group Register with an RSVP code Do I need to say it's in New Orleans? Related Articles - Convergence 2009 registrations now open - click here Until next post! MG.- Mariano Gomez, MIS, MCP, PMP, MVP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

One of the things I always tell other Microsoft Dynamics GP developers is not to discard the power of Dexterity. Not because there is .NET and Visual Studio Tools it means that you should abandon Dexterity as a development option (while I confess, I am in the process of increasing my knowledge of C# and so on). Business Situation A common request is the ability to submit emails from within your Dexterity-based application. However, emails are not always straight forward. In environments where Dynamics GP resides on a Citrix farm or Terminal Server, companies are reluctant to install Microsoft Office and/or setup email profiles for everyone. After all, that's what the Exchange Server is for. So, how can you get email messages sent from your application without SMTP, Exchange, or Outlook? Background Collaboration Data Objects (CDO) has been around for quite sometime and is Microsoft's technology for building messaging or collaboration applications or adding these capabilities to...

After a long absence, my friend David Musgrave is slowly regaining his writing pulse. Please check out some of his cool articles this week, but first and foremost let him know what you think about them. I always encourage everyone to test and play with some of the code we provide in these articles. They are a good way to get acquainted with Microsoft Dynamics GP functionality. So let's get on with the articles: 1. Microsoft Dynamics GP 10.0 Service Packs, Hotfixes and Payroll Compliance/Year End Updates May Damage the Modified Reports and Forms . After a few of these errors circulating on the newsgroups, it has been confirmed by Microsoft development team. The payroll year end update will in fact damage your modified forms and reports. I have a theory on this one: since the YE update is also inclusive of service pack 3, it will be necessary to take all the precautions layed out when applying a service pack, that is, export ALL your customizations to a package file BEFORE applying ...

"Avoid the temptation to take shortcuts" For those of you who follow me on MSDynamicsWorld, after a small writing sabatical I am back with a new article on upgrading to Dynamics GP 10.0 . My key recommendations: work from the FP1 DVD , test several times, make sure you know your upgrade path , take advantage of the latest Microsoft OS and database technology, but foremost, work with your partner! Until next post! MG.- Mariano Gomez, MIS, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

"Dynamics GP experience becomes a big plus when job hunting" Fellow MVP Mark Polino, addresses, in his most recent MSDynamicsWorld article , something employers rarely sit down to consider: your Dynamics GP expertise! If there is one thing that will differentiate you among your peers during these tough times, when pink slips seem to be at the order of the day, that would have to be your ability to take a business issue and leverage your knowledge of Dynamics GP to solve a problem. On the other hand, if your layoff is imminent, make sure you brush up your resume and highlight your Dynamics GP skills first and foremost. Believe it or not, it may well be the key to your next career move. Until next post! MG.- Mariano Gomez, MVP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

While I am a big SQL Server development fan and get a lot of support issues taken care of this way, it amazes me that many customers and partners are still not taking advantage of the Microsoft Dynamics GP Automated Solutions. This may be due to lack of understanding or simply a lack of knowledge of the existance of these tools. What are Automated Solutions? In Microsoft's own terms " Automated Solutions are used to simplify certain tasks which would otherwise require knowledge of the SQL Query Language and access to the SQL Query Tools or Support Administrator Console ". In my own terms, Automated Solutions takes away the amount of SQL and Dynamics GP architecture you would need to know to resolve some very common everyday issues. The best part of it all, they are bundled with your Microsoft Dynamics GP enhancement plan and accessible via CustomerSource or PartnerSource. *Click on image to enlarge Automated Solutions exists for releases of Dynamics GP going back to 7.5 a...

Business Situation A few days ago, a user was facing an issue in which apparently, their tax history was not being recorded or was missing for a good number of transactions created in Sales Order Processing. In addition, the user wanted to use Dynamics GP's built-in tax reports as they did exactly what she needed. Of course, the question came in: How can I rebuild my Tax History? Solution One thing I love about Dynamics GP is the fact that information created in the subsidiary modules, flow to other parts of the application tables when GP is instructed to do so. This instruction comes via the configuration options in the system. In this particular case, when Sales Order Processing transactions are created and taxes are calculated for those documents, the tax detail information is stored in the Sales Tax Work and History table (dbo.SOP10105). When invoices and returns are posted, and if history is being maintained for the tax details on these documents, the resulting tax records ar...

One very common request by users and organizations using Microsoft Dynamics GP and Integration Manager is the ability to schedule an integration to run overnight. For small companies this can become a challenge, more so, when they lack the technical resources on staff or the basics on the Dynamics GP architecture. The Basics Any of the approaches used to schedule the integration will start from the same point: creating a login macro. The steps to create a login macro are widely documented in the Integration Manager Users Guide printable manual, in Chapter 17, under the section Recording the Login Macro , hence will not be the focus of this article. However, here is a sample of what the macro file (conveniently named LOGIN.MAC) should look like. LOGIN.MAC macro # Created by Mariano Gomez, MVP # Sample Dynamics GP Login Macro. # This sample code is provided "AS IS" and conferes no rights express or implied # # FILE: LOGIN.MAC # # DEXVERSION=10.0.313.0 2 2 Logging file 'IMM...

Fellow MVP Frank Hamelly addresses a concrete day to day business issue: how to enter prepayments for vendors in GP. While it's very simple to record prepayments for customers in the Sales series, it is not always as straight forward in the Purchasing series. Take a look at Frank's article and let him know what you think. Until next post! MG.- Mariano Gomez, MVP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Stop making more work for yourself! Fellow MVP Mark Polino opens the year at MSDynamicsWorld.com with an article I believe you should read very carefully. Since we all make New Year's resolutions, it should come as no surprise that these resolutions should extend to our use of business solutions too. Mark's 5 resolutions are: 1) take SmartList for what it is, not for an Excel file generator, 2) get over the darn palette windows and menus, 3) how many GP clients do you need to install to prove you know GP? Consider Terminal Server instead, 4) Stop forcing your receivables modules to do project accounting, get the modules you actually need, and go get some training for God's sake! Until next post! MG.- Mariano Gomez, MVP, MCP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Just recently I had been helping my friend Cal at EMC to resolve an issue with the Professional Services Tools Library (PSTL) Item Combiner utility. In a previous life, his company had installed the Manufacturing series and decided not to use it. They upgraded to Dynamics GP 10.0 and decided they wanted to take advantage of PSTL's Item Combiner to do some cleanup work on their product master. Not so quick! The Item Combiner failed with the message: '[Microsoft][SQL Native Client][SQL Server]Invalid object name 'MOP1014' We began an extensive troubleshooting process to find the culprit of the problem with no success, that is, until the problem was narrowed down to the following question: How to find a text within a SQL Server trigger, stored procedure, or UDF module . The following T-SQL query accomplishes just that: -- run against DYNAMICS and company databases SELECT OBJECT_NAME([object_id]) FROM sys.sql_modules WHERE [definition] LIKE '%MOP1024%'; It turns ou...

Microsoft has made Round 1 of the 2009 Payroll Tax Updates now available. Round 1 updates a number of state and federal tax tables, per the document: - California - Maine - Michigan - Minnesota - New Mexico - Oklahoma - Fica/Social Security Wage Limit - Personal Exemption amounts - Earned Income Credit In past days, I reported the EIC value being incorrect for 2009 and apparently it has been taken care of in Round 1. Tax updates can be applied/re-applied by following these simple instructions: Dynamics GP 10 Go to Microsoft Dynamics GP > Maintenance > U.S. Payroll Updates > Check for Tax Updates Dynamics GP 9.0 Go to Help > U.S. Payroll Updates > Check for Tax Updates Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com

In the previous installment , you saw how to make changes to the Requested Ship Date roll down to the line items and linked purchase order documents via a SQL Server trigger. This article will now show a VBA method combined with a SQL Server stored procedure call. The advantage of using VBA is to allow more interaction with the end-user and control the flow a bit more. The following steps will get us there: 1) Add the Sales Transaction Entry window to VBA. Open the Sales Transaction Entry window and press CTRL + F11 on your keyboard. 2) Add the Document Type drop-down list, Document Number, and Customer ID fields to VBA. Press Shift + F11 on your keyboard to activate the visual cue. Once active, click on both fields. Press Shift + F11 to deactivate the visual cue. 3) Add the Sales Date Entry window to VBA. Click on the expansion button next to the Date field to open the Sales Date Entry window and press CTRL + F11 on your keyboard. 4) Add the Requested Ship Date field...

If you have applied 2009 Payroll Tax Update to your Microsoft Dynamics GP system, you will need to correct the Earned Income Credit amount in the UPR41300 tax table from its old value of $1,750 for 2008, to $1,826 for 2009. However, the amounts seem to be correct for the married and single filing statuses in the UPR41302 tax table. To fix this issue, run the following script against your DYNAMICS database: -- Run againt your Dynamics database USE DYNAMICS GO UPDATE UPR41300 SET MXEICPMT = 1826.00 WHERE TAXCODE = 'EIC' Thanks to Bob Harrison at The Lighthouse Neurological Rehabilitation Center ( http://lighthouserehab.com/ ) for this pointer . Related Articles 2009 Payroll Tax Update for Microsoft Dynamics GP 8.0 - Click here 2008 Year End Update and Microsoft Dynamics GP 8.0 - Click here Until next post! MG.- Mariano Gomez, MVP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Kudos to Ms. Victoria Yudin for her 5th straight year of being selected as one of Microsoft's Most Valuable Professionals for the Dynamics GP product line. It's safe to say that the Microsoft Dynamics Community won't be what it is today without the contributions of Ms. Yudin, who as of the date of this post has racked up 2933 responses in the microsoft.public.greatplains newsgroup. Until next post! MG.- Mariano Gomez, MVP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/