The Dynamics GP Blogster

After a long writing hiatus, MVP Frank Hamelly on his gp2themax blog explains how to setup the often overlooked Accounts Rollup feature which he describes as "not as tasty as fruit rollups, but equally satisfying". Frank also explains how to use Microsoft Dynamics GP's Navigation Pane to organize SmartLists for the end-users. His narrative is based on a requirement posed by the client at an actual project we both had the opportunity to work and collaborate on. Please be sure to read Frank's articles as you will find a lot of interesting things from his personal experience. Until next post! MG.- Mariano Gomez, MVP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Unless you have been working with Dynamics GP from the days of Ctree and Btrieve, it is quite difficult to comprehend why Dynamics GP seems to behave (as in act up) in certain ways on SQL Server -- how would I say this... not quite like your other Windows applications that run on SQL Server. To understand some of these behavioral issues, David Musgrave brings a two-part series on Understanding how Microsoft Dynamics GP works with Microsoft SQL Server -- if it was my article I would have labeled it Dynamics GP technological idiosyncrasies , but then again, I did not write it. :-) Part 1 of Understanding how Microsoft Dynamics GP works with Microsoft SQL Server Part 2 of Understanding how Microsoft Dynamics GP works with Microsoft SQL Server Ever wonder why the cryptic table names and columns? A lot of it has to do with the origins of the application and the multiple operating systems and ISAM platforms supported back in the 80's and 90's. Know your Microsoft Dynamics GP histor...

I recently came across a question as to how to move data across Dynamics GP companies. It then became almost an instant challenge to find all the possible answers to this question and while I don't claim to have them all, I will point out as many methods (and resources) I could think of. If you have used other methods I would certainly value your input to this post. So here we go: Method 1. SQL Server Backup/Restore . This method assumes you will be creating an exact copy of the production data for either test purposes or as a simple fall back method for recovery purposes. See KB article 871973 for more information on how to create a mirrored copy of your GP data. Method 2. Support Debugging Tool You can use Support Debugging Tool's XML Export and XML Import features to select specific tables to transfer. If transfering master tables, KB article 872709 lists the tables you will need to select for the transfer. Method 3. Integration Manager Integration Manager is still one of ...

The purpose of this article is to describe how to identify your current Microsoft SQL Server version number and the corresponding product or service pack level. It also describes how to identify the specific edition of SQL Server you are using in your environment. How to determine which version of SQL Server 2008 is running To determine which version of Microsoft SQL Server 2008 is running, connect to SQL Server 2008 by using SQL Server Management Studio, and then run the following Transact-SQL statement. SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition') The following results are returned: The product version (for example, 10.0.1600.22) The product level (for example, RTM) The edition (for example, Enterprise) For example, the results resemble the following. 10.0.1600.22 RTM Enterprise Edition The following table lists the product version and file version (Sqlservr.exe) numbers for SQL Server 2008: ...

It seems that nowadays we can add security to pretty much every aspect of the Microsoft Dynamics GP application, but when it comes to VBA projects things are not too clear for many developers and Dynamics GP systems administrators. Follow these simple steps to secure your VBA project. 1. Open the Visual Basic Editor. Go to Microsoft Dynamics GP > Tools > Customize > Visual Basic Editor, or press ALT+F11 from your keyboard. 2. Open the project's Properties window. Right-click on the project within Project Explorer and select the project Properties option. You can also access the project Properties window by highlighting the project and using the Tools menu. 3. Click on the Protection tab, then click on the Lock project for viewing checkmark. 4. Enter a password to prevent unauthorized access, then confirm the password entered. 5. Press Ok to apply the settings. 6. Save your VBA project and exit Dynamics GP. When you access the application next time and attempt to access y...

How many times have you wished you could save and print a sales order, a fullfillment order, or an invoice document, with their corresponding packing slips and picking tickets all in one step? The SOP Quick Print feature offers just that! Written by my friend David Musgrave , and a standard part of Microsoft Dynamics GP, it's one of the most overlooked (or perhaps, unknown) yet, one of the most requested options by users working with SOP. David says " Just set it up with the Keep Current Document Displayed option unchecked.Then instead of saving and then printing. Just use Ctrl-Q to quick Print.That will then save the document and print it.That's what I wrote the feature for. " To access the setup window, click on the Options menu on the Sales Transaction Entry screen, then select Quick Print Setup. You can configure additional options even for packing slips and picking tickets, along with report output settings. You can also choose to let the document remain on ...

Well, everyday I learn something new. My friend and fellow MVP Victoria Yudin had been wondering what could be the possible values stored in the PSTGSTUS ('Posting Status') column of the tables dbo.SOP10100 (technical name: SOP_HDR_WORK ) and dbo.SOP30200 (technical name: SOP_HDR_HIST ) statuses as a result of a posting operation in Sales Order Processing. In fact, just recently on the Dynamics GP Newsgroup, someone reported seing a status 508 in the SOP30200 table. The complete list was provided by a Microsoft support engineer on the Partners forum -- take note as they are not documented in the SDK. Transcript All transactions in SOP10100 that have not been posted yet should have a PSTGSTUS value of 0 (unposted). This value of this field will change to 2 (posted) when posted and the record will move to SOP30200. With this in mind, all transactions in SOP10100 should have PSTGSTUS value of 0 and all transactions in SOP30200 should have PSTGSTUS value of 2 (posted). Any oth...

Business Situation A few days aback I addressed an issue where the user needed to set the 1099 Box on the vendor import with Integration Manager . To my surprise, while there is a field to map the 1099 Type, there is no 1099 Box field in the Options collection in the Vendor import in IM. To make matters a bit more interesting, the user needed to have the 1099 Box filled based on some settings in the source system. Solution The 1099 Box is not accessible via Integration Manager , however we can create a small SQL Server stored procedure in the company database that will accept the vendor ID and the 1099 Box as parameters. The stored procedure in turn will update the vendor record with the value submitted. dbo.uspUpdateVendor1099Box -- Created by Mariano Gomez, MVP IF OBJECT_ID ( N'dbo.uspUpdateVendor1099Box', N'P' ) IS NOT NULL DROP PROCEDURE uspUpdateVendor1099Box; GO CREATE PROCEDURE dbo.uspUpdateVendor1099Box @VENDORID CHAR(21) = NULL ,@TEN99BOXNUMBER SMALLINT = NULL...

Global English Partner Online Technical Communities upgraded to the forum platform since Apr 27, 2009, at 9:00am PST. I have to say I have been quite excited lately working on the new Dynamics GP (Partners) forum platform. Forums defer from Newsgroups in that these are moderated by Microsoft employees and are exclusive to Microsoft partners who are Registered members, Certified members, or Gold Certified members -- this is, they are not public. The following are links to the different partner forums: Dynamics GP Dynamics AX Dynamics NAV Dynamics SL Dynamics RMS Dynamics Sure Step Until next post! MG.- Mariano Gomez, MVP Maximum Global Business, LLC http://www.maximumglobalbusiness.com/

Folks, this one can rather come across as a brain teaser or a catchy certification question, but don't be surprised by how often I get asked during a demo and how often I -- and many others -- have automatically provided the unequivocal answer "Unlimited!". The question comes in various flavors, for example: How many distributions can I add to a journal entry? How many line items can I add to a Sales document? How many line items can I add on a Purchase Order? Unlimited? The truth is, there is a limit to everything, whether the contraints are imposed by physical storage capacity or by a data type definition, the fact is, it's nonetheless a limit. So let's take a look. The Line Sequence Number field in Dynamics GP is generally defined as an integer value. According to Microsoft SQL Server Books Online , an integer requires 4 bytes of storage, which means it can any number between -2^31 and 2^31 or -2,147,483,647 and 2,147,483,647. Now consider this, the first line...

Not too long ago, I answered a pretty interesting question on the Dynamics GP newsgroup. The user's company images signed delivery tickets and store these as PDF documents on a shared location on their network. The PDF document is named after the corresponding invoice number in Dynamics GP, for example, if the invoice number is INV010001, the corresponding image of the delivery ticket would be stored as INV010001.PDF The user wanted to know if it was possible to create a smartlist to show invoices without a scanned PDF image of the delivery ticket. Solution When you think of this problem from the eyes of a user, it seems almost impossible to write a SmartList that would produce the results wanted by the user. But when you breakdown the problem, the real issue is, how do we create a SQL Server view that is able to show whether a file exists for a specific Dynamics GP invoice document. The solution is to create a SQL Server user-defined function (UDF) that is able to return whether a...

In Part 1 of this series you saw how to leverage a Common Table Expression (CTE) query with the use of the T-SQL OVER clause to determine the ordinal position of a line item displayed in a scrolling window. The ordinality is calculated based on the Line Sequence Number stored by Dynamics GP, but frees the developer of having to use complex formulas -- thay may or may not always work -- to determine ordinality of an item within the set. Continuing with our series, we will now explore how to use these scripts with VBA and Dexterity to retrieve the ordinal position value to use within any customization. First, we will convert one of our scripts to a SQL Server stored procedure that can accept, say a document number, document type, and an item number and will then return the ordinal value. Let's look at one such script from Part 1: SOPLineItemSequenceWorkHistory.SQL -- Created by Mariano Gomez, MVP WITH SOPCTE (SOPTYPE, SOPNUMBE, ITEMNMBR, LNITMSEQ) AS ( SELECT SOPTYPE, SOPNUMBE, ...

One of the questions that most often come up in newsgroups and at client sites is, "how can I find the line number of an item on a * document *?", you can replace the word *document* for anything from a sales order, sales invoice all the way to a purchase order, purchase receipt, or invoicing invoice -- and pretty much any other thing you can think of. The bottom line is, while Dynamics GP creates a line sequence number for every item entered in a scrolling window -- click here to see my past article on line sequence numbers and scrolling windows -- it is not very good at aiding users and/or developers in tracking the true ordinal value (1, 2, 3,.., n) of an item within the set of items on a document. So, I decided to give you a push by creating several T-SQL scripts that will help you identify these ordinal values. You can then take these same scripts and convert it into a SQL Server UDFs or stored procedures and call them from VBA or Dexterity if needed. I will also provid...