Sage 300 Connector Tool — OrderUniquifier Generating Errors

Summary

You have created a task where the OrderUniquifier object should be auto-generated for Add operations. It is not, and the value seems to be truncated. You are also seeing an error similar to:

Exception occurred during processing of GetByKey Sage 300 object (OEORD):
The value specified for 'OrderUniquifier' could not be converted to Decimal: Input string was not in a correct format.

Note that the above error is for a specific database table, OEORD. You may see a different table name here depending on the task requirements.

This article describes the causes of the error and how to resolve them.

Description

The problem here is two-fold:

Although the OrderUniquifier object is defined as a decimal, the Sage API stores the value as decimal(19,0) in the database, effectively treating it as an integer and truncating it after the decimal point. If BPA Platform generates a value of 123.45, then generates another value of 123.67, when the Sage API tries to store the latter, an error occurs advising of duplicate values.

Also, when the OrderUniquifier is auto-generated, the numbers are generated randomly. This means that the Sage 300 Connector tool has no way of knowing what values exist and which can be used.

Solution

For scenarios where you want the OrderUniquifier auto-generated for Add operations, it is recommended you add a Database Query step (either ODBC or OLEDB) before the Sage 300 step in your task, which queries what OrderUniquifier values are in use already. You can then ensure the value that is auto-generated does not clash with existing OrderUniquifiers.