Tuesday, December 9, 2014

Fun with eConnect and .NET decimal rounding

By Steve Endow

There are some situations where you need to import transactions into Dynamics GP, where the source data contains line item totals that need to be broken down before being imported into Dynamics GP.

Since that probably didn't make a whole lot of sense, let me give an example.

One of my clients needs to import SOP Invoices.  Due to the nature of their business, the invoice data to be imported into Dynamics GP consists of item number, quantity, and extended price.  There is no unit price in the data.

For example:

Item:  A100
Quantity: 231
Amount:  \$53,981.87

To import the line item using eConnect, you need to calculate the unit price.  If you calculate the unit price for this example, you get \$233.69.  Pretty simple, right?

Well, if you verify the unit price:  231 x \$233.69 = \$53,982.39.  Note that this is \$0.52 more than the actual line amount.

Fortunately, and surprisingly, eConnect (and the Dynamics GP client) is okay with this scenario.  You can import the calculated unit price and the extended price, even though there is a discrepancy due to rounding.

Easy peasy lemon squeezy, right?

Well, if your integration was developed with .NET, there is a caveat.  When you perform the calculation to get the unit price, you will need to round the resulting decimal value.  And this can cause a problem.

Let's use a different example for this one.

Item:  A200
Quantity: 8
Amount:  \$2,089

So to get unit price:  \$2,089 / 8 = \$261.125

And then you dutifully round that value to two decimals with this code:

sopLine.UNITPRCE = Math.Round(lineAmount / lineQuantity, 2);

And you get a unit price of:

\$261.12

Say what?

Every kid knows that .125 should round up to .13, right?

So what in the world is going on?

Well, the .NET Math.Round method has two different "rounding conventions", as this article explains.

The default rounding convention is "To Even".  This means that .125 is rounded to the nearest even value, which is .12.

If you send a quantity of 8, with a unit price of \$261.12, and an extended price of \$2,089, eConnect will return the following error:

Error Number = 722  Stored Procedure= taSopLineIvcInsert  Error Description = Unit Price calculation does not match out to Extended price

It took me a while to figure this out, but eConnect uses "conventional" rounding, and in this case expects \$261.13.

So how do we fix this?  Well, the Math.Round method has a parameter to change the rounding convention.

Math.Round(lineAmount / lineQuantity, 2, MidpointRounding.AwayFromZero);

The MidpointRounding option allows you to tell .NET to use the more conventional rounding method which Microsoft calls "Away From Zero".

This will produce the desired result of a unit price of \$261.13 and eliminate the somewhat perplexing eConnect Error Number 722.

Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics GP Certified IT Professional in Los Angeles.  He is the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.