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
Security
Review

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.

Implementing SalesHQ Portal: Conclusion

SalesHQ PortalThis post is part of a series on Implementing SalesHQ Portal, which is one of the GP Elementz Portals from my new employer, ISC Software Solutions.

I realise working for the company who created and sells the Sales HQ portal prevents me from being a neutral reviewer, so I won’t try to offer a conclusion as I have been doing on recent series. Instead I’ll leave you with a quick recap of some salient points:

  • Responsive design which works on desktop, notebook/laptop, tablet and mobile/cell.
  • Portal is hosted on Azure; but no inbound ports need to be opened on your firewall.
  • Salespeople can be assigned multiple customers to look after; customers can be assigned to more than one salesperson.
  • Access for customers to view their account.
  • Salespeople can create prospects or raise quotes/orders/invoices.
  • Free trial connected to your system available so you can try SalesHQ risk free.

Have an enquiry for ISC Software Solutions?

If you’re interested in speaking to ISC Software about consultancy, upgrades, implementation, development, GP Elementz add-ons or portals, or anything else, you can use the form below.





Your Name (required):
Your Email (required):
Subject:
Enquiry message:

Implementing SalesHQ Portal: Using SalesHQ Portal

SalesHQ PortalThis post is part of a series on Implementing SalesHQ Portal, which is one of the GP Elementz Portals from my new employer, ISC Software Solutions.

Over the course of this series, I’ve taken a look at how SalesHQ is implemented. I thought it would be useful to do a post on actually using SalesHQ, but first a reminder that earlier in the series I did a post on What SalesHQ is and what functionality it contains.

I am going to look at Sales HQ from the perspective of a salesperson who has access to a US territory.

When you visit the SalesHQ portal, you will be presented with the login window; this uses the crdentials entered when the salesperson was created:

SalesHQ Login

Continue reading “Implementing SalesHQ Portal: Using SalesHQ Portal”

Implementing SalesHQ Portal: Test SalesHQ Portal

SalesHQ PortalThis post is part of a series on Implementing SalesHQ Portal, which is one of the GP Elementz Portals from my new employer, ISC Software Solutions.

Once I have SalesHQ deployed and salespeople and/or customers with access, I run a quick test to make sure I can log into the portal.

Navigate to the web address you supplied during the implementation (I didn’t use a custom address so have a default one). When the login page loads, enter the email address and password of one of the salesperson accounts you created:

Sales HQ web portal logon screen

Continue reading “Implementing SalesHQ Portal: Test SalesHQ Portal”

Implementing SalesHQ Portal: Customer Setup

SalesHQ PortalThis post is part of a series on Implementing SalesHQ Portal, which is one of the GP Elementz Portals from my new employer, ISC Software Solutions.

With SalesHQ fully deployed, the final step of configuration is to grant customers access sot they can see their account information online. This is done via a window accessible from Customer Maintenance (Sales » Cards » Customer).

To add access for a customer open the Customer Maintenance window, open a customer record and click the Additional button on the action pane and select Manage Customer Accounts:

Customer Maintenance

Continue reading “Implementing SalesHQ Portal: Customer Setup”