Solved

"Conversion failed when converting the varchar value to data type int." error

  • 23 March 2023
  • 5 replies
  • 455 views
M
Rank
Userlevel 5
Badge +1

I am trying to build a GI using the INItemSite table to display the InventoryID and the MinQty designated for each warehouse. I’m trying to use the following formula to limit to the MinQty from a specific warehouse and will duplicate several times for each warehouse (grouping the results by InventoryID):

=IIf([INItemSite.SiteID]='Warehouse-01',[INItemSite.MinQty],0)

However, when I try to view the results I receive the following error message:

Conversion failed when converting the varchar value 'Warehouse-01' to data type int.

 

Anyone have any suggestions around this? I would imagine this error means the INItemSite.SiteID field does not actually contain the text data but is actually a numeric value assigned to that SiteID, but I can’t figure out how to fix this GI. Seems fairly straightforward.

icon

Best answer by lauraj46 23 March 2023, 01:08

View original

5 replies

lauraj46
Rank
Userlevel 7
Badge +7

Hi @mikeho ,

The Site field is an integer.  If you need to reference the human readable code, add INSite to your GI and use INSite.SiteCD in your formula.

Laura 

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
Naveen Boga
Rank
Userlevel 7
Badge +17

@mikeho  As indicated by Laura, you can join the INSite with INItemSite with SiteID field.

 and check like below

 

IIf([INSite.SiteCD]='Warehouse-01',[INItemSite.MinQty],0)
Naveen B
M
Rank
Userlevel 5
Badge +1

Thank you @lauraj46 and @Naveen Boga! Now to throw a larger wrench into the works…

I need to start from a list of all the inventory items (InventoryItem table) and was going to connect the INItemSite table to that table via the InventoryID field. However, it would seem that the INSite table would need to sit in between these two tables, but I can’t figure out the best way to do this. Any suggestions?

lauraj46
Rank
Userlevel 7
Badge +7

Hi @mikeho ,

No need for it to go in the middle, you should be able to join InventoryItem --> InItemSite and  InItemSite -->INSite.

Hope that helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
M
Rank
Userlevel 5
Badge +1

Guess I was just making it more complicated than it needed to be. Thanks, @lauraj46! That worked fine.

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