Skip to main content
Solved

Custom currency field error: the number does not fit the SQL decimal data type

  • 2 August 2022
  • 7 replies
  • 367 views

I’m struggling to add a new currency field to my SOLine DAC.

 

Errors

When setting the value on any of the SOLines, if the value is >=100, then I get this error.

Customization

Here’s how I added the custom fields: I created two fields, a UsrSalesRepUnitCost and a UsrCurySalesRepUnitCost, with the following attributes in each.

 

The “Database Scripts” section in the customization project window indicates the both of the custom fields are using the `DECIMAL(8, 4)` datatype, which I would assume would allow values as high as 9999.9999.

 

Questions

  1. Why am I getting an error indicating that the value 150.0000 is too large?
  2. Have I properly configured the two custom fields to accurately work for representing currency? I couldn’t find any resources in the documentation about creating custom currency fields.

@clarkmcc Try using the Restart Application action in Apply Update screen to reset everything. You should be able to use the field names you want.

@Dioris Aguilar that solved it, thank you! Where can I go to learn about the proper procedure for removing all traces of a custom field? It would be nice to get rid of the broken fields in case we’d like to use those field names in the future.

@clarkmcc I suggest creating new fields with different names from scratch with the corresponding size (decimal(19,6)) and attributes. Probably there’s some old information cached and the validation is out-dated.

@Dioris Aguilar I’m still seeing the same issue. Interestingly, when I was creating the custom fields again, I apparently didn’t save my changes to the attributes at first, so by default, the attributes for both fields were

-PXDBDecimal]
bPXUIField(DisplayName="SalesRepUnitCost")]

With these attributes, I could store numbers >=100. Once I switched the attributes to the following, I got the same original errors. The underlying SQL datatype was unchanged decimal(19,6). 

UsrSalesRepUnitCost

UsrCurySalesRepUnitCost

 

@clarkmcc Try to remove the new custom columns from database and then republish your project making sure the SQL script are run again (Publish to Multiple Tenant > Execute all database scripts).

@Dioris Aguilar thank you for your reply. Unfortunately, I’m still seeing the same issue. I can confirm that the customization project is published and the new datatype is reflected in the screenshot. 

 

 

@clarkmcc Normally, cost and price fields in Acumatica are defined as decimal(19,6). Try that and see how it goes.

Reply


Terms & ConditionsCookie settings