LEFT function on ARTran.InventoryID field returning unexpected results

  • 16 August 2022
  • 6 replies

Userlevel 5
Badge +1

We use prefixes on our Stock Items to indicate the condition of products. I’m trying to use the LEFT function on the ARTran.InventoryID field in a GI to return the first 3 characters of the InventoryID but it is instead returning 3 numbers.

I’ve tried using all the conversion functions and they all return a string of numbers from the ARTran.InventoryID field.

Any suggestions?


Best answer by Naveen Boga 16 August 2022, 05:16

View original

6 replies

Userlevel 5
Badge +1

Understood. Thank you, all!

Badge +18

@mikeho ,

Good morning!  I realized artran doesn’t hold the field you needed. In hindsight, I guess I should have also said, “join to inventory”.  I hope your Gi has worked out for you.


Userlevel 5
Badge +2

Perhaps it’s not clear from all the posts above. InventoryID is a numerical value being the unique id assigned to the InventoryItem table for each row added to that table (ie. each Item you add to the system). When you display this value in a GI, Acumatica conveniently maps this to the actual alphanumeric code value that you see in the application. This is all well and good and a timesaver until you want to apply functions to the auto-mapped value, which don’t work as expected.

In these cases you need to fetch the InventoryCD value from InventoryItem (as @Naveen Boga and @laura01 demonstrated) and apply your functions to that field.

Userlevel 7
Badge +18

Hi @mikeho  If you wanted to fetch the Inventory ID (Integer value) then you need to join with the InventoryItem table with ARTran and fetch the details like below.




Userlevel 5
Badge +1

Hi @laura01,

There is no InventoryCD option for the ARTran table.


Also, if you look at the ARTran.InventoryID field results it shows the “name” of the item and not the numerical InventoryID number.

Badge +18

Hello, In some tables we have both Inventory ID and Inventory Code. Perhaps you need the field called InventoryCD, shown first (on the left) here:



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