Returning high precision values in a Smart Object field

  • 28 October 2014
  • 9 replies
  • 3 views
A
Badge +1

Hi,

 

I have a Smart Object created from a SQL Server Service instance.  One of its return fields is of Decimal type.  When a value for this decimal field from the underlying database is too small, I receive the following error when testing the Smart Object in the Tester (the value in the field is 0.00004000):

"Input String was not in a correct format.Couldn't store <4E-05> in Discount column.  Expected type is Decimal."

 

In SQL I have tried casting the output to a float, decimal and numeric - all with higher scales, but none of these seemed to work.  I would like to retain the value as a number rather than converting to a varchar, so in doing this, how can I successfully return this field without the above error occurring?

 

Thanks,

Andy

 

 

 

9 replies

M
Badge +10

Hi Andy,

 

I've done a little digging on this and it would seem the SQL end of things is not the issue.  On the K2 side, the decimal data type doesn't seem willing to accept that long of a decimal number.  If the smartobject is generated using the service tester utility, it "guesses" that decimal is the data type most appropriate for an SQL decimal field, even though there appears to be a disconnect between the valid range of both data types.  K2 has the same behavior for a float variable, casting it as a decimal value, and leading to the same problem.

 

The best suggestion I can make is to do what you described, and use a character or string data type to store that decimal value, and then use the inline function ToDouble (http://help.k2.com/onlinehelp/k2blackpearl/userguide/current/webframe.html#conversion.html) in order to convert it to a type more acceptable in your situation.

 

Regards,

 

Mike

 

Edit:  Maximum precision for a decimal field appears to be 0.000X

A
Badge +1

I'll try the string conversion approach and see how much it affects the rest of the view.

 

Thanks Mike!

M
Badge +10

AndyMal,

 

Trying to figure out the exact conditions this occurs under, can you let me know the precise data type you are using in SQL?  Ex. [ decimal(18,0), decimal(30,10) ]

 

Regards,

 

Mike

A
Badge +1

I'm using numeric(18,2) in SQL.  The field that is giving the issue is a calculated field where both numerator and denominators are numeric(18,2).  This field looks something like this:

 

(CustomerListPrice - SalePrice) / CustomerListPrice.....where CustomerListPrice = 50 000.00 and SalePrice = 49 999.00.

 

As mentioned I've tried extending the decimal point scale of the SQL Type and modifying all the input types to decimal (18,6), but I still get the same smartobject failure.

M
Badge +10

@AndyMal,

 

What version of K2 are you currently running?

 

Regards,

 

Mike

A
Badge +1

4.6.8

M
Badge +10

Andy,

 

Can you please open a support ticket regarding this?  I feel it warrants additional investigation.

 

Regards,

 

Mike

A
Badge +1

I've created ticket #70461.

 

Thanks Mike.

J
Badge +3

Anyone willing to share outcome of that ticket or other conclusive knowledge?

 

I have a similar situation in 4.6.11: backing storage for SmartObject is an MSSQL server table with a float column. Values small enough to need exponential notation  cause the List method to throw an exception from the Service Tester. Error transcription:

  Input string was not in a correct format. Couldn't store <3.3E-12> in Amount Column. Expected type is Decimal. 

  Source: System.Data

  Inner Exception: Input string was not in a correct format.

  Source: mscorlib

 

 

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings