0

Comparing 2 fields using whole numbers only

Jet Reports Historic Posts

March 06, 2015 18:56

None

Follow

Hello,

I have a formula that compares the Quantity field with the EDI Original Qty. field and returns only results where the the Quantity is less.

=NL("Sum","Sales Invoice Line","Quantity","No.",$J22,"Posting Date",$E$6,"Sell-to Customer No.",$E22,"Quantity","=""<""&nf(,""EDI Original Qty."")"

Occasionally the difference between the two fields is only a very small amount due to rounding. I'd like to exclude any amounts where the difference is less than 1. Does anyone have any suggestions on how to do this?

Thanks,

Jill

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts March 07, 2015 14:51

  You can use Excel formula's in your NL-fucntion.
  
  Something like this:
  =NL("Sum","Sales Invoice Line","Quantity","No.",$J22,"Posting Date",$E$6,"Sell-to Customer No.",$E22,"=(INT(NF(,""Quantity""))<INT(NF(,""EDI Original Qty."")))","1")Typed from memory, so no syntax checking.
  
  I meant to achieve the following:
  Take the integer part of the quantity field and see if it is less then the integer part of the EDI Original Qty. field.
  That is either true or false (one or zero), so I check for true by using 1 as a filter value.
  
  I'm not sure your version of Excel will use the INT() or INTEGER() function for it.
  You possibly need to localize to your Excel version.
  
  NB.
  Keep in mind that functions like this can take a LOT of computing time, since you basically ask NAV to calculate it for every available record before returning any result.
  
  HTH
  
  rmw

• 0
  
  Jet Reports Historic Posts March 09, 2015 17:49

  Thank you for the idea, but I think I am going to need some help with the syntax. I know Excel and Jet fairly well but in this case I'm finding the combination of the two syntax's quite confusing!
  
  I am using Excel 2010 and I believe that INT is the function I need to use, not INTEGER. I've tried to add the formula you listed below but it does not work, so I tried several variations but so far I have not found one that works. I can't figure out how to wrap the INT formula into the existing Jet formula, and I can't see how your TRUE/FALSE statement fits in either.
  
  Can you give me any further help with the syntax?
  
  Thanks,
  
  Jill

• 0
  
  Jet Reports Historic Posts March 09, 2015 19:34

  Basically what you are trying to do is use an Excel formula within a Jet formula.
  That gets you juggling with quotes :)
  
  Try to write the Excel formula in a separate cell, like G22 (or whatever column is available).
  =(INT(NF($F22,"Quantity"))<INT(NF($F22,"EDI Original Qty.")))
  Where F22 holds
  =NL("Rows", Sales Invoice Line",,"No.",$J22,"Posting Date",$E$6,"Sell-to Customer No.",$E22)
  That should give true or false as a result.
  If that works, you can take that working formula from G22 and put it in a filter field in the Jet formula (that means also removing the key from the NF() and adding the necessary quotes) and use true (or 1) as a filter value.
  
  HTH
  
  rmw

• 0
  
  Jet Reports Historic Posts March 09, 2015 21:31

  I have tried that formula in another cell, but all I get back is a #VALUE error. When I debug the error the message is that 'the record key has been corrupted or damaged'. I don't think it likes using the other function as a key, and I can't figure out how to incorporate this INT into the original formula. Everything I have tried also results in a #VALUE error.

• 0
  
  Jet Reports Historic Posts March 11, 2015 08:07

  I'd use this formula:
  
  =NL("Sum","Sales Invoice Line","Quantity","No.",$J22,"Posting Date",$E$6,"Sell-to Customer No.",$E22,"NUMBER&"&"=nf(,""EDI Original Qty."")-nf(,"Quantity")",">1")
  
  If I've understaood the question right that should filter where the difference between the EDI original qty and the quantity is greater than 1?