Execute Batch File In Integration Manager

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPI 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 (http://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.

● Categories: Dynamics, GP, Integration Manager, Microsoft ● Tags: , , , ,  ● Permalink ● Shortlink ●

List View Action Pane Error During Upgrade

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPWe have been doing a number of upgrades for clients recently, the majority of which went fine. However, we did have one, being done by one of my team, where there was an error produced on the table List View Action Pane when upgrading to Microsoft Dynamics GP 2015 R2:

Company Detail

Continue reading → List View Action Pane Error During Upgrade

● Categories: Dynamics, GP, GP Utilities, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Integration Manager VBA Script To Create XML Output

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPWe 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 (http://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"
● Categories: Dynamics, GP, Integration Manager, Microsoft ● Tags: , , , ,  ● Permalink ● Shortlink ●

List All Installed Programs

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPThis post is a diversion from the usual Microsoft Dynamics GP focused ones, but is related to Microsoft. The admin team at work recently emailed everyone asking for a list of all the applications installed on laptops and desktop machines for a licence audit. They asked for a screen shot of the Add/Remove Programs window, but one of the developers replied with an email containing a set of commands which would list all programs. Which is much easier when you have a lot of software installed which would require multiple screenshots.

To run the script, open a command prompt in administrator mode and type:

cd c:\windows\system32\wbem (hit enter)
wmic (hit enter)
/output:C:\InstallList.txt product get name,version

The first highlighted section above contains the output location and the second shows the fields to return.

You can use the following to get a list of available fields:

cd c:\windows\system32\wbem (hit enter)
wmic product get /?(hit enter)
● Categories: Microsoft, Windows ● Tags: , ,  ● Permalink ● Shortlink ●

Microsoft Dynamics GP 2016 R1 Web Client Copy & Paste Bug

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPI’ve recently been upgrading all of my demo and test environments from Microsoft Dynamics GP 2015 R2 to 2016 R1. Microsoft Dynamics GP 2015 R2 web client had a bug in the General Ledgers Transaction Entry windows Excel copy and paste function which prevented it from working.

It worked fine in the desktop client which meant for regular issues for those clients operating a hybrid installation with a mix of desktop and web clients. It could also cause problems when doing a demo and someone asked to see it while I had the web client open rather than the desktop client.

My understanding was that this bug was fixed in 2016, which I was really looking forward to; I also have a large client who was looking forward to this being fixed as well so they could minimise the use of Integration Manager for importing journals.

Unfortunately, after installing 2016 I did a quick test and found that copy and paste still did not work. I asked around and found that Belinda Allen had the exact same issue, she had found the issue already before I asked, which confirmed that it was not confined to my system.

Belinda raised a call with Microsoft and they have confirmed that it is a reproducible bug which has been escalated to the development team to look into.

It would be nice to have a hotfix, but I am guessing that it will be in Dynamics GP 2016 R2 at the earliest.

● Categories: Dynamics, GP, Microsoft, Web Client ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Upgrading Microsoft Dynamics GP: Missing Security Roles

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPWhen you do a fresh install of Microsoft Dynamics GP, all of the required security roles and tasks are created. However, when Dynamics GP is upgraded, the new security roles and tasks are not automatically added. Microsoft do supply a set of scripts each time which can be run to add the roles and tasks.

This comes up every time we upgrade a client and I am tired of having to hunt out the posts from Microsoft, so I’m bringing together a list of the last few scripts and will add new ones here as each version is released.

The versions I could find again are listed below

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL View Joining GL Transactions To MDA

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPWe 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.YEAR
			,GLT.TRXDATE
			,GLT.REFRENCE
			,GLT.SOURCDOC
			,GLT.DEBITAMT
			,GLT.CRDTAMNT
			,GLT.ACTINDX
		FROM
			GL20000 WITH (NOLOCK)
		UNION ALL 
			SELECT GLT.JRNENTRY
				,GLT.YEAR
				,GLT.TRXDATE
				,GLT.REFRENCE
				,GLT.SOURCDOC
				,GLT.DEBITAMT
				,GLT.CRDTAMNT
				,GLT.ACTINDX
			FROM
				GL30000 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
● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R1: Install SBA Web Components

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPIn the last post we installed the feature in the desktop client for SBA; in this post I’m going to install the server components.

To do this, launch the Microsoft Dynamics GP 2016 setup utility and select Web Components:

Microsoft Dynamics GP 2016 setup utility

Continue reading → Hands On With Microsoft Dynamics GP 2016 R1: Install SBA Web Components

● Categories: Dynamics, GP, Microsoft, Service Based Architecture ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

Microsoft Dynamics GP 2016 Cookbook Available In January 2017

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPMark Polino and I are working on a new version of the Microsoft Dynamics GP Cookbook for Dynamics GP 2016.

The book is currently scheduled for release in January 2017 and can be pre-ordered now from Packt Publishing:

Microsoft Dynamics GP Cookbook

● Categories: Dynamics, GP, Microsoft, Packt Publishing, Publishing ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

Hands On With Microsoft Dynamics GP 2016 R1: Install SBA Client Components

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPThe final additional product to install, is the Service Based Architecture. This is a relatively new addition for Dynamics GP which allows for interaction using REST based web services, rather than the more traditional SOAP based ones made available via the Web Services for Microsoft Dynamics GP.

There are two steps to installing Service Based Architecture (SBA), the first of which, install the SBA feature to the Dynamics GP client. This is the one which we will cover in this post; the next post will cover the second.

I am going to add this feature to the desktop client installation on my web server, so need to use the Programs and Features Control Panel applet. Once it is loaded, select Microsoft Dynamics GP and click Change:

Programs and Features

Continue reading → Hands On With Microsoft Dynamics GP 2016 R1: Install SBA Client Components

● Categories: Dynamics, GP, Microsoft, Service Based Architecture ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●