"error Converting data type varchar to numeric" on GI that is filtering on UDF TRUE checkbox value
After we upgraded from 2019 R2--->2021 R1 we have this error: “Error converting data type varchar to numeric.” pop every time we try to load a GI that is supposed to display sales orders that have a UDF checkbox checked. Anyone have any ideas?
Attached is a screenshot of the trace.
Page 1 / 1
Yes, I have a ticket open with Acumatica but they have been very slow to respond and help. This is very helpful and have shared this with Acumatica on the open ticket. Thanks again for your help!
Hi @gd41,
I can able to replicate this issue and this might the limitation for User Defined fields.
I ran the request profiler and got the below details, hope this helps you to understand the issue with the screenshots.
The reason for the getting this:
When we add a filter settings to the Generic Inquiry, using the User Defined Fields of checkbox type, it is NOT taking as Boolean type and it is taking as Varchar. You can check the below screenshot for “FilterRow” table
Also, since it is a User Defined field, the value will be saved in the “SOOrderKvExt” table, and you can see the value stored in Decimal/Numeric type.
Error: Acumatica is trying to convert the value from Varchar to Numeric, hence getting this issue.
Better you can submit a support case with Acumatica.
Hi @gd41 ,
Oh okay. What are the type of these fields? (all fields are checkboxes at Attributes screen?)
The problem is specifically with UDF checkboxes and filtering on true values in the GI. Thats when the error occurs.
Hi @gd41 Sorry, I was tried in 2020 R2 version.
I worked on the above GI and I can able to execute the GI’s properly without any issues.
Have you enabled Manufacturing module in Enable/Disable screen?
I tried importing them into a test environment, imported without any issues.
Not sure if version matters? 2021 R1?
Thanks for looking into it.
No Luck, still getting the same issue.
Re-exported them….any idea on why there would be an error?
Hi @gd41 Unable to import the above provided xml files and receiving an error like below.
Thanks in advance for looking at this.
It looks like this error with UDF happens when we’re using the filters on the GI.
On the Midopt Order Status GI the error occurs on the following filter names:
FilterName="BLNKT"
FilterName="Confirm(RTS)"
On the Eval and Sample GI it happens on every filter...because they are all looking for TRUE value UDF checkboxes.
XMLs are attached. Thanks again,
Georgy
Hi @gd41
Please provide the XML file for your GI to check further.