Skip to main content
Solved

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

  • Mar 22, 2023 11:59 PM UTC
  • 5 replies
  • 1509 views
  • Translate All
michaelho
Semi-Pro III
Forum|alt.badge.img+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.

Best answer by lauraj46

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 

View original
2024 Acumatica MVP

5 replies

lauraj46
Captain II
Forum|alt.badge.img+9
  • lauraj46
  • Captain II
  • Answer
  • Mar 23, 2023 12:08 AM UTC

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 

Translate
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
    Captain II
    Forum|alt.badge.img+20
    • Naveen Boga
    • Captain II
    • Mar 23, 2023 2:46 AM UTC

    @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)
    Translate
    Naveen B - Acumatica MVP 🏆 | Acumatica Developer 🚀 ⏩ https://www.linkedin.com/in/naveenb-acumatica-mvp/
      michaelho
      Semi-Pro III
      Forum|alt.badge.img+1
      • michaelho
      • Author
      • Semi-Pro III
      • Mar 23, 2023 4:58 PM UTC

      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?

      Translate
      2024 Acumatica MVP
      lauraj46
      Captain II
      Forum|alt.badge.img+9
      • lauraj46
      • Captain II
      • Mar 23, 2023 5:04 PM UTC

      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

      Translate
      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*.
        michaelho
        Semi-Pro III
        Forum|alt.badge.img+1
        • michaelho
        • Author
        • Semi-Pro III
        • Mar 23, 2023 5:07 PM UTC

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

        Translate
        2024 Acumatica MVP
          Terms & ConditionsAccessibility statement
          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