Solved

Alpha characters entered into an INT field

W
Rank
Userlevel 4
Badge

Is there a way to “filter” out alphanumeric characters from an INT field? Somehow, our Parent.BAccountID field has aat least four customer numbers, such as AAMISC, C000001, INTMISC, and KCTEST which, when I do a GI linking Parent accounts with Child accounts it continually throws an error message:

I’ve tried filtering out the “bad” customer numbers in various ways without any success. For example and I am only using two of the four for now:

Any help, if it is possible, would be appreciated. I suspect that these were imported into the system because I didn’t think it was possible to do this as a manual entry.

icon

Best answer by darylbowman 9 May 2024, 21:48

View original

14 replies

darylbowman
Rank
Badge +11

It is not possible for a database int field to store a text value. I would guess the error you're receiving is arising from a different problem.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
darylbowman
Rank
Badge +11

Could you post the join conditions you're using for that table?

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
W
Rank
Userlevel 4
Badge

I have tried a left, inner and right joins. Right now I am using left. The Parent.BAccountID is an INT field but there are alpha characters in the field such as AAMISC, C000001, INTMISC, KCTEST which I thought was not possible for an INT field.

The error of the C000001 is popping up. Sometimes it comes up with the KCTEST as the culprit.

W
Rank
Userlevel 4
Badge

The whole idea is to get an invoice history with the Parent data (bill_to_name, bill_to_no, bill_to_city, bill_to_state, bill_to_country etc.) , associated child (ship_to_name, ship_to_no, ship_to_city, ship_to_state, ship_to_country) and other pertinent data such as the for each invoice sent out that day.

 

darylbowman
Rank
Badge +11

Could you post an xml export of the GI?

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
W
Rank
Userlevel 4
Badge

Attached is the XML.

1 Attachment
darylbowman
Rank
Badge +11

Here is a version that should work a bit better.

I can see why the error message you were receiving caused you to come to the conclusions that you did. However, I believe the issue is simply that the tables were badly joined.

Building GIs is a learning curve. Having a background in relational databases definitely helps, but is not necessarily a requirement.

1 Attachment
Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
W
Rank
Userlevel 4
Badge

Sorry, but the import of the XML is throwing an error:

 

W
Rank
Userlevel 4
Badge

I am familiar with SQL joins, but still on a learning curve with the DAC stuff. It’s fun but can kind of hairy at times. I appreciate the help.

darylbowman
Rank
Badge +11

I appreciate the help.

No worries.

What version are you on? I exported this from 23 R2 and it imports fine there. I’ve never seen an error quite like that before.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
darylbowman
Rank
Badge +11

Did you delete the original GI before trying to import? It could be having trouble replacing the existing one.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman
W
Rank
Userlevel 4
Badge

We are on 23 R2 also. I did not delete the original, but will try that. Thank you again.

W
Rank
Userlevel 4
Badge

I downloaded it again and the import worked that time. I’ll play around with it some more. In SQL I used a lot of INNER joins, but Acumatica seems to love the LEFT joins more than anything else. Again, I appreciate the help. Thank you again.

darylbowman
Rank
Badge +11

The concept is the same. Inner joins will only return values matching in both tables. Left will return values matching in the left table and regardless of values matching in the right. For instance, SOOrder LeftJoin SOLine will return all SOOrders, even those without any lines. InnerJoin would only return those with lines.

Daryl ⚡Contract Acumatica Developer (MVP🏆) for hire ⏩ linkedin.com/in/darylbowman

Reply


Terms & ConditionsCookie settings
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