Category: GP (Great Plains)

While implementing Microsoft Dynamics GP for a new client a while ago, we used Integration Manager to import over 100,000 Inventory items and then a SQL script to insert the 80,000 bill of materials (BOMs).

This worked well, in that it got all of the information loaded, but found that if we inserted the BOMs in this way, they could not be viewed in the BOM Inquiry window.

I used a SQL query to change the status from Active to Pending and then looked at the best way of changing them to Active through the Dynamics GP.

The only way to bulk change the data was by using a GP Macro; the usual way we do this is to do an extract of the data and then mailmerge this into a prerecorded macro in Microsoft Excel.

However, this can be avoided, by using the SQL select to not only get the data, but to output the macro at the same time:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
SELECT DISTINCT
	'# DEXVERSION=11.00.0364.000 2 2
	CheckActiveWin dictionary ''default''  form bmBillMaintenance window bmBillMaintenance 
	  TypeTo field ''Item Number'' , ''' + RTRIM(BMH.ITEMNMBR) + '''
	  MoveTo field ''Bill Status'' item 0 
	  ClickHit field ''Bill Status'' item 2  # ''Pending'' 
	  MoveTo field ''Expansion Button 1'' 
	  ClickHit field ''Expansion Button 1'' 
	NewActiveWin dictionary ''default''  form bmBillMaintenance window ChangeStatus 
	  ClickHit field ''Bill Status'' item 1  # ''Active'' 
	  MoveTo field ''Process Button P'' 
	  ClickHit field ''Process Button P'' 
	NewActiveWin dictionary ''default''  form bmBillMaintenance window bmBillMaintenance 
	  MoveTo field ''Save Button'' 
	  ClickHit field ''Save Button'' 
	'
FROM
	BM00101 AS BMH
INNER JOIN
	BM00111 AS BMC
		ON BMC.ITEMNMBR = BMH.ITEMNMBR
WHERE
	BMH.Bill_Status = 2

I needed to make sure that SSMS was configured to return the data into text and that the data returned was more than the default 256 characters.

Once you have the returned macros, save the file, open Bill of Materials Maintenance and then run the macro.

Budgets in Microsoft Dynamics GP are easy to maintain using the Budget Wizard. However, we do regularly field calls from clients reporting that the budget import is not working.

The problem reported, was the same as the problem always is; the budget template was not in the correct format.

The first image is the header of the one they were trying to import and the second is the Master budget I exported from GP to show them:

As you can see the former budget template has an extra row in the header; this is sufficient to stop the import working.

Everytime we have had this error reported we have found that something was changed in the budget template: a new header row, a header row being deleted, a column being added (the latter is the most common.

The client removed the extra row and was then able to import the budget.

We’re in the middle of large upgrade project at the moment for a client and encountered an error when trying to run an integration.

This particular integration is an Account one, which has been extended with VB Script which checks to see if the segments exist, and if not, it inserts them. When a user tried to run the integration, they get the following error:

Opening source query...
Establishing source record count...
Beginning integration...
DOC 1 ERROR: System.Data.SqlClient.SqlError: Cannot open database "GPLIV" requested by the login. The login failed.
DOC 2 ERROR: System.Data.SqlClient.SqlError: Cannot open database "GPLIV" requested by the login. The login failed.
DOC 3 ERROR: System.Data.SqlClient.SqlError: Cannot open database "GPLIV" requested by the login. The login failed.
Integration Failed
Integration Results
    3 documents were read from the source query.
    3 documents were attempted:
        0 integrated without warnings.
        0 integrated with warnings.
        3 failed to integrate.

I did some double checking and it turned out that the user who was doing the testing had two Domain accounts (one with full name and one with initial and surname) and were not using the one I had configured originally.

All of the other users I had worked with had been using Domain accounts of initial and surname, which was the one I had configured, but this particular user was using the account with their full name.

Adding this Domain user account to the database with the DYNGRP role resolved the problem.

I can’t remember why I created this code, but it allows a batch file to be run from an Integration Manager script:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
Set objShell = CreateObject("WScript.Shell")
Set objExec = objShell.Exec("B:\IM.bat")

I’m posting it here so I don’t lose it for when I remember why I write it, and perhaps it may be useful to others.

We have a client who will be loading payables transactions into Microsoft Dynamics GP and need secondary postings created into the General Ledgers of other company databases. The idea is to use the AfterDocument script to create and submit an eConnect document, which is in XML format.

The below code is a small proof of concept which creates an XML file with child and parent nodes and saves it to the T:\:

/*
Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (https://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 2.0 UK: England & Wales (CC BY-NC-SA 2.0 UK).
*/
Set xmlDoc = CreateObject("Microsoft.XMLDOM")

Set objRoot = xmlDoc.createElement("RootElement")
xmlDoc.appendChild objRoot

Set objRecord = xmlDoc.createElement("SubLevel1")
objRoot.appendChild objRecord

Set objName = xmlDoc.createElement("Element1")
objName.Text = "AAAAAAA"
objRecord.appendChild objName

Set objDate = xmlDoc.createElement("Element2")
objDate.Text = "BBBBBBB"
objRecord.appendChild objDate

Set objIntro = xmlDoc.createProcessingInstruction ("xml","version='1.0'")
xmlDoc.insertBefore objIntro,xmlDoc.childNodes(0)

xmlDoc.Save "T:\Test.xml"

We have a couple of clients using MDA (Multi-dimensional Analysis; the precursor to Analytical Accounting), which I don’t know terribly well. So, when one of them asked for a new SmartList Object to be created which extracts information about General Journals and the related MDA information, I needed to do some exploring of the database to work out the links.

Unfortunately, the links between the GL transactions and MDA are not especially obvious. To verify what I had created I did a search and came across a post from 2011 by Mark Polino which was posting code created by a Jeremy Lowell.

I ended up combining some of the code I had with Jeremy’s code (when I tried just his I was getting duplicate lines) to create the below SQL View. Since writing and giving the view to the client, I’ve spotted a few places where the SQL could be tightened up, but this view has been tested in its current state.

CREATE VIEW uv_AZRCRV_LinkGLtoMDA AS
	SELECT DISTINCT
		GLT.JRNENTRY
		,GLT.YEAR
		,GLT.TRXDATE
		,GLT.REFRENCE
		,GLT.SOURCDOC
		,GLT.DEBITAMT
		,GLT.CRDTAMNT
		,GLT.ACTINDX
		,DTA10100.DTASERIES
		,DTA10100.DTAREF
		,DTA10100.GROUPID
		,DTA10100.DTA_GL_Reference
		,DTA10100.GROUPAMT
		,DTA10200.CODEID
		,DTA10200.POSTDESC
		,DTA10200.CODEAMT
	FROM
		(SELECT GLT.JRNENTRY
			,GLT.OPENYEAR AS YEAR
			,GLT.TRXDATE
			,GLT.REFRENCE
			,GLT.SOURCDOC
			,GLT.DEBITAMT
			,GLT.CRDTAMNT
			,GLT.ACTINDX
			,GLT.SEQNUMBR
			,GLT.OrigSeqNum
			,GLT.ORCTRNUM
		FROM
			GL20000 AS GLT WITH (NOLOCK)
		UNION ALL 
			SELECT GLT.JRNENTRY
				,GLT.HSTYEAR AS YEAR
				,GLT.TRXDATE
				,GLT.REFRENCE
				,GLT.SOURCDOC
				,GLT.DEBITAMT
				,GLT.CRDTAMNT
				,GLT.ACTINDX
				,GLT.SEQNUMBR
				,GLT.OrigSeqNum
				,GLT.ORCTRNUM
			FROM
				GL30000 AS GLT WITH (NOLOCK)
		) AS GLT
	LEFT OUTER JOIN
		DTA10100 WITH (NOLOCK)
			ON
				DTA10100.JRNENTRY = GLT.JRNENTRY
			AND
				DTA10100.ACTINDX = GLT.ACTINDX 
			AND
				(DTA10100.SEQNUMBR = GLT.SEQNUMBR OR DTA10100.SEQNUMBR <> GLT.SEQNUMBR)
			AND
				GLT.ORCTRNUM = DTA10100.DOCNUMBR 
	LEFT OUTER JOIN
		DTA10200 WITH (NOLOCK)
			ON
				(DTA10200.DTAREF = DTA10100.DTAREF
					AND
				GLT.SEQNUMBR = GLT.OrigSeqNum)
			OR
				(DTA10200.DTAREF = DTA10100.DTAREF
					AND
				GLT.SEQNUMBR <> GLT.OrigSeqNum)
GO
GRANT SELECT ON uv_AZRCRV_LinkGLtoMDA TO DYNGRP
GO