LEFT function on ARTran.InventoryID field returning unexpected results
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?
Page 1 / 1
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:
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.
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.
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.
@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.