Skip to main content

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', SPOLine.OpenQty],0)  and  =iif(POLine.LineType]='Goods for IN', oPOLine.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,

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 =kPOLine.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

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

Your suggestion worked perfect!!! Many thanks!

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

Reply


Terms & ConditionsCookie settings