GeneralUser Error When Migrating Management Reporter to a New Server

Microsoft Dynamics GPAcross the years since it was launched I have done a lot of work with Management Reporter, including many migrations to new servers and upgrades which necessitated a migration to a new server or instance of SQL Server, but have never seen this particular error message before.

In this case, I migrated the Management Reporter database to a new server and installed the very latest version of the server software. When I started the database configuration, I received this error message:

Database configuration error

Validation Messages

Database configuration: The connection to the database was successful, but the connection to database 'Management Reporter' failed. Verify that the Management Reporter service account has been added to the General User role in the database.

Continue reading "GeneralUser Error When Migrating Management Reporter to a New Server"

Update Microsoft Dynamics GP Customer Emails on Test Including Sent Emails

Microsoft Dynamics GPAfter creating a new script for updating vendor emails on test which includes the emails of sent items, I figured that a similar script will be needed to update the email address on sent emails in the Sales Series.

The script below, includes the tables holding sent email information for the Sales Series emails (both Receivables Management and Sales Order Processing) as well as the usual Internet Addresses table; the highlighted email address can be changed to whatever email address you're using for testing your Sales emails:

/*
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 @TestEmail VARCHAR(100) SET @TestEmail = 'email@example.com' -- UPDATE existing email addresses UPDATE SY01200 --Internet Addresses (SY01200) SET INET1 = @TestEmail ,EmailToAddress = @TestEmail ,EmailCcAddress = '' ,EmailBccAddress = '' WHERE Master_Type = 'CUS' -- UPDATE Email Details of previously sent emails UPDATE SY04910 --Email Details (SY04910) SET EmailToAddress = @TestEmail ,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END WHERE MODULE1 IN (9,11) -- Receivables Management / Purchase Order Processing -- UPDATE Email Details of previously sent emails UPDATE SY04915 --Email History (SY04915) SET EmailToAddress = @TestEmail ,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END WHERE MODULE1 IN (9,11) -- Receivables Management / Purchase Order Processing GO

Update Microsoft Dynamics GP Vendor Emails on Test Including Sent Emails

Microsoft Dynamics GPAbout 10 years ago I did a post on changing emails on the test system so they didn't go outside the organisation. This has worked well for a long time and no-one has mentioned any issues, until this week when I was working with a client and we made some changes to a remittance format and tested by emailing.

The remittance itself emailed to the test address fine, but the reprint remittance went to the original email address of the vendor used on the live system.

After doing a little exploring I found there were two additional tables which held this data for sent emails and which were used by the reprint remittance function.

The script below, includes the Internet Addresses table as well as these two tables for the Purchasing Series emails (both Purchase Order Processing and Payables Management); the highlighted email address can be changed to whatever email address you're using for testing your Purchasing emails:

/*
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 @TestEmail VARCHAR(100) SET @TestEmail = 'email@example.com' -- UPDATE existing email addresses UPDATE SY01200 --Internet Addresses (SY01200) SET INET1 = @TestEmail ,EmailToAddress = @TestEmail ,EmailCcAddress = '' ,EmailBccAddress = '' WHERE Master_Type = 'VEN' -- UPDATE Email Details of previously sent emails UPDATE SY04910 --Email Details (SY04910) SET EmailToAddress = @TestEmail ,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END WHERE MODULE1 IN (12,19) -- Purchase Order Processing / Payables Management -- UPDATE Email Details of previously sent emails UPDATE SY04915 --Email History (SY04915) SET EmailToAddress = @TestEmail ,EmailCcAddress = CASE WHEN LEN(CAST(EmailCcAddress AS VARCHAR(1000))) = 0 THEN '' ELSE @TestEmail END WHERE MODULE1 IN (12,19) -- Purchase Order Processing / Payables Management GO

Recent ISC Software Webinar: Powerful document generation for Dynamics GP

ISC Software SolutionsIn our most recent webinar, we took a look at Powerful document generation for Dynamics GP. In this webinar, we covered how dox42 can be used to design attractive document templates in Microsoft Office and integrate data from Microsoft Dynamics GP and other systems such as SharePoint or Microsoft 365. If you want to catch up on this, or any other, webinar, you can do so here.

In this blog post, I am going to recap the webinar and show the benefits and some example uses of dox42 for generating documents with data from Dynamics GP and other systems:

  1. Introducing dox42
  2. Examples of how dox42 can be used
  3. Template Design
  4. Integrate data from various sources
  5. Automate your output
  6. Key benefits
  7. Examples from Dynamics GP
  8. Conclusion

Introducing dox42 ^

dox42 are headquartered in Vienna Austria and sell dox42 through a partner channel using companies such as ISC Software. It allows you to generate individualised documents from all your existing systems, including Microsoft Dynamics GP, automatically. Offers, quotes, contracts, inspections or server-reports, presentations, Excel-charts and insurance policies; dox42 is a flexible, powerful and intuitive application which allows you to use the common interface of Microsoft Word, Excel or PowerPoint to design document templates.

Continue reading "Recent ISC Software Webinar: Powerful document generation for Dynamics GP"

Map Folder to a Drive Letter

WindowsAcross the years I have mapped a folder to a drive letter, often to share a drive with an RDP connection where I didn't want to add the entire c drive, but today, when I needed to do it, I completely forgot how, so I am posting this as an aide-mémoire.

In Windows Explorer, right-click on This PC an select Map network drive...:

Click on Map network drive

Continue reading "Map Folder to a Drive Letter"

Simple Audit for Microsoft Dynamics GP: Create Triggers for Audit of Customer Items

Microsoft Dynamics GPThis post as been added as part of the series on creating a simple audit for Microsoft Dynamics GP, but wsn;t part of the original series.

I recently used the simple audit to add an audit to the Sales Customer Item Cross Reference (SOP60300) table to allow a client to keep an audit of changes to customer items. They wanted to keep track of all changes so this means three triggers are required on:

  1. INSERT
  2. UPDATE
  3. DELETE

These triggers will record all customer items which are added, amended or removed. The Customer Items window contains a few fields, but the only ones with sensitive dta which needs to be audited are:

  1. Customer Item Number
  2. Customer Item Description

The first trigger creates the trigger which runs when data is inserted:

/*
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). */
CREATE TRIGGER utr_AZRCRV_SOP60300_AuditUpdate ON SOP60300 AFTER UPDATE AS INSERT INTO ut_AZRCRV_Audit SELECT 'Sales Customer Item Cross Reference' ,CAST(RTRIM(d.ITEMNMBR) AS VARCHAR(30)) + '|' + CAST(RTRIM(d.CUSTNMBR) AS VARCHAR(15)) ,'Update' ,SYSTEM_USER ,GETDATE() ,'Customer Item Number = ' + CAST(RTRIM(d.CUSTITEMNMBR) AS VARCHAR(30)) + ' | ' + 'Customer Item Description = ' + CAST(RTRIM(d.CUSTITEMDESC) AS VARCHAR(30)) ,'Customer Item Number = ' + CAST(RTRIM(i.CUSTITEMNMBR) AS VARCHAR(30)) + ' | ' + 'Customer Item Description = ' + CAST(RTRIM(i.CUSTITEMDESC) AS VARCHAR(30)) FROM deleted AS d LEFT JOIN inserted AS i ON i.ITEMNMBR = d.ITEMNMBR AND i.CUSTNMBR = d.CUSTNMBR GO

Continue reading "Simple Audit for Microsoft Dynamics GP: Create Triggers for Audit of Customer Items"

SQL Stored Procedure to Get the Next Microsoft Dynamics GP Sales Document Number

Microsoft Dynamics GPThis stored procedure can be executed to generate the next sequential sales document number; this script was created to get the next sales invoice number for a transaction to be inserted into Microsoft Dynamics GP through eConnect. I write stored procedures as a wrapper around the eConnect stored procedure as we are often working with the clients IT department or a third party and this abstracts the call way from the other application so any changes by Microsoft can be managed within the wrapper stored procedure rather than the application.

This particular example is for generating a sales order number, but can be used to generate a document number for any of the sales document transactions. To do this, change the first highlighted parameter to one of the following numbers:

  1. Quote
  2. Order
  3. Invoice
  4. Return
  5. Back Order
  6. Fulfillment Order

The second parameter should be set to the Doc ID which will vary depending on how you have configured Sales Order Processing.

The stored procedure to get the next sales document number is:

-- drop stored proc if it exists[/sqlgrey]
IF OBJECT_ID (N'usp_AZRCRV_GetNextSOPDocumentNumber', N'P') IS NOT NULL
	DROP PROCEDURE usp_AZRCRV_GetNextSOPDocumentNumber
GO
-- create stored proc
CREATE PROCEDURE usp_AZRCRV_GetNextSOPDocumentNumber 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 4.0 International (CC BY-NC-SA 4.0 Int). */
BEGIN DECLARE @return_value INT DECLARE @I_tSOPType TINYINT = 2 DECLARE @I_cDOCID CHAR(15) = 'STDORD' DECLARE @I_tInc_Dec TINYINT = 1 DECLARE @O_vSopNumber AS VARCHAR(21) DECLARE @O_iErrorState INT EXEC @return_value = taGetSopNumber @I_tSOPType = @I_tSOPType, @I_cDOCID = @I_cDOCID, @I_tInc_Dec = @I_tInc_Dec, @O_vSopNumber = @O_vSopNumber OUTPUT, @O_iErrorState = @O_iErrorState OUTPUT SELECT @O_vSopNumber END GO -- grant execute permission on stored proc to DYNGRP GRANT EXECUTE ON usp_AZRCRV_GetNextSOPDocumentNumber TO DYNGRP GO

You can execute the stored procedure using the below:

-- execute stored proc
EXEC usp_AZRCRV_GetNextSOPDocumentNumber
GO

I've written similar stored procedures in the past in other next numbers for other parts of Dynamics GP:

I have also posted a custom solution which can be used to generate a next number when there isn't a method available in eConnect.

Excel Snippets: AND/OR Operators

Microsoft ExcelI might not post many Excel snippets, but I'm collecting them into a small Excel Snippets series to make them easy to find.

I've been doing work with quite a few clients with Jet Reports recently and one of the common requirements it to hide a row when values are blank. While the clients I am working with are experienced with Excel, they often are very good with VLOOKUP and other functions, but not so much with AND/OR operators.

This can be done using an IF statement combined with an AND operator. In Excel the and statement format is different to programming/scripting languages.

The below is an example AND checking the value in cell A2 and B2 and returning true if they are both 1 otherwise returning false:

=IF(AND(A2=1,B2=1),"True","False")

Just for completeness, the below is an example OR checking the value in cell A2 and B2 and returning true if they either cell is 1 otherwise returning false:

=IF(OR(A2=1,B2=1),"True","False")

SQL View to Return Sales Invoice Lines

Microsoft Dynamics GPI've been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales invoice lines and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesInvoiceLines', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesInvoiceLines
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesInvoiceLines] 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 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Invoice Number' ,['Sales Transaction Amounts Work'].LNITMSEQ AS 'Line Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMNMBR, ['Sales Transaction Amounts Work'].ITEMNMBR)) AS 'Item Number' ,RTRIM(ISNULL(['Sales Customer Item Cross Reference'].CUSTITEMDESC, ['Sales Transaction Amounts Work'].ITEMDESC)) AS 'Item Description' ,RTRIM(['Item Master'].TCC) AS 'Tax Commodity Code' ,'UK' AS 'Country of Origin' ,RTRIM(['Sales Transaction Amounts Work'].UOFM) AS 'Unit of Measure' ,['Sales Transaction Amounts Work'].QUANTITY AS 'Quantity' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].ORUNTPRC) AS 'Originating Unit Price' ,CONVERT(DECIMAL(10,2),['Sales Transaction Amounts Work'].OREXTCST) AS 'Originating Extended Price' ,RTRIM(['Sales Transaction Amounts Work'].LOCNCODE) AS 'Site' FROM SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100) INNER JOIN SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) ON ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE INNER JOIN IV00101 AS ['Item Master'] -- Item Master (IV00101) ON ['Item Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR LEFT JOIN SOP60300 AS ['Sales Customer Item Cross Reference'] -- Sales Customer Item Cross Reference (SOP60300) ON ['Sales Customer Item Cross Reference'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR AND ['Sales Customer Item Cross Reference'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR WHERE ['Sales Transaction Amounts Work'].SOPTYPE = 3 GO GRANT SELECT ON uv_AZRCRV_SalesInvoiceLines TO DYNGRP GO

[/postcode]

SQL View to Return Sales Invoice Headers

Microsoft Dynamics GPI've been organising a demo of software which can be used to produce documentation using data from Microsoft Dynamics GP and integrate it with data from other systems or network shares. I thought about a few ways of getting the Dynamics GP data and decided that a custom SQL view returning only the required information was the best way to go.

This view returns sales invoice headers and related fields such as the sales user defined.

-- drop view if it exists
IF OBJECT_ID(N'uv_AZRCRV_SalesInvoiceHeaders', N'V') IS NOT NULL
	DROP VIEW uv_AZRCRV_SalesInvoiceHeaders
GO
-- create view
CREATE VIEW [dbo].[uv_AZRCRV_SalesInvoiceHeaders] 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 4.0 International (CC BY-NC-SA 4.0 Int). */
SELECT RTRIM(['Sales Transaction Work'].SOPNUMBE) AS 'Invoice Number' ,['Sales Transaction Work'].DOCID AS 'Invoice Type' ,FORMAT(['Sales Transaction Work'].DOCDATE, 'dd-MM-yyyy') AS 'Document Date' ,FORMAT(['Sales Transaction Work'].INVODATE, 'dd-MM-yyyy') AS 'Invoice Date' ,FORMAT(['Sales Transaction Work'].INVODATE, 'yyyy-MM-dd') AS 'Invoice Date Sortable' ,FORMAT(['Sales Transaction Work'].DUEDATE, 'dd-MM-yyyy') AS 'Due Date' ,FORMAT(['Sales Transaction Work'].ReqShipDate, 'dd-MM-yyyy') AS 'Requested Ship Date' ,RTRIM(['Company Master'].ADRCNTCT) AS 'Shipper Contact Person' ,RTRIM(['Company Master'].ADDRESS1) AS 'Shipper Address 1' ,RTRIM(['Company Master'].ADDRESS2) AS 'Shipper Address 2' ,RTRIM(['Company Master'].ADDRESS3) AS 'Shipper Address 3' ,RTRIM(['Company Master'].CITY) AS 'Shipper City' ,RTRIM(['Company Master'].STATE) AS 'Shipper State' ,RTRIM(['Company Master'].ZIPCODE) AS 'Shipper Zip Code' ,RTRIM(['Company Master'].COUNTY) AS 'Shipper Country' ,RTRIM(['Company Master'].PHONE1) AS 'Shipper Phone 1' ,RTRIM(['Sales Transaction Work'].CUSTNMBR) AS 'Customer Number' ,RTRIM(['Sales Transaction Work'].CUSTNAME) AS 'Customer Name' ,RTRIM(['RM Customer Master'].TXRGNNUM) AS 'Tax Registration Number' ,RTRIM(['Sales Transaction Work'].PRSTADCD) AS 'Ship To Address Code' ,RTRIM(['Sales Transaction Work'].CNTCPRSN) AS 'Contact Person' ,RTRIM(['Sales Transaction Work'].ShipToName) AS 'Ship To Name' ,RTRIM(['Sales Transaction Work'].ADDRESS1) AS 'Address 1' ,RTRIM(['Sales Transaction Work'].ADDRESS2) AS 'Address 2' ,RTRIM(['Sales Transaction Work'].ADDRESS3) AS 'Address 3' ,RTRIM(['Sales Transaction Work'].CITY) AS 'City' ,RTRIM(['Sales Transaction Work'].STATE) AS 'State' ,RTRIM(['Sales Transaction Work'].ZIPCODE) AS 'Zip Code' ,RTRIM(['Sales Transaction Work'].COUNTRY) AS 'Country' ,RTRIM(['Sales Transaction Work'].PHNUMBR1) AS 'Phone 1' ,RTRIM(['Sales Transaction Work'].CURNCYID) AS 'Currency ID' ,( SELECT CONVERT(DECIMAL(10,0), SUM(QUANTITY)) FROM SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) WHERE ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE ) AS 'Number Of Pieces' ,( SELECT CONVERT(DECIMAL(10,2), SUM(QUANTITY) * SUM(['Item Master'].ITEMSHWT)) FROM SOP10200 AS ['Sales Transaction Amounts Work'] -- Sales Transaction Amounts Work (SOP10200) INNER JOIN IV00101 AS ['Item Master'] -- Item Master (IV00101) ON ['Item Master'].ITEMNMBR = ['Sales Transaction Amounts Work'].ITEMNMBR WHERE ['Sales Transaction Amounts Work'].SOPTYPE = ['Sales Transaction Work'].SOPTYPE AND ['Sales Transaction Amounts Work'].SOPNUMBE = ['Sales Transaction Work'].SOPNUMBE ) AS 'Total Weight' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].SUBTOTAL) AS 'Sub Total' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].TAXAMNT) AS 'Tax Amount' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].FRTAMNT) AS 'Freight Amount' ,CONVERT(DECIMAL(10,2),['Sales Transaction Work'].DOCAMNT) AS 'Document Amount' FROM SOP10100 AS ['Sales Transaction Work'] -- Sales Transaction Work (SOP10100) LEFT JOIN RM00101 AS ['RM Customer Master'] -- RM Customer MSTR (RM00101) ON ['RM Customer Master'].CUSTNMBR = ['Sales Transaction Work'].CUSTNMBR LEFT JOIN DYNAMICS..SY01500 AS ['Company Master'] -- Company Master (SY01500) ON ['Company Master'].INTERID = DB_NAME() WHERE ['Sales Transaction Work'].SOPTYPE = 3 GO GRANT SELECT ON uv_AZRCRV_SalesInvoiceHeaders TO DYNGRP GO

[/postcode]