0

How do I compare two fields from the same table

Jet Reports Historic Posts

October 28, 2008 20:19

None

Follow

I am trying to count the number of units in a non-navision database.

I am using a table of INCOMING, and I want to count the items within a date range. This is fine and I have this currently.

But I want to only count units where the PACKTECH is not equal to the PACKINSPECTOR. How can I write this formula where it will show a one cell total. Currently I have.

=NL("count","Incoming","PackDate","PackDate","01/01/08..01/31/08",,,,,,,,,,,,,,,,,"DataSource=","TV Tracking")

Thanks,

Brad

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Jet Reports Historic Posts October 29, 2008 10:20

  Hi Chris,
  
  Here's a Jet Reports fucntion I used a couple of years ago, which I converted to your function:
  =NL("Count","Incoming","PackDate","PackDate","=CONCATENATE(""<>"",TEXT(NF(,""PackInspector""),""mm/dd/yyyy""))")
  Nowadays I use a different method for NAV which is based on marking records. But I'm not sure if you can do that in the Universal connector.
  
  Please let us know if this worked for you.

• 0
  
  Jet Reports Historic Posts October 29, 2008 14:24

  I might have complicated this too much.
  
  I am trying to count the number of units where a PackTech (a four digit ID ex. 1256) is not equal to the PackInspector (a different four digit ID ex. 1481). I tried to use the formula above as an example, but could not get it to work.
  
  So the base formula would be
  
  =NL("count","Incoming","PackDate",,,,,,,,,,,,,,,,,,,"DataSource=","TV Tracking
  
  How do I add a filter for one field "PackTech" to not be equal to another field "PackInspector"
  
  I might have to open a ticket, but I thought other people might be interested.

• 0
  
  Jet Reports Historic Posts October 29, 2008 19:26

  Aha! I thought it was a date type of field.
  
  Here's a Jet Reports fucntion I used a couple of years ago, which I converted to your function:
  
  How about…
  =NL("Count","Incoming","PackDate,"PackTech","=CONCATENATE(""<>"",NF(,""PackInspector""))")

• 0
  
  Jet Reports Historic Posts October 29, 2008 21:32

  I am only getting an error message. Is this referenced anyplace in an official Jet Manual? I don't see this in mine.
  
  Any other solutions are welcome too.

• 0
  
  Jet Reports Historic Posts October 30, 2008 12:02

  What kind of error? And maybe you can upload the report?

• 0
  
  Jet Reports Historic Posts November 28, 2008 13:39

  Hi hansfousert,
  
  What do you mean by "…marking records". Can you give an example?
  
  Thank you very much.
  PSmile
  
  

  Hi Chris,
  
  Here's a Jet Reports fucntion I used a couple of years ago, which I converted to your function:
  =NL("Count","Incoming","PackDate","PackDate","=CONCATENATE(""<>"",TEXT(NF(,""PackInspector""),""mm/dd/yyyy""))")
  Nowadays I use a different method for NAV which is based on marking records. But I'm not sure if you can do that in the Universal connector.
  
  Please let us know if this worked for you.

• 0
  
  Jet Reports Historic Posts December 01, 2008 09:02

  Hi
  
  I am using Excel 2003 and Jet report 7.1.2 - howeve, this "concatenate" does not seem to work here - are anything special with function? any other add ons, that i must have?
  
  in advance, Thanks!!
  
  /Lars