Solved

Converting String Attribute to Decimal in a GI

  • 18 November 2021
  • 7 replies
  • 74 views

Userlevel 1
Badge

I am trying to convert an attribute on the customer record to a double

when it appears on a GI so that the data can be sorted with it. I have tried using the formula editor and using the convert formulas, but the don’t seem to work. I get an error saying “Error: The virtual field SALES2020_Attributes cannot be used in a condition expression.”, SALES2020_Attributes being the name of the text attribute. 

This is what the formula looks like in the formula editor:
 

Any help would be appreciated! 

icon

Best answer by Naveen B 26 November 2021, 02:24

View original

7 replies

Userlevel 7
Badge +10

Hi @eleanorp  If possible, can you please share the GI here, I can take a look from my end.

Userlevel 1
Badge

Thank you for helping me (again xD)

The three attributes I’d like to convert are SALES2019_Attributes, SALES2020_Attributes, and SALES2021_Attributes. I just changed one as I had to change it back so the team can still use it as is. Thank you again and let me know if you find anything! 

Userlevel 7
Badge +10

Hi @eleanorp  I just verified this GI, SALES2020_Attributes is not a bound field and we can call it as unbound/non-persist/Virtual field.

For the Virtual fields, we can not use the expressions and will get the errors. To more details about this, here is the article for you - https://asiablog.acumatica.com/2017/09/database-mapped-vs-virtual-fields.html

To covert to decimals we need to use the Bound fields, and we can achieve this requirement like below and hope that helps.

  • You need to join with CSAnswers table, where the bound field exist with the value
  • Use the decimal conversion for the Value field.
  • Since you have multiple attributes, you need to create join with CSAnswers for the each Attribute ID, since you have multiple attributes.

Here are the screenshots for your reference.

 

GI Modifications:

 

 

Userlevel 1
Badge

Hello, 

 

Thanks for the article and the explanation! I tried it out and it only seems to show up blank. I attached a copy. Any idea what I missed? Thank you so much! 

Userlevel 7
Badge +10

Hi Elenorp,

Yesterday only I came to know that we can make it allow only numbers even it is TEXT attribute by providing the Mask in for the respective attribute. This is simple change and you can avoid CSAnswers table joins.

Hoping that this is right solution for you.

 

 

Userlevel 5
Badge +4

@Naveen B It is nice that we have this Entry Mask feature in the Attributes screen. 

Userlevel 7
Badge +10

Yes @ChandrasekharM  Already provided the update to @eleanorp  to implement the same.

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 — 2020  Acumatica, Inc. All rights reserved