Implementing SmartList Builder: Register

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

If you’re testing SmartList Builder, you can use it in the Famrikam Sample company without entering a registration key. However, yo use it on a normal company, even a test one, you will need to input the key.

To do this, log into Microsoft Dynamics GP as a user with the POWERUSER* role and open the SmartList Builder Register window (Administration area page » Setup » SmartList Builder » Register SmartList Builder) and enter the key in the Registration Key field:

SmartList Builder Register

Click Save to sdave the key and close the window.

Implementing SmartList Builder: Download and Install

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

SmartList Builder is downloaded direct from the eOne Solutions website as a zip file.

Download SmartList Builder

Continue reading “Implementing SmartList Builder: Download and Install”

Implementing SmartList Builder: SmartList Builder vs. SmartList Designer

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

As mentioned in the last post SmartList Builder was directly sold by Microsoft under a licensing agreement for many years until, with the launch of Microsoft Dynamics GP 2013 SP2, the product was released back to the original ISV; a Fargo based ISV called eOne SOlutions.

I understand this was because the new top person in charge of Microsoft Dynamics GP did not like licensing products from the ISVs and release quite a few back to the original developer. However, this left a gap in Microsoft Dynamics GP in that users could no longer create new SmartLists. And so, SmartList Designer was created.

In theory it fulfils the same job as SmartList Builder; you can create new SmartLists by joining tables together or by linking in a SQL view. When it was first released, I took a look at it as an alternative to SmartList Builder on the basis it was free as opposed to needing to buy SmartList Builder.

However, in every area, it is an inferior product. It is not as functional and it is not as easy to use.

Continue reading “Implementing SmartList Builder: SmartList Builder vs. SmartList Designer”

Implementing SmartList Builder: What is SmartList Builder?

eOne SolutionsThis post is part of the series on Implementing SmartList Builder from eOne Solutions.

Before launching into the installation of SmartList Builder, I thought it might be worthwhile to do a post on what it is. All users of Microsoft Dynamics GP will be familiar with SmartList; this is the flexible reporting tool which allows you to generate a report showing a list of master records or transactions. Each series has a set of SmartLists available by default, with predefined favourites where search criteria or columns are supplied already configured.

SmartList favourites can be customised and new ones created quite easily, but new SmartLists themselves cannot be created. To create a new SmareList, you need a tool such as SmartList Builder or, in more recent years, SmartList Designer. In this series, I am focussing on SmartList Builder as this is an additional product available from an ISV for Microsoft Dynamics GP.

Back in 2003 when I started working with Microsoft Dynamics GP, SmartList Builder was available direct from Microsoft. SmartList Builder was originally developed by eOne Solutions and then licensed by Microsoft; in the run up to the release of Microsoft Dynamics GP 2013 SP2, the licensing deal for SmartList Builder was ended and maintenance of the product reverted back to eOne Solutions.

SmartList Builder the product, includes four utilities:

  1. SmartList Builder – SmartList Builder allows you to create both brand new SmartLists or modify existing SmartList. You can link up to 32 tables together. Tables can be standard GP tables, any of the Third Party (ISV) tables, any SQL table, SQL views or SQL Scripts, other SmartLists or Extender resources.
  2. Excel Report Builder – Excel Report Builder generates an Excel Spreadsheet with a live connection back to GP or any other data you include. This means you have live refreshable reports, anytime you need them. You can give all your data from GP to anyone in the organization via Excel – without buying new, full-user licenses of Dynamics GP.
  3. Drill Down Builder – Drill Down Builder completes the functionality of Excel Report Builder by letting you drill from your spreadsheet back into that same record within Dynamics GP. Where Microsoft hard codes a drill down – eOne makes it completely configurable to allow you to drill down to any screen or SmartList in Dynamics GP. This is an essential part of bringing the most common business tools together. Working in GP and Excel all day – it only makes sense to auto-switch between one and the other.
  4. Navigation List Builder – Navigation List Builder allows you to publish your SmartList into the Navigation List user interface.

The standard installation of the SmartList Builder product, gets you all four of the above utilities.

Implementing SmartList Builder: Series Index

eOne SolutionsI’ve posted series on a few different ISV addons for Microsoft Dynamics GP over the last few years, but, somewhat ironically I guess, have not posted all that much about the addin I’ve used the most over the years.

That addin is SmartList Builder from eOne Solutions.

SmartList Builder allows you to create both brand new SmartLists or modify existing SmartList. You can link up to 32 tables together. Tables can be standard GP tables, any of the Third Party (ISV) tables, any SQL table, SQL views or SQL Scripts, other SmartLists or Extender resources.

This series is going to cover the implementation of SmartList Builder; I’ll take a look at report builder in a future series.

Implementing SmartList Builder
What is SmartList Builder?
SmartList Builder vs. SmartList Designer
Register

This Workflow Is Locked By Another User And Cannot Be Edited

Microsoft Dynamics GPI was onsite recently with a client making a Workflow project live and encountered a lock on the workflow we needed to amend. There were no users logged into the company so we knew the lock was an orphaned one.

This workflow is locked by another user and cannot be edited. Please try again later.

"This workflow is locked by another user and cannot be edited. Please try again later."

The locks on Workflow are stored in the Workflow User Security (WF00104) table; when clearing locks, care should be taken to only remove the lock required, so as not to cause additional problems.

The highlighted section should be changed to the Workflow Type for which the lock should be removed:

/*
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).
*/
DELETE FROM
	WF00104
WHERE
	Workflow_Type_Name = 'Purchase Requisition Approval'
GO

SQL Script to Delete Unused Segments

Microsoft Dynamics GPWhile the General Ledger Year-End Close routine can delete unused segments, during implementation, or creation of new companies , we sometimes end up with segments created which are not needed. The below script can be used to remove all segments not assigned to an account (segments which have been used will not be removed).

The script allows the user to define which segment should be removed by changing the highlighted parameter:

/*
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).
*/
DECLARE @SGMTNUMB AS VARCHAR(2) = 3

DELETE FROM
	GL40200
WHERE
	SGMTNUMB = @SGMTNUMB
AND
	SGMNTID NOT IN (
			SELECT
				CASE @SGMTNUMB 
				WHEN 1 THEN GL100.ACTNUMBR_1
				WHEN 2 THEN GL100.ACTNUMBR_2
				WHEN 3 THEN GL100.ACTNUMBR_3
				WHEN 4 THEN GL100.ACTNUMBR_4
				WHEN 5 THEN GL100.ACTNUMBR_5
				WHEN 6 THEN GL100.ACTNUMBR_6
				WHEN 7 THEN GL100.ACTNUMBR_7
				WHEN 8 THEN GL100.ACTNUMBR_8
				WHEN 9 THEN GL100.ACTNUMBR_9
				WHEN 10 THEN GL100.ACTNUMBR_10
				END
			FROM
				GL00105 AS GL105
			INNER JOIN
				GL00100 AS GL100
					ON
						GL100.ACTINDX = GL105.ACTINDX
			)
GO

Find Table in All Microsoft Dynamics GP Databases

Microsoft Dynamics GPI was doing some work with a client recently with a custom extension being tested in some Microsoft Dynamics GP companies. Due to how it is deployed (very manually) we had only deployed it to some databases and not all.

Due to other project commitments, no testing was done for a while and when we returned, we weren’t sure if the deployment notes listed all databases correctly or if some of those databases had been overwritten for other testing.

So, a small script was needed to check for the presence of a custom table in all databases; the below is what I came up with to check for a table in all Microsoft Dynamics GP databases:

/*
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).
*/
DECLARE @command nvarchar(max)

DECLARE @SystemDatabase VARCHAR(15) = 'DYNAMICS'
DECLARE @Table VARCHAR(50) = 'SY00800'
CREATE TABLE #ReturnedData(
	DBNAME VARCHAR(15)
	,TABLENAME VARCHAR(100)
)

SELECT @command = 'IF EXISTS (SELECT 1 FROM sys.databases AS dbs LEFT JOIN ' + @SystemDatabase + '..SY01500 SY ON SY.INTERID = dbs.name WHERE dbs.name = ''?'' AND (dbs.name = ''' + @SystemDatabase + ''' OR SY.INTERID IS NOT NULL))
						BEGIN
							USE [?];
							INSERT INTO #ReturnedData (dbname, tablename) (SELECT DB_NAME() AS ''DB_NAME'', o.name FROM sys.objects AS o WHERE o.name LIKE ''' + @Table + '%'')
						END'

EXEC sp_MSforeachdb @command

SELECT * FROM #ReturnedData

DROP TABLE #ReturnedData

Can I Create a Temp Table With Collation Different to the Server Collation?

Microsoft SQL ServerAs with virtually all headlines containing a question, the short answer is no. From the research I have done, the temp table will always be created with the collation of the SQL Server. If you want to skip to the solution, click here.

Why would you want a different collation? Well, in this case we need a different collation as there is a third party database which a customisation in Microsoft Dynamics GP is using for pricing lookups. The database was originally on a server with the same collation (SQL_Latin1_General_CP1_CI_AS) as the Dynamics GP server (and which the database shares). However, the database has now been moved to a new server with the default UK server collation (Latin1_General_CI_AS).

The problem now is that when data is being inserted or updated into this database, a collation error is produced:

Cannot resolve the collation conflict

[Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal operation.

Code: 8004OE14

Source: Microsoft OLE DB Provider for ODBC Drivers

Continue reading “Can I Create a Temp Table With Collation Different to the Server Collation?”

SQL Script to Insert Vendors

Microsoft Dynamics GPWhile writing a Rockton Software’s SmartFill product recently, I wanted to do a test on a large dataset. The testing of SmartFill I had done to that point had been on vendors, so I deviced to continue in that vein.

I produced the script below which inserts records into the PM Vendor Master File (PM00200) and PM Address MSTR (PM00300) tables bgy copying an existing vendor, but assigning a unique Vendor ID to each new record. For ease of running, I creaed the script as a stored procedure wic accepts the number of records to create, the vendor to be copied and the new name of the vendor which will be created concatenated with an increasing integer.

-- drop stored proc if it exists
IF OBJECT_ID (N'usp_AZRCRV_CreateTestVendors', N'P') IS NOT NULL
    DROP PROCEDURE usp_AZRCRV_CreateTestVendors
GO

-- create stored proc
CREATE PROCEDURE usp_AZRCRV_CreateTestVendors
	@MaxLoop INT = 5
	,@VendorID VARCHAR(15) = 'BEAUMONT0001'
	,@VendorIDPrefix VARCHAR(8) = 'VEND'
AS
	/*
	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).
	*/
	DECLARE @Loop INT = 1

	WHILE (@Loop <= @MaxLoop)
		BEGIN
			-- Insert into PM Vendor Master File (PM00200)
			INSERT INTO PM00200
				(
					VENDORID,VENDNAME,VNDCHKNM,VENDSHNM,VADDCDPR,VADCDPAD,VADCDSFR,VADCDTRO,VNDCLSID,VNDCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHNUMBR1,PHNUMBR2,PHONE3,FAXNUMBR,UPSZONE,SHIPMTHD,TAXSCHID,ACNMVNDR,TXIDNMBR,VENDSTTS,CURNCYID,TXRGNNUM,PARVENID,TRDDISCT,TEN99TYPE,TEN99BOXNUMBER,MINORDER,PYMTRMID,MINPYTYP,MINPYPCT,MINPYDLR,MXIAFVND,MAXINDLR,COMMENT1,COMMENT2,USERDEF1,USERDEF2,CRLMTDLR,PYMNTPRI,KPCALHST,KGLDSTHS,KPERHIST,KPTRXHST,HOLD,PTCSHACF,CREDTLMT,WRITEOFF,MXWOFAMT,SBPPSDED,PPSTAXRT,DXVARNUM,CRTCOMDT,CRTEXPDT,RTOBUTKN,XPDTOBLG,PRSPAYEE,PMAPINDX,PMCSHIDX,PMDAVIDX,PMDTKIDX,PMFINIDX,PMMSCHIX,PMFRTIDX,PMTAXIDX,PMWRTIDX,PMPRCHIX,PMRTNGIX,PMTDSCIX,ACPURIDX,PURPVIDX,NOTEINDX,CHEKBKID,MODIFDT,CREATDDT,RATETPID,Revalue_Vendor,Post_Results_To,FREEONBOARD,GOVCRPID,GOVINDID,DISGRPER,DUEGRPER,DOCFMTID,TaxInvRecvd,USERLANG,WithholdingType,WithholdingFormType,WithholdingEntityType,TaxFileNumMode,BRTHDATE,LaborPmtType,CCode,DECLID,CBVAT,Workflow_Approval_Status,Workflow_Priority,Workflow_Status,VADCD1099,ONEPAYPERVENDINV
				)
			-- VALUES
				(SELECT
					
					@VendorIDPrefix + CAST(@Loop AS VARCHAR(7))
					,VENDNAME,VNDCHKNM,VENDSHNM,VADDCDPR,VADCDPAD,VADCDSFR,VADCDTRO,VNDCLSID,VNDCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,PHNUMBR1,PHNUMBR2,PHONE3,FAXNUMBR,UPSZONE,SHIPMTHD,TAXSCHID,ACNMVNDR,TXIDNMBR,VENDSTTS,CURNCYID,TXRGNNUM,PARVENID,TRDDISCT,TEN99TYPE,TEN99BOXNUMBER,MINORDER,PYMTRMID,MINPYTYP,MINPYPCT,MINPYDLR,MXIAFVND,MAXINDLR,COMMENT1,COMMENT2,USERDEF1,USERDEF2,CRLMTDLR,PYMNTPRI,KPCALHST,KGLDSTHS,KPERHIST,KPTRXHST,HOLD,PTCSHACF,CREDTLMT,WRITEOFF,MXWOFAMT,SBPPSDED,PPSTAXRT,DXVARNUM,CRTCOMDT,CRTEXPDT,RTOBUTKN,XPDTOBLG,PRSPAYEE,PMAPINDX,PMCSHIDX,PMDAVIDX,PMDTKIDX,PMFINIDX,PMMSCHIX,PMFRTIDX,PMTAXIDX,PMWRTIDX,PMPRCHIX,PMRTNGIX,PMTDSCIX,ACPURIDX,PURPVIDX,NOTEINDX,CHEKBKID,MODIFDT,CREATDDT,RATETPID,Revalue_Vendor,Post_Results_To,FREEONBOARD,GOVCRPID,GOVINDID,DISGRPER,DUEGRPER,DOCFMTID,TaxInvRecvd,USERLANG,WithholdingType,WithholdingFormType,WithholdingEntityType,TaxFileNumMode,BRTHDATE,LaborPmtType,CCode,DECLID,CBVAT,Workflow_Approval_Status,Workflow_Priority,Workflow_Status,VADCD1099,ONEPAYPERVENDINV
				FROM
					PM00200
				WHERE
					VENDORID = @VendorID)

			-- Insert into PM Address MSTR (PM00300)
			INSERT INTO PM00300
				(
					VENDORID,ADRSCODE,VNDCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,UPSZONE,PHNUMBR1,PHNUMBR2,PHONE3,FAXNUMBR,SHIPMTHD,TAXSCHID,EmailPOs,POEmailRecipient,EmailPOFormat,FaxPOs,POFaxNumber,FaxPOFormat,CCode,DECLID
				)
			-- VALUES
				(SELECT
					
					@VendorIDPrefix + CAST(@Loop AS VARCHAR(7))
					,ADRSCODE,VNDCNTCT,ADDRESS1,ADDRESS2,ADDRESS3,CITY,STATE,ZIPCODE,COUNTRY,UPSZONE,PHNUMBR1,PHNUMBR2,PHONE3,FAXNUMBR,SHIPMTHD,TAXSCHID,EmailPOs,POEmailRecipient,EmailPOFormat,FaxPOs,POFaxNumber,FaxPOFormat,CCode,DECLID
				FROM
					PM00300
				WHERE
					VENDORID = @VendorID)
			SET @Loop = @Loop + 1
		END
	GO
GO

-- grant execute permission on stored proc to DYNGRP
GRANT EXECUTE ON usp_AZRCRV_CreateTestVendors TO DYNGRP
GO

-- execute stored proc
EXEC usp_AZRCRV_CreateTestVendors 20, 'ADVANCED0001', 'VENDOR'
GO

This script was created for, tested with and only run against the Fabrikam sample database.