Category: GP (Great Plains)

I’ve been doing some work with SmartConnect for a client recently where one of the integrations was creating new items. As the project progressed, some of the items which had been imported needed to be replaced with different item numbers. To ensure we didn;t cause problems, I didn’t want to delete items through SQL directly, due to the number and variety of tables involved, so needed to come up with a way of generating the macro.

Macros are useful ways of repeating an action, such as deleting items, but any variation to the data and the macro will fall over. One variation was that some items had posted transactions against them so I needed to avoid these transactions.

I created the below script to create the macro for me, with SSMS set to output to text, and built in joins and checks on all of the relevant tables in Purchase Order Processing, Inventory and Sales Order Processing which may have contained data. The script is probably a little overkill on the checks it does, but I wanted to make sure it caught as much as possible.

Once the script has been run, you can copy the macro text into a file and play the macro to delete the items (if you have a lot of items, you can play the macro fast).

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
SELECT
	'# DEXVERSION=14.00.0085.000 2 2
CheckActiveWin dictionary ''default''  form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' 
  TypeTo field ''Item Number'' , ''' + CAST(RTRIM(['Item Master'].ITEMNMBR) AS VARCHAR(50)) + '''
  MoveTo field Inactive  # ''FALSE''
  CommandExec dictionary ''default''  form ''IV_Item_Maintenance'' command ''Delete Button_w_IV_Item_Maintenance_f_IV_Item_Maintenance'' 
# Are you sure you want to delete this record?
NewActiveWin dictionary ''default''  form DiaLog window DiaLog 
  ClickHit field OK 
NewActiveWin dictionary ''default''  form ''IV_Item_Maintenance'' window ''IV_Item_Maintenance'' '
FROM
	IV00101 AS ['Item Master']
LEFT JOIN
	(
		SELECT
			ITEMNMBR
		FROM
			POP10110
		UNION
			SELECT
				ITEMNMBR
			FROM
				POP30110
			UNION
				SELECT
					ITEMNMBR
				FROM
					POP10210
				UNION
					SELECT
						ITEMNMBR
					FROM
						POP30210
					UNION
						SELECT
							ITEMNMBR
						FROM
							SOP10200
						UNION
							SELECT
								ITEMNMBR
							FROM
								SOP30300
							UNION
								SELECT
									ITEMNMBR
								FROM
									IV30300
	) AS ['Used Items']
		ON
			['Used Items'].ITEMNMBR = ['Item Master'].ITEMNMBR
INNER JOIN
	IV00102 AS ['Item Quantities']
		ON
			['Item Quantities'].ITEMNMBR = ['Item Master'].ITEMNMBR
		AND
			['Item Quantities'].LOCNCODE = ''
LEFT JOIN
	IV10301 AS ['Stock Count Line']
		ON
			['Stock Count Line'].ITEMNMBR = ['Item Master'].ITEMNMBR
WHERE
	['Used Items'].ITEMNMBR IS NULL
AND
	['Item Quantities'].QTYONHND = 0
AND
	['Item Quantities'].QTYRTRND = 0
AND
	['Item Quantities'].QTYINUSE = 0
AND
	['Item Quantities'].QTYINSVC = 0
AND
	['Item Quantities'].QTYDMGED = 0
AND
	['Item Quantities'].ATYALLOC = 0
AND
	['Stock Count Line'].ITEMNMBR IS NULL

If when you run the script you only get part of the macro text, you can change the query results length.

A client recently reported a problem whereby users were no longer able to export a SmartList favourite to Excel as they were receiving a permissions error when the Excel file opened:

The SELECT permission was denied on the object 'uv_AZRCRVBudgetByMonth', database LIVE', schema 'dbo'

I did some checking and realised that the problem was that the file being opened when the user tried to do an export, was named like a Refreshable Excel report; a little more checking showed that there was a Refreshable Excel report of that name using the view from the SmartList. The SmartList object being used was using a SQL View to return the data and this had made the Publish button available. An accidental click had then published the Excel report, but, as the user did not have permissions to execute the view under their Windows account, the Excel report produced the error.

To confirm this I ran the below script to see what published Refreshable Excel reports were present:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
SELECT
	DEX_ROW_ID
FROM
	syDeployedReports
WHERE
	ObjectType = 5

In this case, a few items were returned, but none of the SmartList Designer SmartLists should have been published, so I was able to take the above script and include it in a delete statement:

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
DELETE FROM
	syDeployedReports
WHERE
	DEX_ROW_ID IN
		(
			SELECT
				DEX_ROW_ID
			FROM
				syDeployedReports
			WHERE
				ObjectType = 5
		)

After running this, we just needed to delete the Excel report from the published location (same as the ones published from Reporting Tools Setup).

Now when a user tries to export the SmartList, the data is exported to Excel using the standard export function.

I have a Microsoft Dynamics GP project deployment coming up soon for a client project for which a number of SQL views have been created; I can easily grab a create script for the views from Object Explorer Details in SQL Server Management Studio, but I also need to create the grant statement to give permissions to Dynamic GP users to use the SmartLists Builder objects which use these views.

INFORMATION_SCHEMA.VIEWS is available for querying in SQL Server and allows you to get a listing of the views. The following script selects all views with my custom user view prefix (highlighted).

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (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).
*/
SELECT
	CONCAT('GRANT SELECT ON ', TABLE_NAME,' TO DYNGRP', CHAR(13), 'GO')
FROM
	INFORMATION_SCHEMA.VIEWS
WHERE
	TABLE_NAME LIKE 'uv_AZRCRV_%'

Run the script with the output set to text as for each view the script creates the GRANT and subsequent GO commands.

Mark Polino highlighted a post on Getting Custom with Excel Refreshable Reports on the Dynamics GP Support and Services Blog.

I’ve made a lot of use for clients on refreshable Excel reports and have made them available in the navigation list for clients (more recently, I’ve used that to make Jet Reports accessible in the same way):

Continue reading “Custom Refreshable Excel Reports in Microsoft Dynamics GP navigation lists”