Skip to main content
Solved

Inventory Allocation Report


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

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!

Laura

View original
Did this topic help you find an answer to your question?

lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • October 17, 2022

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!

Laura


  • Freshman I
  • October 17, 2022

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

Your suggestion worked perfect!!! Many thanks!


  • Freshman I
  • September 27, 2023

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


Reply


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