Skip to main content
Solved

Error message Conversion failed when converting the varchar value 'Joel Darby' to data type int. when using PMProject.OwnerID in a generic Inquiry formula

E
Jr Varsity III
Forum|alt.badge.img

I am trying to replace the Owner ID in a generic Inquiry into the project managers Initials.  I created the formula below: 

=IIf([PMProject.ownerID]='Joel Darby','JD',(IIf([PMProject.ownerID]='Daniel Bandanza','DZ',(IIf([PMProject.ownerID]='Michael McLaughlin','MM',(IIF([PMProject.ownerID]='David Dunn','DD',(IIf([PMProject.ownerID]='Josh Lavallee','JL',(IIf([PMProject.ownerID]='Daniel Mahr','DM',null)))))))))))

I am getting the Error message Conversion failed when converting the varchar value 'Joel Darby' to data type int. when using PMProject.OwnerID in a generic Inquiry formula.  When I just pull the data field It shows up as the full name but the DAC is telling me it’s type int which I am assuming is why I am getting the error message so is it maybe pulling the PKID?  I can’t find anywhere else where I can pull that info as varchar.

Best answer by BenjaminCrisman

​@eucciferri35 It’s because the ownerID is an INT in the database but the UI is converting it to a name so it’s more user friendly, but it’s not a string field. You can try to convert to string using CStr() or replace the name with the actual ID. You could probably use the Replace() function instead as well, but it just comes down to what you’re comfortable with most likely.

Add the field to the GI results like PMProject |  =[PMProject.OwnerID] and you’ll see the actual values as stored in the DB

View original
    cmoreland
    Nilkanth Dipak
    2 people like this
    • cmoreland
      cmoreland
    • Nilkanth Dipak
      Nilkanth Dipak
    Did this topic help you find an answer to your question?

    2 replies

    E
    Jr Varsity III
    Forum|alt.badge.img
    • eucciferri35
    • Author
    • Jr Varsity III
    • 17 replies
    • March 24, 2025

    I figured it out. PMProject.OwnerID_description actually worked. I tried that for something else similar and it didn’t work.  Apparently I just needed to post and I was able to answer my own question. 

    BenjaminCrisman
    Acumatica Employee
    Forum|alt.badge.img+4
    • BenjaminCrisman
    • Acumatica Support Team
    • 711 replies
    • Answer
    • March 24, 2025

    ​@eucciferri35 It’s because the ownerID is an INT in the database but the UI is converting it to a name so it’s more user friendly, but it’s not a string field. You can try to convert to string using CStr() or replace the name with the actual ID. You could probably use the Replace() function instead as well, but it just comes down to what you’re comfortable with most likely.

    Add the field to the GI results like PMProject |  =[PMProject.OwnerID] and you’ll see the actual values as stored in the DB

    E
    Chris Hackett
    2 people like this
    • E
      eucciferri35
    • Chris Hackett
      Chris Hackett

    Reply


    Most helpful members this week

    Terms & ConditionsCookie settings

    Cookie policy

    We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

     
    Cookie 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