Solved

Inventory Allocation Report

  • 17 October 2022
  • 3 replies
  • 271 views

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,

icon

Best answer by lauraj46 17 October 2022, 18:14

View original

3 replies

Userlevel 7
Badge +7

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

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!

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