Posts

Showing posts from July, 2008

How to launch a URL from VBA in Microsoft Dynamics GP

Image
So you have created this great customization and part of it is to launch a URL and pass in some parameters via http. For example, a customization on the customer master that will open the customer's invoice entry portal where you can type in the invoice information. The following code can be placed in the (General) section of your VBA customization and be called from any other event, for example the BeforeUserChanged event. Public Function OpenBrowser(ByVal URL As String) As Boolean Dim res As Long ' it is mandatory that the URL is prefixed with http:// or https:// If InStr(1, URL, "http", vbTextCompare) <> 1 Then URL = "http://" & URL End If result = ShellExecute(0, "open", URL, vbNullString, vbNullString, vbNormalFocus) OpenBrowser = (result > 32) End Function You can call the ShellExecute() Windows API function from a Visual Basic for Applications in Microsoft Dynamics GP to start another program under Microsoft Windows. Use Shel

Microsoft Dynamics GP Support Debugging Tool

You may have heard about the Support Debugging Tool for Microsoft Dynamics GP previewed a few months ago at the Microsoft Dynamics GP Technical Airlift 2008 in May with good reception among the attending crowd. What is the Support Debugging Tool? The Support Debugging Tool is a Dexterity application which contains a collection of utilities and tools to make the task of supporting, developing and debugging Microsoft Dynamics GP easier and faster. David Musgrave, author of the tool says " I originally developed the tool when our Microsoft Dynamics Support in the Asia Pacific region was assisting a customer with a situation that produced invalid data in a table, but no cause could be replicated. Looking at the customer’s data it was verified that there was an incorrect value in the table. No one was able to identify when the previously correct value in the table was being changed to the incorrect value. " Using Dexterity triggers and the ability to programmatically control De

Microsoft Dynamics GP's History... from a Logo Perspective

I am not sure if my friend David Musgrave has too much time on his hands, but one thing is for sure: the guy does have some of the coolest articles around the GP blogosphere. This time, David surprises us with a time progression of Dynamics GP logos . If the product history could be documented in pictures, this would be it. Let's see, I started working with Dynamics GP when the wheat and red sun logo was around back in 1996. Back then I took R3.15 to Latin American Spanish... not how I want to be reminded of my age. Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Create your own taGetNextNumber stored procedure

I have heard this one very often on developers and users newsgroups alike. In past releases of eConnect (8.0 and before) it was not possible to retrieve the next document number for a few existing transactions in the system via eConnect's COM interface, case in point the Purchasing Receipt Transaction. However, the problem is not limited to eConnect. Many developers have tried to retrieve transactions next document numbers via Integration Manager and have faced the same issue. The following sample code segment creates a wrapper around the little known dbo.ivNumber_Inc_Dec stored procedure, present within the company database. create procedure sptaGetNextNumber @poprctnm char(17) output, @errorstate int output as declare @nextrctnm char(17) select @poprctnm = poprctnm, @nextrctnm = poprctnm from pop40100 -- call Inc_Dec procedure exec @errorstate = dbo.ivNumber_Inc_Dec 1, @nextrctnm output, @errorstate output if @errorstate = 0 update pop40100 set poprctnm = @newrctnm return

Microsoft Dynamics GP 10.0 Service Pack 2 Login Issue Fixed

Mark has interesting information posted on his blog on a security fix addressed in Microsoft Dynamics GP 10.0 Service Pack 2 (10.00.1061). The original issue had to do with turning on password control with password expiration in GP, which is in turn address by Active Directory. Go on to read Mark's post on the issue to get the full details. Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com

Cross Dictionary Development with Microsoft Dexterity

Image
When I first started working in 1996 as a Software Development Engineer on the Dynamics GP Spanish release for Latin America project in Colombia for my former company Nucleus S.A. -- a Microsoft regional partner at the time -- I was challenged by a number of puzzling coding issues that seem to have no solution. Our inability to access third party dictionary information interactively with Dynamics GP was the absolutely most disappointing issue we faced since we had great plans for integrating Fixed Assets (back then a Forestar product) with GP for some Latin American localizations needed at the time. Those issues propagated over a period of 2 years, and during the time our only possibilities were database triggers and stored procedures after the fact. I must say that I worked hand to hand with some of the most brilliant people on the International Team at the former Great Plains Software in getting the product off the ground from a development perspective: Tom Irsfeld, Dave Gaboury, Tad

Microsoft Dynamics GP Scrolling Windows and Line Sequence Numbers

Image
I thought I would write about something meaningless that no one really thinks about. However, I realized that this is a compelling topic since it makes for some interesting discussion around the ability to insert line items withinin most transaction scrolling windows. Let’s take some time to understand where Line Sequence Numbers come from. A Line Sequence Number is auto generated by Dynamics GP and uses a seed value of 16,384 as a basis for the first line. Each subsequent line in the scrolling window is incremented by this number, so line number 2 would have a Line Sequence Number value of 32,768, line number 3 would have Line Sequence Number Value of 49,152, and so on. Since most GP transaction entry scrolling windows allow inserting rows in between line items, the newly inserted Line Sequence Number value is calculated as an average between the previous and the next line item sequences, i.e., if you were to insert an item in between row 1 and row 2 in the scrolling window -- keep in

New article on MSDynamicsWorld: Five Tips for Getting Your Microsoft Dynamics GP Implementation Right – The First Time!

Please stop by MSDynamicsWorld.com and read my new article " Five Tips for Getting Your Microsoft Dynamics GP Implementation Right – The First Time ". In this article, I explore the reasons why most implementations go sour and provide some guiding principles for customers (and partners/consultants) to be successful. Implementations are enterprise transformation projects and not just mere software installations. Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com

Modifying reports that use Temp table and Accessing SQL data in Report Writer

David Musgrave demonstrates two Report Writer customization techniques. The first technique shows you how to access SQL data using VBA , which can be thought of as a way of working around those impossible table references, especially between GP and third-party products or viceversa. The second technique discusses how to customize reports that use temporary tables and considerations when attempting to create new relationships to other tables. Enjoy these two Dynamics GP customization jewels and remember that you can take advantage of David's fully downloadable samples. Until next post! MG.- Mariano Gomez, MVP, MCP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Suppressing "Credit Limit" Warning in Sales Order Processing

Image
A few days aback, I was asked how to keep the "This transaction exceeds the customer's credit limit" message box from popping up when entering a Sales Order. At first I thought it was a strange request, since the warning serves its purpose, but then the user explained that they had their own credit management system which takes into consideration a lot more information that GP does not manage beyond the aging buckets and the limit itself. In addition, they already had a customization in place to check the customer's credit limit against their credit system and automatically set the order on hold if limit was exceeded. In summary, the message was an inconvenience since it appeared each time a line item was entered. To resolve this issue, I created a small VBA script that would automatically respond to the modal dialog without the user even noticing a blink on the screen, as follows: 1) Add the Sales Transaction Entry window to Visual Basic. 2) Open the Visual Basic Edi

A First Look at GP's PerformancePoint Connector by Alan Whitehouse

Alan Whitehouse explores GP's intergration to PerformancePoint Server 2007 and shows details of his experience working with the Integration Wizard -- he also provides his honest opinions. As Alan kindly points out, "grab yourself an adult beverage" for this one as there is plenty of information to go through. Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

New Article on MSDynamicsWorld: XBRL and Microsoft Dynamics GP

Please stop by MSDynamicsWorld.com to read my new article addressing the Securities and Exchange Commission's (SEC) initiative to implement filings of financial results for publicly traded companies with XBRL and how two of Dynamics GP's tools can help you accomplish that goal: Analytical Accounting and FRx. Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com

David Musgrave's Conditional Logos Implementation in Report Writer

How many of you have struggled and have ditched the idea of having a logo print on your reports based on say, for example, the company you are currently logged into. David really has a cool 1-2 punch example on how to implement conditional logos in Report Writer. The man is simply a genius and illustrates that sometimes the simplest solutions just happen to be the most powerful. Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

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

A few days aback, my buddy Jim Harris at TurboChef was having an issue with creating a Go To in SLB that would link open credits and return documents displayed in SmartList to the Apply Sales Documents window in GP where he could effectively apply documents that had not been applied to invoices, etc. I thought, how complicated could that be? You open SLB, you setup your SmartList fields then, setup your restrictions for RM Document Type-All equal or greater than 7, and add the Go To by matching the Customer Number field on the window to the Customer Number in the RM Open Table, the Document Type on the window with the RM Document Type-All field in the table, and the Document Number in the window withe the Document Number in the table, a few clicks to close and save, build and... not so quick! At first the Apply Sales Document window filled in the Customer Number and Document Number, but screamed at the Document Type field. I figured, my restriction was wrong, so I went back in and che

David Musgrave's "Developing for Dynamics GP" Blog Moves to New URL

Dear readers, please update your Favorites link as David's blog site has moved to a new URL ( http://blogs.msdn.com/DevelopingForDynamicsGP ). The previous site now displays a link to his new site. Great picture on the new site mate! For those of you not familiar with David, he is the dude on the right. Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

The Saga Continues: Microsoft SQL Server 2008 to be Shipped in August

Image
Microsoft announced on Wednesday, July 9, 2008, at Worldwide Partner Conference that it will be shipping SQL Server 2008 in August with RTM set for sometime during Q3. I had the opportunity to preview SQL Server 2008 last April 29 in the Heroes Happen Here tour through Atlanta, held at the Convention Center Concourse and was very excited with the features and improvements in the product, however, other is got to be the story for customers and ISVs testing the product since last February, who have had to settle for the TCP and RC0 versions. Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Bug: Correcting Journal Performance (or Lack Thereof)!

If you are experiencing slow performance issues while looking up a journal entry for correction, you are not alone! Make sure to stop by Jivtesh's blog site to get an insight on the issue. What I can't understand is why this problem has been dragging on since v9.0, and why the fix has not made it permanently to a service pack. The good news is there is a fix available with KB article 925326 in the form of a SQL script. Until next post! MG.- Mariano Gomez, MIS, MVP, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

To Check or Not to Check: Understanding Landed Cost's Invoice Match and Revalue Inventory Options in Microsoft Dynamics GP

Dynamics GP presents two selection options in the Landed Cost Maintenance screen: Invoice Match and Revalue Inventory. These two options have profound accounting effects in how inventory and purchases are tracked in GP. The following is provided as is and was posted by Marge Swanson, Senior Software Development Engineer at Microsoft in response to a user's question on the Dynamics GP community board, but I felt it was important to rescue as it contains valuable information not found in the manuals or elsewhere: You should determine whether or not to mark the Invoice Match checkbox based on how you want your distributions to be tracked for the Landed Cost. If you do not mark Invoice Match, the distributions will be reversed from the accrued purchases account used on the Shipment – which defaults from the Landed Cost card. If you mark Invoice Match, the distributions will be created with the Purchase Price Variance account on the Landed Cost Maintenance window. This helps you track

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

It appears that SLB cannot format the SQL query required to extract data after adding a few restrictions. Specifically, any query requiring more than one restriction will ommit the AND operator. This has been documented under Problem report 47625: Restrictions don't work if have more than one on a SmartList which is currently scheduled to be resolved in v10 SP3. " If you want it fixed earlier in a hotfix, I would suggest that you log a support case and reference the bug number. The more customers who request the fix, the higher the priority will be ", said David Musgrave, Microsoft Escallation Engineer in a Microsoft Dynamics GP community post . Until next post! MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

How to Automate a PDF File Output Name for a Microsoft Dynamics GP Posting Report

Image
Many of you automating posting jobs have come across an issue with these schedulers: you are only allowed to output reports to the same physical file setup under your posting options. If you attempt to do this via Named Printers, the MDI writer will use a generic and all too cryptic 'NoteXXXX' file name that cannot be associated to anything coming from GP. So, I figured you could probably use a SQL Server job that would run, say 20 minutes before your habitual posting schedule to change the file name. The following example will use the General Ledger Posting Journal report as an example, however, this can be accomplished for any report that need to be output to a file. In this example, it is all too important to keep in mind that the output of all transactions posting is stored in the 'Posting Journal Destinations' table (SY02200) in the company database. 1) Open Dynamics GP posting options. Go to Microsoft Dynamics GP > Tools > Setup > Posting > Posting. Ch

Restricting Access to Metrics on Microsoft Dynamics GP 10 Homepage

When it comes to securing metrics on GP 10's homepage, very little can be found outside of KB articles 918313 and 914898 . But a few of us have decided to put this issue to rest -- I say a few of us because this 'how to' guide could not be possible without the assistance of FlieHigh and Tim Foster of Trudell Medical Limited in London, Ontario, Canada, both regular contributors in the Microsoft Dynamics GP community -- by combining our different approaches on the subject. Lets get started! There are two approaches to lock down the metrics for a particular user in GP. Method 1: T-SQL with SQL Security 1) Open Query Analyzer (SQL 2000) or SQL Server Management Studio (SQL 2005). Execute the following statement against the DYNAMICS database. UPDATE SY08100 set Visible = 0 WHERE(DictID = 0) and (SectionID = 3) and (UserID = ' userid ') The following is a list of Section ID values: 1 -- To Do's 2 -- Outlook 3 -- Metrics 4 -- My Reports 5 -- Quick Links 2) Open Enter

'Important' Fixes to Come in Microsoft's July Patch Cycle

Image
It's this time of the year when Microsoft releases a set of patches for most of its server applications, and according to Redmond Magazine the patches have been deemed 'important'. The patches will address mostly exploit risks in products such as SQL Server (7.0 SP4, 2000 Itanium, and all versions of 2005 SP2), all versions of Windows (Vista, XP, Server 2003, and Server 2008), and Exchange Server. You can read the full article at Redmond Magazine . Keep in mind that applying any of these patches may affect your Dynamics GP system and you need to consult with Microsoft before applying any of them. Until next post! MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

GP_eEnterprise Yahoo! Group Moving to Google Groups

GP_eEnterprise... that tells you how long this group is been around, and for those of us who have seen it grown over the years, moving to our new home in Google Groups ( http://groups.google.com/group/DynamicsGP ) comes with a little bit of sadness and nostalgia, but yet with the excitement of leveraging some of the best tools available on the Internet to continue with our commitment of helping others. The group will begin its activities on July 7, 2008 at 7:00 GMT. During the transition phase, all efforts were made to transfer all postings from the GP_eEnterprise group to DynamicsGP group as a way of maintaining the rich history accumulated over the years. Please visit our new home and register. Until next post! MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com

David Musgrave, "Father Dexterity" Himself!

Ok, not quite! But Microsoft Dexterity and the overall Dexterity development community owes a great deal of progress in the tool and development techniques to David Musgrave. His now famous " Pushing the Limits with Dexterity " series have been a staple to everyone for quite sometime now, but he is well known for fathering products such as Omni Tools and Omni Security. David decided it wasn't enough and has ventured to join the increasing number of bloggers willing to share their knowledge with the rest of the community. You can now find David at his MSDN blog site . Good luck with this new venture. Until next post! MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Automating Buyer ID field entry with logged in User ID in the PO Entry window in Microsoft Dynamics GP

Image
Here is another very common request: how can the Buyer ID field entry be automated with the user ID currently logged into Microsoft Dynamics GP? This is certainly an important control feature in environments where security is key to auditors and systems administrators alike. A simple VBA script can take care of this issue, as follows: 1) Add the Purchase Order Entry screen and the Buyer ID field to Visual Basic 2) Open the Visual Basic Editor 3) Locate Microsoft_Dynamics_GP > Microsoft Dynamics GP Objects 4) Double-click on the PurchaseOrderEntry (Window) object 5) Add the following code in the editor ' Created by Mariano Gomez, MVP ' Code is provided "AS IS". No warranties express or implied ' Private Sub BuyerID_BeforeGotFocus(CancelLogic As Boolean) Dim usrinfObj As UserInfo Dim usrID As String Set usrinfObj = VbaGlobal.UserInfoGet() usrID = usrinfObj.UserID BuyerID = usrID End Sub I know what you are thinking... this script could use some enhancem

Microsoft Excel Spreadsheet as Source for Professional Services Tools Library's Account Modifier/Combiner

This Microsoft Dynamics GP nugget comes courtesy of Victoria Yudin, MVP and owner of Flexible Solutions . Account Modifier/Combiner, a tool part of the PSTL, allows to change previous account numbers into new account numbers or combine existing accounts. The tool can automatically identify which one of these activities you are trying to perform. However, if you have used the tool in the past, you are well familiar with the long waiting times until it finishes with one modification/combination. What if you need to combine or modify hundreds of accounts? Account Modifier/Combiner allows an end-user to setup two columns in a spreadsheet representing the Account Number and the Convert To Account Number fields respectively. The detail steps to import the accounts to be combined or modified are outlined in the Professional Services Tools Library manual, but in summary, once the spreadsheet is created, you can choose to open and validate the accounts prior to any action being carried out by

Bug: Excel Budget Wizard Export/Import issues in Microsoft Dynamics GP 10

If you are an Excel Budget Wizard user, please check out Mark Polino's post on a bug found after exporting the budget to Excel and attempting to reimport the data back into GP. The bug is persistent through Service Pack 2, but a workaround is provided. Until next post! MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Mark Polino's Six Best Practices for Using Microsoft Dynamics GP to Improve Processes and Save Time in Closing the Books

Well, you have to love when darn good consultants also happen to be darn good writers! Please check Mark Polino's article - Six Best Practices for Using Microsoft Dynamics GP to Improve Processes and Save Time in Closing the Books - on MSDynamicsWorld.com. The article explores a series of fast closing principles applied to Microsoft Dynamics GP financial processes that will help controllers and accounting managers realize efficiencies and improve time during accounting book closings. Until next post! MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com