Error Registering Table GL_Account_MSTR

● Ian Grieve ●  ● 3 Comments  ● 

Microsoft Dynamics GPWhile ding an upgrade of a client to Microsoft Dynamics GP I suddenly started getting an error when navigating the system; for example, when opening the General Journal Entry window:

Microsoft Dynamics GP: Error Registering Table GL_Account_MSTR

Microsoft Dynamics GP

Error Registering Table GL_Account_MSTR

Continue reading → Error Registering Table GL_Account_MSTR

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , ,  ● Permalink ● Shortlink ●

General Ledger Summary Trial Balance Returning Incorrect Data

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPWe are currently in the process of upgrading a large 20 site client from Microsoft Dynamics GP 9 to a later version (unfortunately not 2015 due to third party add-ons which are not supported on this version). The client previously had standalone implementations at each site, but we are aggregating them into one implementation for ease of maintenance and also to reduce licensing costs.

The first few we centralised went fine, but we then ran into problems when we did the head office companies. When they ran the GL Summary TB on the old and new systems there was a mismatch between them.

After investigating, the support team established that the problem was down to some corruption in the Account Index Master table (GL00105) where some of the accounts had segments separated with dots instead of dashes as defined in Account Format Setup (Administration area page ¯ Setup ¯ Company ¯ Account Format).

The solution they followed was fairly simple, and in three steps:

  1. Run a script to clear the Account Index Master table:

    DELETE FROM GL00105
  2. Run Check Links (Microsoft Dynamics GP menu ¯ Maintenance ¯ Check Links) on Account Master.
  3. Run the General Ledger Reconcile utility (Financials area page ¯ Utilities ¯ Reconcile) on all years starting with the oldest and working forward.

After this was done, the report produced from the upgraded system matched the old one.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

SQL Script To Set Budget Transaction History To Be Kept In All Companies

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPWe recently upgraded a client with well over a hundred companies from Microsoft Dynamics GP 10 to GP 2015; one of the new pieces of functionality they wanted to start using was the Budget Transactions.

One problem is that the keep history checkbox for Budget Transactions in General Ledger Setup (Financials ¯ Setup ¯ General Ledger) is unmarked.

Enabling it for this many companies was going to take a substantial amount of time (even if we generated a macro to do this in automatically) as the client has over 120 companies in Dynamics GP. It is a simple setting in one table which needs to be updated, so instead of using a macro I created a SQL script containing a cursor to loop through all of the databases attached to the system database and enable this setting.

As with all scripts which change data, please make sure you have a good backup of your database (or in this case all of your company databases) before running the script.

DECLARE @IntercompanyID VARCHAR(5)
DECLARE @SQLStatement AS VARCHAR(2000)

DECLARE
	cursor_InterID CURSOR 
FOR 
	SELECT
		INTERID
	FROM
		DYNAMICS..SY01500
	INNER JOIN
		master..sysdatabases
	ON
		name = INTERID
	
	OPEN cursor_InterID

	FETCH NEXT FROM
		cursor_InterID
	INTO
		@IntercompanyID
	WHILE (@@FETCH_STATUS <> -1)
		BEGIN
		IF (@@FETCH_STATUS <> -2)
			SET @SQLStatement = 'UPDATE ' + @IntercompanyID + '..GL40000
								SET KPBUDTRXHIST = 1
								WHERE KPBUDTRXHIST = 0'
			EXEC (@SQLStatement)
			FETCH NEXT FROM
				cursor_InterID
			INTO
				@IntercompanyID
		END
	CLOSE cursor_InterID
DEALLOCATE cursor_InterID

You will need to amend the script if your System Database is not called DYNAMICS.

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 R2 Feature of the Day: Display Debits before Credits

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 has been released and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The twenty second Feature of the Day is . A top suggestion on the MS Connect site, this feature moves the Debit column to display before the Credit column by default in account transaction reporting options in which the Credit column previously displayed before the Debit column.

This applies to SmartLists and Excel Reports, as well as the database tables and views on which the reports are based.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

● Categories: Dynamics, Excel Reports, GP, Microsoft, SmartList ● Tags: , , , , , , , , ,  ● Permalink ● Shortlink ●

New Book On Microsoft Dynamics GP Workflow 2.0 Available Now

● Ian Grieve ●  ● 4 Comments  ● 

Microsoft Dynamics GPMy fourth book is now available to buy in eBook format from azurecure Publishing.

Dynamics GP includes a variety of tools and modules to assist in controlling processes and data; one of the major modules for this was the Dynamics Workflow module. However, this module had major flaws which very much limited its usefulness; it was slow, clunky and difficult to install, configure and maintain.

Microsoft Dynamics GP Workflow 2.0

Continue reading → New Book On Microsoft Dynamics GP Workflow 2.0 Available Now

● Categories: azurecurve Publishing, Dynamics, GP, Microsoft, Publishing, Workflow ● Tags: , , , , , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 R2 Feature of the Day: Combined General Ledger Inquiry

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPMicrosoft Dynamics GP 2015 R2 will soon be released (due late May/early June and, as with previous releases, Microsoft have started a Feature of the Day series on the Inside Microsoft Dynamics GP Team blog. As I have done with the last three series, I’ll be reposting these blog posts here with a little commentary.

The ninth Feature of the Day is Combined General Ledger Inquiry.

In Microsoft Dynamics GP 2015 R2 in General Ledger, the inquiry windows have been consolidated. Now, you can view open and historical information in the same window for Summary Inquiry, Detail Inquiry and for Account Summary.

Any GoTos or drill backs will now go to the single window instead of the user picking open or history.

Combined General Ledger Summary Inquiry

Combined General Ledger Detailed Inquiry

This particular feature is well overdue. It always feels a little embarrassing when doing user training for a new client and having to show them separate enquiry windows for open and history years.

Click to show/hide the MDGP 2015 R2 Feature of the Day Series Index

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Management Reporter Error: The Length Of The Value Is Invalid

● Ian Grieve ●  ● 1 Comment  ● 

Microsoft Dynamics GPA client recently logged an issue with Management Reporter producing an error when they tried generating a report. As is commonly the case, the error in the Queue Status window wasn’t terribly informative, but we did get a more detailed error when looking in the Windows Event Log:

Microsoft.Dynamics.Performance.DataProvider.Core.RequestException: The source system request did not complete successfully. ---> System.ArgumentOutOfRangeException: The length of the value is invalid.  \r\nMinimum supported length: 1 \r\nMaximum supported length: 512
Microsoft.Dynamics.Performance.DataProvider.Core.RequestException: The source system request did not complete successfully. ---> System.ArgumentOutOfRangeException: The length of the value is invalid.  \r\nMinimum supported length: 1 \r\nMaximum supported length: 512

From the error message, we have an item which is the wrong length. I took a shot and guessed that the problem might be down to the budgets as I have seen corruption in client’s systems before which has caused problems with Management Reporter.

One quick select later confirmed that there was a corrupt record in the Budget Master (GL00200) table. I deleted this entry using the below code:


DELETE FROM
	GL00200
WHERE
	BUDGETID = ''

After which the client confirmed Management Reporter was able to generate the report successfully.

● Categories: Dynamics, GP, Management Reporter, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●

Error In SmartList Excluding One Category

● Ian Grieve ●  ● 2 Comments  ● 

Microsoft Dynamics GPA client reported an error to our service desk recently when they were working with an Account Transactions SmartList which they were trying to filter to exclude a particular Account Category. When they entered the search criteria they received a string of error messages.

First:

Microsoft Dynamics GP - GPS Error: 58
Microsoft Dynamics GP

GPS Error: 58

Continue reading → Error In SmartList Excluding One Category

● Categories: Dynamics, GP, Microsoft, SmartList, SmartList Builder ● Tags: , , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 Feature of the Day: Management Reporter Data Integration

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

The thirty third Feature of the Day is Management Reporter Data Integration. In Microsoft Dynamics GP 2015, the user can determine the data that is available to the Microsoft Dynamics Management Reporter DataMart, improving the performance and giving the user control over the data selected.

The user can choose per company (including sample company) if they want the General Ledger data available for reporting.

The user also has the ability to include or exclude Analytical Accounting data for each company:

Company Setup Options

I am slightly puzzled by this feature of the day as you can simply not import a company into Management Reporter if you don;t want to report on it. While I steer clients away from AA where possible, I am not entirely opposed to it, but I do wonder who would want to report on AA without also wanting to be able to report on the GL.

The roles targeted by this feature are:

  • All

Click to show/hide MDGP 2015 Feature of the Day Series Index

● Categories: Analytical Accounting, Dynamics, GP, Management Reporter, Microsoft ● Tags: , , , , , , , , , , ,  ● Permalink ● Shortlink ●

MDGP 2015 Feature of the Day: Intercompany Journal Entry Inquiry

● Ian Grieve ●  ● 0 Comments  ● 

Microsoft Dynamics GPThe Inside Microsoft Dynamics GP blog has started a series of posts on the new features of, the soon to be released, Microsoft Dynamics GP 2015. As I have done for the last two releases, I intend to shadow these posts and add my own opinions on these new feature; the series index is here.

The thirty first Feature of the Day is Void Intercompany Journal Entries. In Microsoft Dynamics GP 2015, one of the improvements to the intercompany functionality is the ability to view the intercompany distributions of a journal entry from another company.

Having the Intercompany Journal Entry Inquiry window eliminates the need for users to switch between companies to view all originating- and destination-company distributions of an intercompany journal entry. All distributions can be viewed from one company.

The Intercompany Journal Entry Inquiry window is launched from the Journal Entry Inquiry (Financial area page ¯ Inquiry ¯ Journal Entry) using the Intercompany button at the bottom of the window:

Journal Entry Inquiry

Continue reading → MDGP 2015 Feature of the Day: Intercompany Journal Entry Inquiry

● Categories: Dynamics, GP, Microsoft ● Tags: , , , , , , , ,  ● Permalink ● Shortlink ●