0

Quantity on Hand less than NL Sum

Laura Porter

May 28, 2019 20:43

Answered

Follow

Hi I am trying to figure out how to run a filter to check for shortages based on specific orders.  I cannot use quantity on hand < quantity on sales order as I want to filter the sales orders to specific ones based on our field "your reference".  I managed to create this statement, and it runs but it does not populate any fields on my report it just returns "invalid filter' "statement is false" or "statement is true"

What I want is a list of the item numbers on a sales order where the quantity on hand is less than the quantity on sales order - filtered by location and your reference fields.

Here is my statement:

=NL("rows","Stockkeeping Unit","Item No.","Location Code",$C$5,"Quantity on Hand",""<"""NL(""Sum"",""Sales Line"",""Quantity"",""No."",""NF(,""No."")"",""Link="",""Sales Header"",""No."",""=Document No."",""Your Reference"","""&$C$6&""",""Location Code"","""&$C$5&"""))","Link=","Sales Line","Location Code","=Location Code","No.","=Item No.","Type","Item","Document No.",$C$4)

 

Can anyone please help?  This is quite difficult to sort out!

Thanks!

 

 

 

• Facebook
• Twitter
• LinkedIn

2 comments

• 0
  
  Harry Lewis May 30, 2019 15:54

  Hi Laura -

  For my example, I worked with the Item table (the Stockkeeping Unit table in my Cronus DB is empty).

  My report shows all No./Description and QoH where the QoH is less than the sum of the Quantity field (in the Sales Line table) for that No.

  

   

  To do that, I used this function:

  =NL("Rows","Item",,"Location Filter",$C$5,"Quantity on Hand","=""<""&NL(""Sum"",""Sales Line"",""Quantity"",""No."",NF(,""No.""),""LINK="",""Sales Header"",""No."",""=Document No."",""Location Code"","""&$C$5&""")")

   

• 0
  
  Laura Porter May 30, 2019 17:24

  Thanks so much Harry!  I used your statement to fix my syntax - quotes and brackets and a missing "= and an & sign and changed my NF(,No.) to NF,(Item No.) and wouldn't you know it, it runs perfect!  Your a Guru!