Payables Management Batch Posting Date Disabled When Workflow Active

Microsoft Dynamics GPA client recently logged an issue whereby the Posting Date on the Payables Batch Entry window (Purchasing area page » Transactions » Batches) in some companies:

Payables Batch Entry with Posting Date disabled

When we investigated the issue, it turned out that the field was disabled in those companies which had a Payables Transaction Approval workflow active; I did additional testing and the Payables Batch Approval workflow does not have the same effect.

Continue reading “Payables Management Batch Posting Date Disabled When Workflow Active”

Expiration Dates on Exchange Rates

Microsoft Dynamics GPI’ve been doing some work with a US-based company as they implement Microsoft Dynamics GP in Europe for one of their subsidiaries (we do a fair bit of this type of work for expertise with VAT and Intrastat). One of the issues they asked for assistance with was a problem they had while importing transactions.

All of the transactions which were in the functional currency, imported without a problem, but all of those in other currencies failed.

The cause of the problem was actually quite a simple one, and one I have seen before with other clients. On the Multicurrency Exchange Rate Maintenance window, there is an Expiration date field:

Multicurrency Exchange Rate Maintenance

Continue reading “Expiration Dates on Exchange Rates”

SQL Script To Return Functional Currencies For All Companies Without a Cursor

Microsoft Dynamics GPI posted a script a while ago which used a cursor to return the functional currencies for all companies connected to a system database. However, I have recently revisited this script and created a version which does not use a cursor.

This script has been written to only return the companies which do not have a functional currency set; if you want to see all companies, regardless of the functional currency, remove the highlighted section.

Created by Ian Grieve of azurecurve|Ramblings of a Dynamics GP Consultant (
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
CREATE TABLE #FunctionalCurrencies(

							+ 'SELECT 
								''' + INTERID + '''
								' + INTERID + '.dbo.MC40000
								LEN(FUNLCURR) = 0'
					FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

INSERT INTO #FunctionalCurrencies
	EXEC sys.sp_executesql @SQL

SELECT * FROM #FunctionalCurrencies

DROP TABLE #FunctionalCurrencies

MS Connect Suggestion: Include Multicurrency Vouchers in Select Checks Window

Microsoft Dynamics GPThis suggestion is one from MVP Mariano Gomez to improve the payment run process by allowing multicurrency invoices to be included.

Currently, the payment selection process only allows vouchers issued in the batch currency to be selected and added to the check run. If the user wants to add foreign currency vouchers, he/she needs to use the Edit Checks window to add such vouchers. It would be nice to be able to have an option to include functional currency vouchers in the case of a foreign currency batch, or foreign currency vouchers in the case of a functional currency batch.

This is the use case:

Vendor A Invoice 1 CA$10.00
Vendor A Invoice 2 US$20.00
Vendor A Invoice 3 US$30.00

If a payment selection is performed and the batch currency is CA$, only the first voucher will be selected. To add Invoice 2 and 3, you would have to use the Edit Checks window to include both vouchers.

Conversely, if the batch currency is US$ and the payment selection for vendor A is performed, only invoices 2 and 3 would be selected and you would have to use the Edit Checks window to include Invoice 1.

What Mariano would like to see is all invoices selected (based on selection rules) and let the user deal with the exceptions, as it should be.

As mentioned, all the logic is already there via the Edit Checks window and it would be nice to incorporate as part of the payment selection process. As others have mentioned, having multicurrency vouchers included could be a payables configuration item or simply an additional option in the Select Payments (Select Checks) window.

Mark Polino has also suggested that this functionality be optioned with Rod O’Connor further suggesting the option be on the Select Checks window itself to allow maximum flexibility. Both of these seem like a good addition to Mariano’s idea.

The suggestion can be voted on here.

Management Reporter Error: “The operation could not be completed due to a problem in the data provider framework”

Microsoft Dynamics GPA bit like Integration Manager, Management Reporter often presents highly generic meaningless error messages to users. The error message below was provided to me by a client a while ago (but I have just stumbled across the screenshots again), which I was then able to reproduce just be ruunning the report:

Management Reporter: The operation could not be completed due to a problem in the data provider framework

Management Reporter

The operation could not be completed due to a problem in the data provider framework.

Continue reading “Management Reporter Error: “The operation could not be completed due to a problem in the data provider framework””

SQL Script To Return Functional Currencies For All Companies

Microsoft Dynamics GPI have done a fair bit of work recently for clients with the Web Services for Microsoft Dynamics GP. One of the checks the configuration tool does is to check if all of the companies have a functional currency defined.

However, if it returns a fail flag for this check, it doesn’t actually tell you which company (or companies) has failed the check. And when your client has well over a hundred companies you need an alternative method to going through each one manually.

That alternative method is the script below; it uses a cursor to look through the company databases and returns a list of all companies and their functional currency. To only see those companies without a functional currency, there is a where clause at the bottom which is currently commented out.


DECLARE @SQL_Statement VARCHAR(1000)

	cursor_InterID CURSOR 
	OPEN cursor_InterID


		IF (@@FETCH_STATUS <> -2)
				exec (@SQL_Statement)
				exec (@SQL_Statement)
	CLOSE cursor_InterID



Change Rate Calculation Method To Divide

Microsoft Dynamics GPThis is a script I hope not to need in future, but as I have had to write it, I figured I might as well post it.

When creating an exchange rate table in Multicurrency Exchange Rate Table Setup (Administration » Setup » System » Exchange Table)several fields need to be defined. One of them is the Rate Calculation Method which is set to either Divide or Multiply.

This field can be changed until there are rates entered. The reason this script was created was for a client who create several currencies, entered quite a few rates and then realised that the flag for Rate Calculation Method was set to Multiply instead of Divide.

The choices were either to delete all rates or to change the data behind the scenes; the script took five minutes to write and test, whereas doing the update manually would have taken near an hour.

So script it was. Before I ran the update, I double checked to make sure there were no transactions and also that there was a good backup of the system database.


The yellow highlighted section is the list of currencies to update.

This script should NOT be run when transactions for the currency being updated have been entered.

Integration Manager – An Item With The Same Key Has Already Been Added

Microsoft Dynamics GPI was working with a client the other day with Integration Manager to import some purchase orders when we received the following error on some of the purchase orders:

5. 820909 Insert Failed 0. Seconds
DOC 4 ERROR: An item with the same key has already been added.

The vast majority of the documents integrated without problem. When we examined the documents which failed, we determined that those which failed were those which had a currency of Euro.

Integration Manager errors are typically very unhelpful and this was no different; the first thing we checked was to see if the PO Number existed in GP, but as we expected it did not.

We did some investigating and found that the Euro Exchange Rate’s Transaction Rate Default in GP was defined as Exact Date; I was able to update this to Next Date after which the integration stopped producing errors.

Management Reporter 2012 CU8 Now Available

Microsoft Dynamics GPThe Dynamics Financial Reporting blog today announced the release of Management reporter 2012 CU8.

Full details can be found at the link above, but here’s a quick summary of some of the new features:

  • Dynamic calculated column headers…no more hard coding of Months within the column definition
  • Currency translation for Dynamics GP 2013 using DDM Data Mart provider
  • Ability to create a side by side balance sheet by using a section break option in row definition
  • Option to include line numbers on generated reports
  • Ability to set all columns in definition to Autofit by default
  • Collection of Excel formatting improvements

Management Reporter 2012 CU8 can be downloaded from here for the US and all other countries here (CustomerSource or ParnerSource login required).

Management Reporter 2012 Rollup 2 Released

Microsoft Dynamics GPI typically like to see rollups come out as they tend to fix bugs and add new features but yesterday I advised a customer to install rollup 1 for Management Reporter.

Hopefully, I can catch them first thing in the morning and get them onto rollup 2 instead, of which Sivakumar has just announced the release on the Dynamics Partner Technical Services team blog.

Rollup 2 can be downloaded from here (CustomerSource or PartnerSource login required).

This one adds support for SQL Server 2012 as well as other bug fixes and enhancements to the currency translation.