MDGP October 2019 Release Feature of the Day: GL Journal Entry Inquiry Enhancements

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series Feature of the Day posts for Microsoft Dynamics GP “October 2019” Release on which I am following and adding commentary. The index for this series of posts is here.

The ninth Feature of the Day is GL Journal Entry Inquiry Enhancements. A number of enhancements have been made to the Journal Entry Inquiry window:

  • The year drop down list will now display in reverse order with the most recent year displayed first. This change has also been included in the following windows:
    • Net Change Inquiry
    • Detail Inquiry
    • Summary Inquiry
    • GL Trial Balance Report options
  • The User who Posted field has been added to the window; this field will display for both open and historical journal entries.
Journal Entry Inquiry

Copy Account Categories Between Microsoft Dynamics GP Companies

Microsoft Dynamics GPWhen implementing Microsoft Dynamics GP for a new client, they usually have very similar setup between companies. While you can use the Professional Services Tools Library tool Company Copy, you sometimes need to replicate data which was configured after this function had been used.

I’ve previously posted scripts to copy segments and financial calendars; today’s script will copy Account Categories to a new company ensuring you have the categories in both companies:

Continue reading “Copy Account Categories Between Microsoft Dynamics GP Companies”

Error Creating Fiscal Calendar In Fabrikam Caused by Audit Trail Codes

Microsoft Dynamics GPI’ve been receiving an error in the Fabrikam Sample Company database when trying to create a new Financial Calendar for a while and finally had the time to track it down.

The error appears when you enter a new year and click the Calculate button:

Error when calculating a new year

Microsoft Dynamics GP
[Microsoft][SQL Server Native Client 11.0][SQL Server[Violation of PRIMARY KEY constraint 'PKSY40100'. Cannot insert duplicate key in object 'dbo.ST40100'. The duplicate key value is (0, 2021, 0, 2 ,General Entry ).

Continue reading “Error Creating Fiscal Calendar In Fabrikam Caused by Audit Trail Codes”

Currency ID Missing From Batches Integrated Via eConnect

Microsoft Dynamics GPWe’ve been doing some work with a client recently creating a number of integrations in SmartConnect. One of these was creating General Journals into a consolidation company from other Microsoft Dynamics GP databases.

Everything looked fine from a SmartConnect perspective; success reported for the integration and no warnings or errors. However, when we looked at the resulting journal, there was no Currency ID:

General Transaction Entry showing blank Currency ID field

The problem here isn’t actually a SmartConnect one, but an eConnect one which has been known for a long time. There are a number of reports of this, but the one we found when researching was on the Dynamics Blogger.

The solution is to create a chequebook (checkbook) for the Functional Currency.

The company we were importing into, didn’t have one as only the GL was going to be used; once the chequebook was created, the integration worked fine.

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

SQL View to Report on Fixed Allocation Accounts

Microsoft Dynamics GPIf you are using Fixed Allocation Accounts in Microsoft Dynamics GP, there is only a standard report which shows the distribution accounts against one of the accounts. These reports are not very user friendly and can;t be exported to Microsoft Excel in usable way.

Below is a SQL View which can be added to a reporting tool such as SmartList Designer, or a refreshable Excel report, which will allow users to see how Fixed Allocation accounts have been setup.

-- drop view if it exists
IF OBJECT_ID (N'uv_AZRCRV_FixedAllocationAccounts', N'V') IS NOT NULL
    DROP VIEW uv_AZRCRV_FixedAllocationAccounts
GO
-- create view
CREATE VIEW uv_AZRCRV_FixedAllocationAccounts 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).
*/
SELECT
	['Account Index Master - Fixed Allocation'].ACTNUMST AS 'Account Number'
	,['Account Master - Fixed Allocation'].ACTDESCR AS 'Account Description'
	,CAST(['Fixed Allocation Master'].PRCNTAGE AS NUMERIC(15,2)) AS 'Distribution Percentage'
	,['Account Index Master - Fixed Allocation Distribution'].ACTNUMST AS 'Distribution Account Number'
	,['Account Master - Fixed Allocation Distribution'].ACTDESCR AS 'Distribution Account Description'
	,CASE WHEN ['Account Master - Fixed Allocation Distribution'].ACTIVE = 1 THEN 'Yes' ELSE 'No' END AS 'Distribution Account Active'
	,['Account Category Master'].ACCATDSC AS 'Distribution Account Category Description'
	,CASE WHEN ['Account Master - Fixed Allocation Distribution'].PSTNGTYP = 1 THEN 'Profit & Loss' ELSE 'Balance Sheet' END AS 'Distribution Account Posting Type'
	,['Account Master - Fixed Allocation Distribution'].USERDEF1 AS 'Distribution Account User-Defined 1'
	,['Account Master - Fixed Allocation Distribution'].USERDEF2 AS 'Distribution Account User-Defined 2'
	,['Account Master - Fixed Allocation Distribution'].USRDEFS1 AS 'Distribution Account User-Defined 3'
	,['Account Master - Fixed Allocation Distribution'].USRDEFS2 AS 'Distribution Account User-Defined 4'
FROM
	GL00103 AS ['Fixed Allocation Master'] WITH (NOLOCK)
INNER JOIN
	GL00105 AS ['Account Index Master - Fixed Allocation'] WITH (NOLOCK)
		ON
			['Account Index Master - Fixed Allocation'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
INNER JOIN
	GL00100 AS ['Account Master - Fixed Allocation'] WITH (NOLOCK)
		ON
			['Account Master - Fixed Allocation'].ACTINDX = ['Fixed Allocation Master'].ACTINDX
INNER JOIN
	GL00105 AS ['Account Index Master - Fixed Allocation Distribution'] WITH (NOLOCK)
		ON
			['Account Index Master - Fixed Allocation Distribution'].ACTINDX = ['Fixed Allocation Master'].DSTINDX
INNER JOIN
	GL00100 AS ['Account Master - Fixed Allocation Distribution'] WITH (NOLOCK)
		ON
			['Account Master - Fixed Allocation Distribution'].ACTINDX = ['Fixed Allocation Master'].DSTINDX
INNER JOIN
	GL00102 AS ['Account Category Master'] WITH (NOLOCK)
		ON
			['Account Category Master'].ACCATNUM = ['Account Master - Fixed Allocation Distribution'].ACCATNUM
GO
-- grant permissions to view
GRANT SELECT ON uv_AZRCRV_FixedAllocationAccounts TO DYNGRP
GO

Create SmartConnect Journal – Standard Map: Conclusion

eOne SolutionsThis post is part of a series on creating a Journal – Standard integration using SmartConnect from eOne Solutions; I recently posted a series on Implementing SmartConnect.

Over the last few posts in this series, I have shown how to create a simple General Journal integration.

The process is slightly more involved than creating a similar integration, but the huge advantage is being able to schedule the integration to automatically run and select all files from a folder.

With Integration Manager, to allow multiple people to use the same integration you had to point it at a shared drive letter which all users had in common (and often not all did, due to being in different locations).

In addition, SmartConnect can also process the Excel spreadsheet directly, without requiring the user to save the active page down as a CSV; something which Excel really doesn’t want you to do.

The other main benefit of SmartConnect is that if there is an eConnect node available, it can integrate that type of record into Microsoft Dynamics GP, whereas Integration Manager could only use the ones available via the standard or eConnect adapters.

eOne Solutions also have a product called Node Builder which allows additional eConnect nodes to easily be created without the need for a developer. I’ll be taking a look at NodeBuilder in a future series.

Create SmartConnect Journal – Standard Map: Could Not Run The Scheduled Map

eOne SolutionsThis post is part of a series on creating a Journal – Standard integration using SmartConnect from eOne Solutions; I recently posted a series on Implementing SmartConnect.

The final error I received while creating the Journal – Standard map was when I scheduled the map to run automatically:

Windows Event Viewer showing error

JOURNALSTANDARD : SmartConnect Scheduler : Could not run the scheduled map You do not have access to the connectors required for this map.

Continue reading “Create SmartConnect Journal – Standard Map: Could Not Run The Scheduled Map”

Create SmartConnect Journal – Standard Map: Credit Amount Was Not Supplied Error

eOne SolutionsThis post is part of a series on creating a Journal – Standard integration using SmartConnect from eOne Solutions; I recently posted a series on Implementing SmartConnect.

While I was creating the Journal – Standard for this series, it was not all smooth sailing. I received an error message while testing the integration:

SmartConnect Progess window showing error

Continue reading “Create SmartConnect Journal – Standard Map: Credit Amount Was Not Supplied Error”

Create SmartConnect Journal – Standard Map: Connection Could Not be Validated Error

eOne SolutionsThis post is part of a series on creating a Journal – Standard integration using SmartConnect from eOne Solutions; I recently posted a series on Implementing SmartConnect.

When I was creating the Journal – Standard for a client recently, I encountered a problem when trying to add a Key Field in the Map Setup window:

Connection could not be validated error

SmartConnect

Connection could not be validated

ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

This integration polls for Excel spreadsheets in a certain folder; it took me a moment to realise that the server I was working on was a new one and did not have Microsoft Excel installed.

I asked the client of they could install it for me and, once they had done so, the error disappeared and I was able to add the key fields without further problems.