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 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")]

 

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.

@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 Try using the Restart Application action in Apply Update screen to reset everything. You should be able to use the field names you want.

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