“You cannot receive against unauthorised purchase orders.”

Microsoft Dynamics GPI am working with a client at the moment on a large scale roll-out of Purchase Order Processing with a large, complex Workflow approval process. The project started towards the end of the last financial year and into the current one. While users were performing UAT, an issue suddenly arose where a goods receipt notes could not be entered in Receivings Transaction Entry (test). When the user tried, they received the following error:

You cannot receive against unauthorised purchase orders.

Microsoft Dynamics GP

You cannot receive against unauthorised purchase orders.

Continue reading ““You cannot receive against unauthorised purchase orders.””

SQL View to Return Budgets By Month

Microsoft Dynamics GPThe budget functionality in Microsoft Dynamics GP isn’t the strongest with reporting being particularly weak. The ability to report on budgets in Management Reporter does somewhat redeem this area of functionality.

However, the absence of a SmartList Object for budgets is quite a big issue, as SmartList is a very nice flexible reporting tool which the majority of my clients know well. For those with SmartList Builder, it was easy enough to create a SmartList Object for them.

With the introduction of SmartList Designer, we were able to roll out the SmartList budget report to all of the clients who wanted it.

The script is below and returns the budget information with the beginning balance, 12 hard-coded periods and total horizontally across the page.

Continue reading “SQL View to Return Budgets By Month”

Microsoft Dynamics GP Budget Import Issue

Microsoft Dynamics GPBudgets in Microsoft Dynamics GP are easy to maintain using the Budget Wizard. However, we do regularly field calls from clients reporting that the budget import is not working.

The problem reported, was the same as the problem always is; the budget template was not in the correct format.

The first image is the header of the one they were trying to import and the second is the Master budget I exported from GP to show them:

Excel Budget

Excel Budget

As you can see the former budget template has an extra row in the header; this is sufficient to stop the import working.

Everytime we have had this error reported we have found that something was changed in the budget template: a new header row, a header row being deleted, a column being added (the latter is the most common.

The client removed the extra row and was then able to import the budget.

MDGP 2016 R1 Feature of the Day: Budget Import Duplicate Accounts Import Exception Report

I was a little surprised to see Microsoft announce Display Duplicate G/L Accounts on Budget Exception Report as a separate feature of the day as it looks to be part of the Budget Import Exception Report which is a new feature in Dynamics GP 2016 R1 itself:

Budget Import Exception Report

The original feature of showing accounts the budget which don’t exist in Dynamics GP is going to be fabulously well received; this one is a very good addition which I think may not be fully understood by some.

I have had to explain to a few clients that having the same account on the file more than once will result in only the final value ending up in Dynamics GP as it will overwrite the previous entries. If you want to load values in multiple times and have the cumulative values, you need to load multiple budgets and then use the Consolidate Budgets feature.

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

MDGP 2016 R1 Feature of the Day: Budget Import Exception Report

MDGP 2016 R1 Feature of the Day: Budget Import Exception Report

This feature of the day is the production of an exception report during the Budget import. This is going to be a very popular one:

The main features of are:

  • When importing a budget from Excel, an exception report will print displaying accounts that aren’t setup in Dynamics GP

The Budget Wizard is a good tool, but it has always been problematic to identify accounts on the budget upload which didn’t exist. This new feature will make importing budgets far easier now any missing accounts are highlighted.

I have a couple of customers who might be tempted to upgrade just to get this one feature (including one who just upgrade to Dynamics GP 2015 R2.

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

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

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.

Management Reporter Error: The Length Of The Value Is Invalid

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.

Microsoft Dynamics GP 2013 Financial Management Has Been Published

Microsoft Dynamics GPMy second book, Microsoft Dynamics GP 2013 Financial Management has been published and is available now from Packt Publishing.

This is my second book, the first being the Microsoft Dynamics GP 2013 Cookbook, and is the third piece of my work Packt Publishing have released this year (the first being the Microsoft Dynamics GP Techniques Online Leaning Course);

Microsoft Dynamics GP 2013 Financial Management by Ian Grieve

With this book you will:

  • Discover how to improve financial management in Microsoft Dynamics GP 2013
  • Learn the key financial management modules in Microsoft Dynamics GP 2013
  • Improve your abilities with Microsoft Dynamics GP 2013 to gain a better understanding and control of your business

The modules covered in Microsoft Dynamics GP 2013 Financial Management are:

  • Analytical Accounting
  • Cash Flow Management
  • Budgets
  • PO Commitments
  • Encumbrance Management

You can buy Microsoft Dynamics GP 2013 Financial Management from here.

Coming Soon: Microsoft Dynamics GP 2013 Financial Management

Microsoft Dynamics GPWith an expected release date of September 2013, the Microsoft Dynamics GP 2013 Financial Management book is to be released.

This is a brand new book focused on financial management within Microsoft Dynamics GP 2013 by me (I’ve been fairly busy this year);

Microsoft Dynamics GP 2013 Financial Management by Ian Grieve

With this book you will:

  • Discover how to improve financial management in Microsoft Dynamics GP 2013
  • Learn the key financial management modules in Microsoft Dynamics GP 2013
  • Improve your abilities with Microsoft Dynamics GP 2013 to gain a better understanding and control of your business

The modules covered in Microsoft Dynamics GP 2013 Financial Management are:

  • Analytical Accounting
  • Cash Flow Management
  • Budgets
  • PO Commitments
  • Encumbrance Management

This is my second book after the Microsoft Dynamics GP 2013 Cookbook, and you can pre-order the Microsoft Dynamics GP 2013 Financial Management direct from Packt Publishing.

Feature Explained: Combine Budgets

Microsoft Dynamics GPI like how Microsoft Dynamics GP handles budgets and the ease with which they can be exported to, amended and then imported from Excel. However, one of the issues constantly raised by customers in the past was that you could only have one budget and had to include all codes within it. This is problematical when you have several budget holders within your organisation who will have a budget figure to divide up between their account codes.

Well, in Microsoft Dynamics GP 2010, a new feature has been added; you now have the ability to combine several budgets into one. Continue reading “Feature Explained: Combine Budgets”