Inventory Allocation Report

  • 17 October 2022
  • 3 replies

Userlevel 1

I am trying to design a GI that will show me allocations for stock items that we have on Open PO (open qty). I want to know for all open PO quantities, how much is designated/linked to a SO and how much is for “stock”. The report is functioning properly and is showing me what I want, however, I would like to have separate columns for each PO Line to show if it is meant for Stock of SO (if Line type is Goods for IN, I want the open qty to show in my column Stock Inventory...and if Line Type is Goods for SO, then I want the open qty to show in my Inventory for SO column). The logic I am using on my GI is =iif([POLine.LineType]='Goods for SO', [POLine.OpenQty],0)  and  =iif([POLine.LineType]='Goods for IN', [POLine.OpenQty],0). However, all data being returned is 0.

In the example below the first line of my report should show 7 in the “Stock Inventory” column and for the 2nd line I have highlighted, you should see 5 in the “Inventory for SO” column, but for some reason the formula I am using does not seem to function properly. 

Does anyone have any insight on what might be wrong with the formula I am using? I suspect it has something to do with the value I am inserting for Line Type?


Thank you,


Best answer by lauraj46 17 October 2022, 18:14

View original

3 replies

Userlevel 7
Badge +8

Hi @Amandab ,

The POLine.LineType field is actually a 2 letter code.  You can see this by looking at the DAC Browser.  When you display the field on a GI it uses the schema to show the full description, but in formulas you need to reference the code.

A trick to find the code is to use a formula like =[POLine.LineType] in the GI field list.

You can group on the LineType and should get output similar to this.  Based on this, I would say that GI and GS are the codes you need.

Hope this helps!


Userlevel 1

Thank you so much. This is a very helpful trick because I struggle with those specific codes sometimes.

Your suggestion worked perfect!!! Many thanks!

Userlevel 1

Attached is a copy of the report in its final form. I had a request to share this!


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