Why do currency and quantity numbers greater than 999 fail to show?

  • Article

David MeegoThere are times when Microsoft Dynamics GP fails to display currency and quantity numbers greater than 999 after they have been saved. 

Examples of this behaviour include:

  • Displaying a previously saved transaction or transaction line and the amounts which were above 999.99 are all now changed and less than 999.99.
  • Entering Inventory pricing settings and receiving errors about quantity breaks and overlapping ranges.

This posting explains the cause of this issue and what the simple resolution is.

The Microsoft Dynamics GP back office application is written in Dexterity. The Dexterity runtime engine communicates with SQL Server via an ODBC (Open Database Connectivity) Data Source.  Dexterity expects the Data Source to behave purely as a pipeline for communication between the application and the database server.  It is important the the ODBC settings do not change or manipulate the data flowing through this connection.

The ODBC Data Source (DSN) settings include options which can cause the data returned from SQL Server to be changed and formatted differently to what is expected by the application.  These options listed below should all be unchecked:

  • Use ANSI quoted identifiers.
  • Use ANSI nulls, paddings and warnings.
  • Perform translation for character data.
  • Use regional settings when outputting currency, numbers, dates and times.

The specific setting that causes the problem discussed in this article is the "Use regional settings when outputting currency, numbers, dates and times." setting.  This setting formats the numbers returned from SQL Server using the control panel's regional settings. 

This would mean that a value such as 999999999999.99999 would be returned formatted as 999,999,999,999.99999.  The application would read the resulting data up to the comma and truncate the rest so that the value would be read as 999.00000.  Another example could be a quantity of 100 items with a Unit Price of 10.00, this would result in an Extended Price of 1000.00 when it was saved.  When it was read back it was 1,000.00 truncated to 1.00.

This setting can have data damaging consequences and can also cause the application to display errors or hang. Please use the steps in the Knowledge Base (KB) Article 870416 (link below) to ensure these options are unchecked.

For more information on how to set up an ODBC Data Source see the following Knowledge Base (KB) Article:

ODBC setup on SQL Server 2005, on SQL Server 2000, and on SQL Server Desktop Engine 2000 (MSDE) (KB 870416) Secure Link

David 

Ref: Content Idea 97716