Aaron Berquist Rotating Header Image

Dynamics GP

Creating Custom Desktop Alerts In Dynamics GP

Jul 7th, 2012
by Aaron Berquist.
7 comments
Sample Desktop Alert

First off, full credit goes to David Musgrave for sharing the bulk of this code with me and for providing some great troubleshooting help as I worked to call the code properly from VBA.

Allow me to share some background information. While at Convergence 2012 in Houston this year, I attended a presentation on the Support Debugging Tool by David Musgrave and Mariano Gomez. (Protip: I’ve found the sessions they run to be the most valuable ones at Convergence, by far.)

At the session, David mentioned in passing that the Support Debugging Tool was configured to raise a “Desktop Alert” when certain criteria were met. As soon as he demoed this, I thought – this would be a great way to provide feedback to a user without interrupting their workflow. Up until now, any time I wanted to provide a message to the user (such as “Data Saved”, etc) the only tool I had in my toolbox was a message box which they would have to click on to dismiss.

After emailing David with some questions about how he had implemented this feature, and with a lot of hand-holding from him as well, I had managed to code up a subroutine in VBA (and later, VB.NET) which I could call from a customization to send a Desktop Alert to the user. Here is a sample:

Sample Desktop Alert

The code to do this is actually pretty simple, and uses the (unsupported, undocumented) pass-through Dexterity method to call the Dexterity code which raises a desktop alert:

VBA / VB.NET

Public Sub RaiseDesktopAlert(AlertTitle As String, AlertMessage As String)
        'Author: Aaron Berquist
        'Date: 07/16/2012
        'Purpose: Raise a Dynamics GP "Desktop Alert" message in the bottom right-hand corner of the screen.
        'Inputs: 
        'AlertTitle - The title to be displayed in the Desktop Alert
        'AlertMessage - The message to be displayed in the Desktop Alert
        'Based on sample code provided by David Musgrave
        'http://blogs.msdn.com/b/developingfordynamicsgp/

        Dim CompilerApp As Object
        Dim compilercommand As String
 
        CompilerApp = CreateObject("Dynamics.Application")
        Dim CompilerError As Integer
        Dim CompilerMessage As String = ""
 
        CompilerCommand = "local string IN_Title;" & vbCrLf
        CompilerCommand = CompilerCommand & "local string IN_Message;" & vbCrLf
 
        CompilerCommand = CompilerCommand & "IN_Message = " & Chr(34) & AlertMessage & Chr(34) & ";" & vbCrLf
        CompilerCommand = CompilerCommand & "IN_Title = " & Chr(34) & AlertTitle & Chr(34) & ";" & vbCrLf
        CompilerCommand = CompilerCommand & "call wfDisplayDesktopNotification, IN_Title, IN_Message;"
 
 
 
        'Set the Product ID to 0 (Microsoft Dynamics GP)
        CompilerApp.CurrentProductID = 0
        'Execute SanScript

        CompilerError = CompilerApp.ExecuteSanscript(compilercommand, CompilerMessage)
    End Sub

I hope you find this code useful. I have have good feedback from several users now who appreciate the fact that the Desktop Alert does not interupt their workflow, yet still provides valuable feedback to them about certain actions which have occured inside the system.

Post to Twitter

Populate Rounding Account On Multicurrency Payables Transactions Created Via eConnect

Jul 15th, 2011
by Aaron Berquist.
4 comments

If you create multicurrency payables (PM) transactions using eConnect, and you are passing in the distributions manually (by setting CREATEDIST to 0), you may have encountered the same issue I encountered today.

When the transaction was created, eConnect also (correctly) added in a ROUND distribution, to account for 0.01 cents worth of FX related rounding on the transaction. Unfortunately, eConnect did not populate the distribution with an account. This struck me as strange – if you manually create a voucher in Dynamics GP and rounding is encountered, a ROUND distribution is created, and the account is populated (with the Rounding Difference Account, which is set up under Tools: Setup: Posting: Posting Accounts).

I should also mention – there is a specific order that should be followed when creating a multicurrency payables document – create the distributions first, then create the header record. Details are in VSTools thread here.

To resolve this issue, I entered some code into the taPMTransactionInsertPost procedure to retrieve the account and populate the distribution. The code snippet is below, or you can download it here.

/****** Object:  StoredProcedure [dbo].[taPMTransactionInsertPost]    Script Date: 07/15/2011 09:48:57 ******/
IF EXISTS ( SELECT  *
            FROM    sys.objects
            WHERE   object_id = OBJECT_ID(N'[dbo].[taPMTransactionInsertPost]')
                    AND type IN ( N'P', N'PC' ) )
    DROP PROCEDURE [dbo].[taPMTransactionInsertPost]
GO
 
/****** Object:  StoredProcedure [dbo].[taPMTransactionInsertPost]    Script Date: 07/15/2011 09:48:57 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER OFF
GO
 
CREATE PROCEDURE [dbo].[taPMTransactionInsertPost]
    @I_vBACHNUMB CHAR(15) ,
    @I_vVCHNUMWK CHAR(17) ,
    @I_vVENDORID CHAR(15) ,
    @I_vDOCNUMBR CHAR(20) ,
    @I_vDOCTYPE SMALLINT ,
    @I_vDOCAMNT NUMERIC(19, 5) ,
    @I_vDOCDATE DATETIME ,
    @I_vPSTGDATE DATETIME ,
    @I_vVADCDTRO CHAR(15) ,
    @I_vVADDCDPR CHAR(15) ,
    @I_vPYMTRMID CHAR(20) ,
    @I_vTAXSCHID CHAR(15) ,
    @I_vDUEDATE DATETIME ,
    @I_vDSCDLRAM NUMERIC(19, 5) ,
    @I_vDISCDATE DATETIME ,
    @I_vPRCHAMNT NUMERIC(19, 5) ,
    @I_vCHRGAMNT NUMERIC(19, 5) ,
    @I_vCASHAMNT NUMERIC(19, 5) ,
    @I_vCAMCBKID CHAR(15) ,
    @I_vCDOCNMBR CHAR(20) ,
    @I_vCAMTDATE DATETIME ,
    @I_vCAMPMTNM CHAR(20) ,
    @I_vCHEKAMNT NUMERIC(19, 5) ,
    @I_vCHAMCBID CHAR(15) ,
    @I_vCHEKDATE DATETIME ,
    @I_vCAMPYNBR CHAR(20) ,
    @I_vCRCRDAMT NUMERIC(19, 5) ,
    @I_vCCAMPYNM CHAR(20) ,
    @I_vCHEKNMBR CHAR(20) ,
    @I_vCARDNAME CHAR(15) ,
    @I_vCCRCTNUM CHAR(20) ,
    @I_vCRCARDDT DATETIME ,
    @I_vCHEKBKID CHAR(15) ,
    @I_vTRXDSCRN CHAR(30) ,
    @I_vTRDISAMT NUMERIC(19, 5) ,
    @I_vTAXAMNT NUMERIC(19, 5) ,
    @I_vFRTAMNT NUMERIC(19, 5) ,
    @I_vTEN99AMNT NUMERIC(19, 5) ,
    @I_vMSCCHAMT NUMERIC(19, 5) ,
    @I_vPORDNMBR CHAR(20) ,
    @I_vSHIPMTHD CHAR(15) ,
    @I_vDISAMTAV NUMERIC(19, 5) ,
    @I_vDISTKNAM NUMERIC(19, 5) ,
    @I_vAPDSTKAM NUMERIC(19, 5) ,
    @I_vMDFUSRID CHAR(15) ,
    @I_vPOSTEDDT DATETIME ,
    @I_vPTDUSRID CHAR(15) ,
    @I_vPCHSCHID CHAR(15) ,
    @I_vFRTSCHID CHAR(15) ,
    @I_vMSCSCHID CHAR(15) ,
    @I_vPRCTDISC NUMERIC(19, 2) ,
    @I_vTax_Date DATETIME ,
    @I_vCURNCYID CHAR(15) ,
    @I_vXCHGRATE NUMERIC(19, 7) ,
    @I_vRATETPID CHAR(15) ,
    @I_vEXPNDATE DATETIME ,
    @I_vEXCHDATE DATETIME ,
    @I_vEXGTBDSC CHAR(30) ,
    @I_vEXTBLSRC CHAR(50) ,
    @I_vRATEEXPR SMALLINT ,
    @I_vDYSTINCR SMALLINT ,
    @I_vRATEVARC NUMERIC(19, 7) ,
    @I_vTRXDTDEF SMALLINT ,
    @I_vRTCLCMTD SMALLINT ,
    @I_vPRVDSLMT SMALLINT ,
    @I_vDATELMTS SMALLINT ,
    @I_vTIME1 DATETIME ,
    @I_vBatchCHEKBKID CHAR(15) ,
    @I_vCREATEDIST SMALLINT ,
    @I_vRequesterTrx SMALLINT ,
    @I_vUSRDEFND1 CHAR(50) ,
    @I_vUSRDEFND2 CHAR(50) ,
    @I_vUSRDEFND3 CHAR(50) ,
    @I_vUSRDEFND4 VARCHAR(8000) ,
    @I_vUSRDEFND5 VARCHAR(8000) ,
    @O_iErrorState INT OUTPUT ,
    @oErrString VARCHAR(255) OUTPUT
AS 
 
	/*
	AUTHOR: AARON BERQUIST
	DATE: 07/15/2011
	PURPOSE: POPULATE THE "ROUND" DISTRIBUTION AUTOMATICALLY CREATED WHEN
	A MULTICURRENCY PM TRANSACTION IS CREATED USING eCONNECT.
 
	BACKGROUND: WHEN CREATING A MULTICURRENCY PM TRANSACTION USING eCONNECT,
	IF THE "CREATEDIST" FLAG IS SET TO 0 AND DISTRIBUTIONS ARE BEING PASSED IN MANUALLY,
	THE SYSTEM WILL CORRECTLY CREATE A "ROUND" DISTRIBUTION (DISTTYPE = 16) IN THE PM10100 TABLE.
	UNFORTUNATELY, IT APPEARS THAT eCONNECT WILL NOT POPULATE THE DISTRIBUTION WITH THE ACCOUNT
	FROM THE ROUNDING DIFFERENCE ACCOUNT SETUP. IF YOU ENTER THE SAME TRANSACTION MANUALLY
	THROUGH THE GP APPLICATION, THE ACCOUNT IS AUTOMATICALLY POPULATED.
 
	TESTED ON DYNAMICS GP 10.00.1257.	
 
	*/
    SET nocount ON
    SELECT  @O_iErrorState = 0
 
    DECLARE @ROUNDINDEX INT
 
    --CHECK TO SEE IF THE INTEGRATION CREATED A ROUND ENTRY
    --IF SO, eCONNECT DOES NOT PICK THE DEFAULT ROUNDING ACCOUNT IF YOU CHOOSE TO MANUALLY CREATE DISTRIBUTIONS
    --SO THE POST PROCEDURE WILL READ THE ACCOUNT FROM POSTING ACCOUNT SETUP (TOOLS: SETUP: POSTING: POSTING ACCOUNTS: ROUNDING DIFFERENCE ACCOUNT)
    --AND POPULATE THE DISTRIBUTION
    IF EXISTS (SELECT 1 FROM PM10100 WHERE VCHRNMBR = @I_vVCHNUMWK AND DISTTYPE = 16 AND DSTINDX = 0)
    BEGIN
 
		--ROUNDING DIFFERENCE ACCOUNT IS SERIES 2, SEQNUMBR 900
		SELECT @ROUNDINDEX = ACTINDX FROM SY01100 WHERE SERIES = 2 AND SEQNUMBR = 900
		UPDATE PM10100 SET DSTINDX = ISNULL(@ROUNDINDEX,0) WHERE VCHRNMBR = @I_vVCHNUMWK AND DISTTYPE = 16 AND DSTINDX = 0
 
    END
 
    RETURN (@O_iErrorState)
GO
 
GRANT EXECUTE ON [dbo].[taPMTransactionInsertPost] TO [DYNGRP] AS [dbo]
GO

Post to Twitter

Use Dynamic SQL To Generate a Dynamics GP Login Macro

Jul 7th, 2011
by Aaron Berquist.
7 comments

If you run Dynamics GP and have more than a handful of companies, I am sure you have felt the pain of installing or upgrading a 3rd party product, and then having to log in to each company as “sa” to complete the install process. I had to do this recently for a new 3rd party we are using (SmartView from eOne Solutions). Once the install is complete, a log in to each database as “sa” is required to create the necessary tables to support the tool.

Wanting to avoid the aggravation (and tedium) of repeating this process for the 150+ company databases we have, I wrote the SQL script below to generate a Dynamics GP macro which can be played which will log in to each database in sequence without needing any intervention.

It took me a while to figure out how the get the right “Item” number into the macro. I first thought it was the CMPANYID value from the SY01500 table, but that wasn’t right. Next I tried the DEX_ROW_ID value, but that wasn’t it either. Some trial an error finally yielded the right formula. The Item number is the index position in the dropdown list, ordered by CMPANYID! Despite the fact that the dropdown list displays items ordered by CMPNYNAM (in other words, alphabetically), it appears to first populate the list with the items ordered by CMPANYID and assigns them the item number based on that sequencing, then reorders the list to display by CMPANYNM.

/*
AUTHOR: AARON BERQUIST
DATE: 07/07/2011
PURPOSE: THE SCRIPT BELOW WILL GENERATE A DYNAMICS GP MACRO WHICH WILL LOG IN TO EACH COMPANY IN SEQUENCE.
USEFUL WHEN YOU HAVE INSTALLED A NEW 3RD PARTY PRODUCT, OR A SERVICE PACK,
AND EACH DATABASE NEEDS TO BE INITIALIZED BY LOGGING IN AS "SA".
 
TESTED WITH DYNAMICS GP 10, BUILD 10.00.1257
 
OVERVIEW:
 
THE SCRIPT BELOW BUILDS A MACRO WHICH CAN BE RUN IN DYNAMICS GP.
DEPENDING ON THE NUMBER OF COMPANIES YOU HAVE, THE OUTPUT OF THE DYNAMIC SQL BELOW WILL BE TOO LARGE TO BE
FULLY DISPLAYED IN THE RESULTS PANE OF MANAGEMENT STUDIO.
 
THEREFORE, THE DYNAMIC SQL CODE IS INSERTED INTO THE TABLE __MACRO.
 
USE MANAGEMENT STUDIO TO EXPORT THE CONTENTS OF THE TABLE, WITHOUT THE HEADER ROW, INTO A FLAT FILE WITH AN EXTENTION OF .MAC
THE FILE CAN THEN BE PLAYED INSIDE DYNAMICS GP.
 
USAGE:
 
1. EXECUTE THE SCRIPT BELOW - IT USES DYNAMIC SQL TO BUILD A LOGIN MACRO FOR ALL DYNAMICS GP DATABASES.
2. THE CONTENTS OF THE SCRIPT ARE INSERTED INTO THE TABLE __MACRO.
3. EXPORT THE CONTENTS OF THE TABLE TO A FLAT FILE, WITH A .MAC EXTENSION.
4. LOG IN TO ANY DATABASE AS "SA" AND RUN THE MACRO. THE MACRO WILL LOG IN TO EACH DATABASE IN ALPHABETICAL SEQUENCE.
 
POTENTIAL MODIFICATIONS:
 
WHEN INSTALLING SOME 3RD PARTY PRODUCTS, YOU MAY NEED TO CLICK "OK" ON A WINDOW AT LOGIN TO INSTALL, OR PERFORM SOME REPETITIVE TASKS.
THE MACRO CODE BELOW CAN BE MODIFIED TO SUIT YOUR NEEDS. THE "SPECIAL SAUCE" IS THE ROW_NUMBER() OVER CMPANYID.
 
*/
USE DYNAMICS
GO
DECLARE @ssQL VARCHAR(MAX) = ''
 
SELECT @sSQL = @sSQL+'  CommandExec dictionary ''default''  form ''Command_System'' command ''Switch Company''
NewActiveWin dictionary ''default''  form ''Switch Company'' window ''Switch Company''
  ClickHit field ''(L) Company Names'' item '+CONVERT(VARCHAR(3),ROW_NUMBER() OVER (order BY CMPANYID))+'  # '+LTRIM(RTRIM(CMPNYNAM))+'
  MoveTo field ''OK Button''
  ClickHit field ''OK Button''
NewActiveWin dictionary ''default''  form sheLL window sheLL
NewActiveWin dictionary ''default''  form sheLL window sheLL
ActivateWindow dictionary ''default''  form sheLL window sheLL '+CHAR(13)
FROM DYNAMICS..SY01500
ORDER BY CMPNYNAM
 
PRINT (@sSQL)
 
/****** Object:  Table [dbo].[__Macro]    Script Date: 07/07/2011 21:41:48 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[__Macro]') AND type in (N'U'))
DROP TABLE [dbo].[__Macro]
GO
 
/****** Object:  Table [dbo].[__Macro]    Script Date: 07/07/2011 21:41:48 ******/
SET ANSI_NULLS ON
GO
 
SET QUOTED_IDENTIFIER ON
GO
 
SET ANSI_PADDING ON
GO
 
CREATE TABLE [dbo].[__Macro](
	[ssql] [varchar](max) NULL
) ON [PRIMARY]
 
GO
 
SET ANSI_PADDING OFF
GO
 
INSERT __MAcro SELECT @ssQL
 
--NOW EXPORT THE CONTENTS OF THE TABLE TO A FLAT FILE AND RUN IN GP.
--YOU CAN THEN SAFELY DROP THE TABLE __MACRO

You can either copy and paste the code above, or download a script file here.

Post to Twitter

Using SmartList Builder With A Linked Server – It Can Be Done!

Jul 4th, 2011
by Aaron Berquist.
18 comments

We are looking to deploy SmartList Builder + SmartView as an enhanced reporting solution for our internal customers. One of the things I have seen floating around the net is that you can’t use SmartList Builder with a SQL Linked Server. In fact, a number of Dynamics MVP’s alluded to this in a support thread at http://www.ms-news.net/f1610/smartlist-builder-odbc-error-8692687.html. The actual error message will look something like this:

GPS Error 58

SQL Error 7405 ODBC SQL Server Driver

Heterogeneous queries require the ANSI_NULL and ANSI_Warnings options to be set for the  connection.
This ensures consistent query semantics. Enable these options and
then reissue your query.

ODBC error 37000

I’ll show a little further into the article that yes, you can work around this, and pretty easily too – it just takes a little sleight-of-hand with your SmartLists.

A little background – we have 150+ Dynamics GP companies, all on one database server. We also frequently have requests to produce reports which consolidate data from all of these entities onto one SmartList so that, in one report, users can quickly see data from every entity. Up until now, we have been using the technique I outlined here to create reports which use a linked server to execute a stored procedure inside a view using OPENQUERY. In order to make this work, I had to come up with a workaround to the very ANSI_NULLs issue other people have reported as stopping them in their tracks.

The secret boils down to this – you have to trick AnyView, or SmartList Builder into accepting the view the first time you add it. Once you do that, and build your SmartList, you can change what the view is actually doing after the fact. I know that it works with AnyView, and have done a test with SmartList builder and it works there as well.

For the steps below, assume the final query you want to use looks something like the code below. Column1 is varchar(5) and Column2 is TINYINT:

select Column1, Column2 from LINKEDSERVER.SAMPLEDB.dbo.SAMPLETABLE

Here are the steps you need to follow:

Create a SQL view with the columns and field lengths that match the “final” report you want to produce:

CREATE VIEW SampleView
 
AS
 
SELECT convert(varchar(5),'') as Column1, convert(tinyint,0) as Column2
 
GO
 
GRANT SELECT ON SampleView TO DYNGRP

Add that view to SmartList Builder / AnyView and build your SmartList off that view.

Once you have successfully built the object, ALTER the view to call the code from the linked server:

ALTER VIEW SampleView
 
AS
 
--SELECT convert(varchar(5),'') as Column1, convert(tinyint,0) as Column2
select Column1, Column2 from LINKEDSERVER.SAMPLEDB.dbo.SAMPLETABLE

 

Voila! Your SmartList will run the linked server code, with no ANSI_NULL warnings!

I have used this approach for most of the SmartLists we have in Production today, with great success. Of course, using a linked server is not, in the end, an ideal solution, but for cases when you have to, the method above will allow you to use SmartLists with SQL Linked Servers successfully.

Note: I have updated the sample code above to include the “GRANT SELECT” permissions to “DYNGRP” on the view. If you don’t do this, SmartView will spin forever on the “Loading…” screen when you try to run a SmartList.

Post to Twitter

Book Review: Microsoft Dynamics GP 2010 Reporting

May 26th, 2011
by Aaron Berquist.
2 comments

Full Disclosure: I was provided a no-charge eBook version for the purposes of this review by the publisher, Packt.

Microsoft Dynamics GP 2010 Reporting by David Duncan and Christopher Liley, is a very good, general purpose book on the reporting options available in Dynamics GP. It focuses on the “Big 5” reporting options for Dynamics GP – SmartList/Excel Reports, Report Writer, SSRS, Analysis Cubes, and Management Reporter. In my opinion, the book does a good job of balancing the “theory” of report creation with the “reality” of execution. That is to say, the book balances explaining “why” and “when” to pick a certain reporting tool with “how” to use the tool you’ve chosen.

If I had to suggest a target audience for the book, my feeling is the following types of people would best benefit:

1. “Power” end-users of Dynamics GP who want the ability to build some basic reports without needing the help of “the IT guy” or a consultant.

2. Beginner-to-Intermediate Dynamics GP professionals who want some guidance on how to choose the right reporting tool for the job, and how to implement some of the basic features of each reporting tool.

By way of compliments, I can say that I picked up a few tips on SmartList/Excel Reports I can likely make use of at my “day job”. Also, given the somewhat dry nature of a technical book on report creation, the authors took enough of a conversational approach to keep things feeling somewhat fresh.

If you’re an advanced Dynamics GP professional or consultant, I’m not sure there is going to by a lot on this book for you. As mentioned above, I see this as a good, general-purpose book to give a flavour for the reporting options available. It’s not a deep-dive, nuts-and-bolts technical book – and depending on who you are, that may not be a bad thing.

Overall, I give the book a 7/10 – broad coverage of subjects, written well for a technical book, and with enough detail to get someone up and running on a reporting tool.

The publisher is running a promotion for the month of May – details of which can be found at http://link.packtpub.com/J9AEn6

Post to Twitter

Troubleshooting Upgrade to Dynamics GP 2010 R2, Part 2

May 3rd, 2011
by Aaron Berquist.
5 comments

Another error encountered, another error resolved.

During the upgrade of the sample database, TWO, I received an error on the procedure SVC_MailProcessLOG. The error message was:

ERROR [Microsoft][SQL Server Native Client 10.0]TCP Provider: The specified network name is no longer available.

That incredibly helpful error message really means: Perform your GP Utilities run on the SQL Server itself.

I made the mistake of running GP Utilities from one of the client machines, not from the actual SQL Server. It appears that something in that procedure causes an issue when you’re trying to create it from a client machine, over the network, rather than from the local SQL Server itself.

Another good reason to RTFM, I guess :)

Post to Twitter

Troubleshooting Upgrade to GP 2010 R2

May 3rd, 2011
by Aaron Berquist.
15 comments

I’ve been working on installing the newly released GP 2010 R2 in a “sandbox” environment. I ran in to an issue performing the upgrade and thought I’d share it in case it helps anyone else.

I had the GP 2010 client installed on the same machine I was installing R2 on. Rather than creating a new DSN at install time, I simply renamed the existing DSN used for GP 2010 and moved on to GP Utilities.

However, the upgrade kept crashing at the “syExcelReports” step. The crash report indicated an issue with sqlncli.exe.

After trying a few other things, I finally thought about what the sqlncli.exe message might mean. Going in to the DSN setup (c:\windows\syswow64\odbcad32.exe on my 64-bit machine), I noticed that the original GP 2010 DSN I used was using an old version of the SQL Client.

I created a new DSN using the SQL Native Client 10.0 driver, re-ran the upgrade using that DSN, and voila! Upgrade completed. I hope this tip saves someone else some time and frustration as they work through their install/upgrade of GP2010 R2.

 

Post to Twitter

Book Review: Dynamics GP 2010 Implementation by Victoria Yudin

Jan 20th, 2011
by admin.
2 comments

Full Disclosure: I received a no-charge electronic copy of the book from the publisher.

In my opinion, Victoria Yudin has managed to cram all of the information necessary to properly implement Dynamics GP 2010 into this book, “Dynamics GP 2010 Implementation“. The material covers everything from start to finish – from building your implementation team through to post-implementation follow up and training.

Chapters 1-4 cover the essential planning exercises you will need to follow in order to successfully implement your ERP system. I like the fact that she spent the time to outline some of the intricacies of Dynamics GP (such as how, for a SQL-based application, it doesn’t use SQL Server effectively as it could, and why), and how the Dexterity runtime works. Also of benefit in this section is a good guide on how to select the right people for your implementation team, and a sizing guide for how to select the right level of hardware for your deployment.

Chapter 5 covers the actual steps to perform the installation and some common configuration items. I was pleasantly surprised that the whole installation stack was covered – in other words, the book walks the reader through the steps of installing SQL Server. For more advanced or experienced implementers, this may be old hat, but for the “new” implementer this information could be very valuable.

Chapters 6-8 walk through module configuration – the main subledgers (AP, AR, SOP, POP, etc) and the GL. Each section is well written and contains enough detail for a “new” implementer to configure the system the appropriate way.

Chapter 9 covers populating master data using Integration Manager. While I personally am not a fan of Integration Manager, for the “new” implementer it is worthwhile to understand how it works and what it can do.

Chapter 10 covers training, and lists out some common tools that may be of use post-deployment. Echoing the rest of this book, this area is a good mix of suggestions and hard facts to aid the implementer.

Overall, I was impressed by the depth and breadth of material covered. One thing I would have liked to see more of is “tips and tricks” or “lessoned learned” by the author. As someone with many years of experience, I am sure the author has come across a number of unique or interesting scenarios, and adding some of those to the book would have added a little more flavour to the book.

I also would have loved to see a “what’s new” section which highlights some of the main changes  between GP 10 and GP 2010. Although the book appears slanted towards those performing a new implementation of the Dynamics GP system, having insight into what is new about this particular release of the product, as compared to the previous version, would have been helpful as well.

In all, I’d rate the book as an 8/10. Well written, comprehensive, and with enough breadth and depth that I didn’t feel like I was missing anything critical in order to properly implement Dynamics GP 2010.

Post to Twitter

Build IT Better: Overview

Jan 3rd, 2011
by Aaron Berquist.
3 comments

As an IT Architect, my job is to help design solutions to complex business problems. Along the way I’ve learned a few things – some by trial and error, some by listening to what smart people have to say – which help me do my job well.
In the month of January, I’m launching the “Build It Better” series of articles. It’s my goal to craft a series of daily blog postings which will tackle the 5 key areas of building solutions which will “Wow” your customers, whether they are internal or external:

  1. Requirements Gathering
  2. Design
  3. Build, Configure
  4. Test
  5. Deploy

In each section, I’ll have 5 different tips, tricks, or suggestions on how to excel at each particular area. At the end, I hope you’ll have learned a few things which will help you “Build IT Better” as you tackle your next challenge.

Post to Twitter

In Praise of FieldService, Or: Product 949, You Sure Are Fine

Jan 2nd, 2011
by Aaron Berquist.
1 comment

Having spent the better part of my career working with Dynamics GP, I’ve had the pleasure of working with most of the core modules, and a number of 3rd party applications as well. By far and away, my favourite module to work with has been the FieldService module, and more specifically, the Contracts Administration portion of it. I find it’s well designed, well written, and very easy to enhance and extend when the need for custom business processes dictates.

After reflecting on why it is I find this to be my favourite module, it comes down to the fact that so much of it is based on SQL stored procedures. This fact alone has enabled us to make some very useful and powerful changes to the behaviour of certain parts of the module in a fairly easy and straightforward manner. Some of the changes we’ve made by modifying certain stored procedures are:

  1. Create SOP orders with a series of items, then create contractible items based on those items. (Procedure modified: SVC_Create_Cont_Line_From_SOP)
  2. Automatically create/change/delete items on a contract at renewal time. (Procedure modified: SVC_Contract_Renew)
  3. Repurpose certain fields and tables for other uses (Procedure modified: SVC_Create_Cont_Line_From_SOP).
  4. Eliminate contracts from appearing in the Contract Move screen based on certain criteria (Procedure modified: SVC_Create_Contract_Move_WORK)

In most cases, a combination of modifying the stored procedure, plus some “light” VBA coding is all that’s required to implement the custom business logic we require. It would be fantastic if other modules implemented more of a “stored procedure” based approach.

In short, for the developer who does not know Dexterity, the implementation of the FieldService module, with it’s heavy use of SQL stored procedures, makes it pretty straightforward to enhance the application in a rapid and efficient way.

Post to Twitter

← Earlier Posts