Solved

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

  • 2 August 2022
  • 7 replies
  • 317 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.
icon

Best answer by Dioris Aguilar 4 August 2022, 01:33

View original

7 replies

Userlevel 5
Badge +2

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

@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.

Userlevel 5
Badge +2

@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.

Userlevel 5
Badge +2

@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 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. 

 

 

Userlevel 5
Badge +2

@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 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]
[PXUIField(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

[PXDBPriceCost()]
[PXDefault(TypeCode.Decimal, "0.0")]

UsrCurySalesRepUnitCost

[PXDBCurrency(typeof(Search<CommonSetup.decPlPrcCst>), typeof(SOLine.curyInfoID), typeof(SOLineExt.usrSalesRepUnitCost))]
[PXUIField(DisplayName = "Sales Rep Unit Cost", Visibility = PXUIVisibility.Dynamic)]
[PXDefault(TypeCode.Decimal, "0.0")]

 

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved