0

NL Statement Runs for 1 Sec Produces no Results - but does not error

Laura Porter

August 03, 2018 17:38

Answered

Follow

Hi I am new to Jet and had some help constructing this statement - not sure if there is a cell reference format error or what.  I am trying to find Item No.'s for a vendor (option) where the sum of quantity sold is <= (user option) for a certain date range (user option).

My statement runs for 1 sec and produces no results when I know there are items with these parameters.

NL("rows","item","No.","Vendor No.","$C$4","+=NL(""sum"",""Item Ledger Entry"",""Quantity"",""Entry Type"",""Sale"",""Posting Date"", ""$C$6"",""Item No."",NL(""Filter"",""Item"",""No."",""vendor No."",NF(,""vendor No."")))",$C$7)

Can anyone see what's wrong with my statement?  Any help would be appreciated!

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Harry Lewis August 03, 2018 18:41

  Hi Laura -

  There is a minor issue with the reference to cells C4 and C6.

  As you can see from this screen shot, C7 is recognized as a cell reference, but cells C4 and C6 are not:

  

  Because the NL(Sum) function is contained within quotes, it takes a little manipulation to include a cell reference.

  Try this:

  =NL("rows","item","No.","Vendor No.",$C$4,"+=NL(""sum"",""Item Ledger Entry"",""Quantity"",""Entry Type"",""Sale"",""Posting Date"", """"&$C$6&"""",""Item No."",NL(""Filter"",""Item"",""No."",""vendor No."",""@@""&NF(,""vendor No."")))",$C$7)

  I also removed the quotes from around the reference to $C$4 (I assume that "$C$4" is supposed to be a cell reference, not a vendor number :-)

  I also prefixed the NF function with the @@ notation to avoid an issue if any record in the Item table has a blank Vendor No. field.

  When I run this function, I get a list of Item numbers.

  Edited by Harry Lewis August 03, 2018 18:41
• 0
  
  Laura Porter August 03, 2018 19:08

  Thanks so much!  It is definitely running now! Trying to learn as fast as I can - really enjoy the Jet Program!

• 0
  
  Laura Porter August 03, 2018 19:35

  One more question... because item ledger entry is in negatives i am trying to use >=(-50) (without the brackets) as my option number but the statement is returning all items with all quantities.  I changed the entry type to document type sales shipment and it is not producing the desired results - which is sales under 50 for a date range.  Any idea why?

• 0
  
  Harry Lewis August 03, 2018 20:18

  Yep.

  Here's the deal.  You know how that NL(Sum) function has to be inside quotation marks?  That means that Excel treats the results as TEXT... not a number.  So, we have to force things, a bit.

  Try something like this:

  =NL("rows","item","No.","Vendor No.",$C$4,"+=NL(""sum"",""Item Ledger Entry"",""Quantity"",""Entry Type"",""Sale"",""Posting Date"", """"&$C$6&"""",""Item No."",NL(""Filter"",""Item"",""No."",""vendor No."",""@@""&NF(,""vendor No."")))","NUMBER&>="&$C$7)

  

   

• 0
  
  Harry Lewis August 03, 2018 20:19

  For future reference, there is some discussion of that in the Sort By Sum for NAV article.

• 0
  
  Laura Porter August 03, 2018 20:41

  I thought it was syntax to do with the integer... but unfortunately it is still producing all items with all results :(  Not sure where to go from here.

• 0
  
  Laura Porter August 03, 2018 20:45

  Sorry..my bad... had the < sign instead of the > sign!  Running Correctly now!  Thanks for all your help!