We recently upgraded a client with well over a hundred companies from Microsoft Dynamics GP 10 to GP 2015; one of the new pieces of functionality they wanted to start using was the Budget Transactions.

One problem is that the keep history checkbox for Budget Transactions in General Ledger Setup (Financials » Setup » General Ledger) is unmarked.

Enabling it for this many companies was going to take a substantial amount of time (even if we generated a macro to do this in automatically) as the client has over 120 companies in Dynamics GP. It is a simple setting in one table which needs to be updated, so instead of using a macro I created a SQL script containing a cursor to loop through all of the databases attached to the system database and enable this setting.

As with all scripts which change data, please make sure you have a good backup of your database (or in this case all of your company databases) before running the script.

DECLARE @IntercompanyID VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

DECLARE
	cursor_InterID CURSOR 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	
	OPEN cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@IntercompanyID
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			SET @SQLStatement = 'UPDATE ' + @IntercompanyID + '..GL40000
								SET KPBUDTRXHIST = 1
								WHERE KPBUDTRXHIST = 0'
			EXEC (@SQLStatement)
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@IntercompanyID
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

You will need to amend the script if your System Database is not called DYNAMICS.

Want an easy way to see which vendors are configured for remittances to be emailed? Plug this view into SmartList Designer, or SmartList Builder, and you’ll have that easy way.

CREATE VIEW uv_AZRCRV_VendorRemittanceEnabled AS
SELECT 
	EmailCardID AS VENDORID
	,CASE WHEN EmailDocumentEnabled = 1 THEN 'Yes' ELSE 'No' END AS 'Remittance Enabled'
 FROM 
	SY04905
WHERE
	EmailSeriesID = 4
AND
	MODULE1 = 19
AND	
	EmailDocumentID = 6
GO

GRANT SELECT ON uv_AZRCRV_VendorRemittanceEnabled TO DYNGRP
GO

I produced this view to return vendor email address for a client a while ago; I don’t typically use this view by itself, but instead combine it with GP tables to produce a larger SmartList report using either SmartList Designer or SmartList Builder.

CREATE VIEW uv_AZRCRV_VendorInternetAddresses
AS
SELECT
	['Internet Addresses'].Master_ID AS 'Vendor ID'
	,['Internet Addresses'].ADRSCODE AS 'Address Code'
	,['Internet Addresses'].EmailToAddress AS 'Email To Address'
	,['Internet Addresses'].EmailCcAddress AS 'Email Cc Address'
	,['Internet Addresses'].EmailBccAddress AS 'Email Bcc Address'
	,['Internet Addresses'].INET1 AS 'Email'
FROM
	SY01200 AS ['Internet Addresses']
WHERE
	['Internet Addresses'].Master_Type = 'VEN'
GO

GRANT SELECT ON uv_AZRCRV_VendorInternetAddresses TO DYNGRP
GO

This one came up from a query a client had about linking a sales order transaction line to the customer item in SmartList Builder. The problem is that to join two tables together, you need all of the key fields to be on the same table; unfortunately, with the SOP Transaction table, this isn’t the case when you want to link to the customer item.

SOP10100 (Sales Transaction Work) holds the CUSTNMBR (Customer Number), but SOP10200 (Sales Transaction Amounts Work) holds the ITEMNMBR (Item Number) which are both needed to link to SOP60300 (Sales Customer Item Cross Reference) which holds the customer item number and description.

While there may be a way to do this in SmartList Builder I’ve not been able to work it out (other than using two calculated fields), it is easier, quicker and more reusable, to create a simple SQL View which returns the relevant information.

In this case the view I created works only for transactions which are at a status of work:

CREATE VIEW uv_PI_SOPCustomerItemLink
AS
	SELECT
		SOP101.CUSTNMBR
		,SOP102.ITEMNMBR
		,SOP603.CUSTITEMNMBR
		,SOP603.CUSTITEMDESC
	FROM
		SOP10200 AS SOP102
	INNER JOIN
		SOP10100 AS SOP101
			ON SOP101.SOPNUMBE = SOP102.SOPNUMBE
				AND SOP101.SOPOwner = SOP102.SOPOwner
	INNER JOIN
		SOP60300 AS SOP603
			ON SOP603.CUSTNMBR = SOP101.CUSTNMBR
				AND SOP603.ITEMNMBR = SOP102.ITEMNMBR
GO

GRANT SELECT ON uv_PI_SOPCustomerItemLink TO DYNGRP
GO

The SQL above includes the Grant statement used to add select permissions for the DYNGRP.

Well, I received the notification email yesterday afternoon that I had been awarded the Microsoft Most Valuable Professional award for the third year running. It’s really niec to receive the award as it shows I am still contributing in a meaningful way to the Dynamics GP community.

There was another new MVP for Dynamics GP added to the roster this month as well; David Musgrave. Previously David was ineligible for the award as he worked directly for Microsoft, but as of October last year he has been independent again and running Winthrop Development Consultants (the company responsible for GP Power Tools, formerly known as the Support Debugging Tool).

David has always been a great contributor to the Dynamics GP community and I’m delighted to see that this has been officially recognised by Microsoft with his MVP award.

I have had this script for quite a while and have used it a number of times for different clients when implementing the Inventory Control module in Microsoft Dynamics GP.

One client who was using Inventory was entering a lot of user categories, mistakenly entered the description into the Image field. In that case I did not know that they were populating the User Categories or I would have offered this script to them to save time.

To use the script you need a CSV file with four columns: User Category Value (the ID of the item you want to load), User Category Number (which of the user categories into which the row is to be loaded), Image URL and Description:

CREATE TABLE #UploadData
	(USCATVAL VARCHAR(100)
	,USCATNUM VARCHAR(1)
	,Image_URL VARCHAR(300)
	,UserCatLongDescr VARCHAR(300))
GO

BULK INSERT
	#UploadData
FROM
	'c:\temp\UserCategories.csv'
WITH
	(FIELDTERMINATOR = ','
	,ROWTERMINATOR = '\n')
GO

INSERT INTO IV40600
	(USCATVAL
	,USCATNUM
	,Image_URL
	,UserCatLongDescr)
	
	(SELECT
		LEFT(UD.USCATVAL, 10)
		,LEFT(UD.USCATNUM, 1)
		,LEFT(UD.Image_URL, 254)
		,LEFT(UD.UserCatLongDescr, 254)
	FROM
		#UploadData AS UD
	WHERE (SELECT
			COUNT(IV.USCATVAL)
		FROM
			IV40600 AS IV
		WHERE
			IV.USCATVAL = UD.USCATVAL
		AND
			IV.USCATNUM = UD.USCATNUM) = 0)
GO

DROP TABLE #UploadData
GO

You will need to change the highlighted line to the location of your CSV file. As always before running a script on live, test it in a test company first and have a good backup of your database.