Solved

LEFT function on ARTran.InventoryID field returning unexpected results

  • 16 August 2022
  • 6 replies
  • 126 views

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?

icon

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

View original

6 replies

Userlevel 7
Badge +17

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 +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.

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.

 

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:

 

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.

Userlevel 5
Badge +1

Understood. Thank you, all!

Reply


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