0

Return list of sales lines without shipments

Jet Reports Historic Posts

July 17, 2013 13:56

None

Follow

I need to get a list of Sales Lines that haven't had a shipment 'as at' a user specified date

I had thought to do this using an NP("Difference") function in an NL("Rows") like the post below: -
http://community.jetreports.com/viewtopic.php?f=7&t=2694&p=8105&hilit=nl+difference+filter#p8105

Problem I have is that I need to use the 'Order No.' and the 'Order Line No.', however the only examples I can find only ever use one field to link the results.

Quick breakdown
Param1
Dataset 1 - List of Sales Lines with 'Shipment Date' equal to Param1
Dataset 2 - List of Sales Shipments with 'Posting Date' equal to Param1

Result - List of Sales Lines that haven't had a shipment 'as at' Param1


Any help will be greatly appreciated!

Andy

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts July 17, 2013 14:43

  Hi Andy,
  
  So assuming you can get the rest of the solution in the referenced community site posting to work, you COULD use a calculated field to combine the Document No. and Line No. in your NL(Filter) functions like this:
  
  =NL("Filter","Sales Line","=NF(,""Document No."")&""|""&NF(,""Line No."")",…)
  Then after you have the list of document numbers combined with line numbers that you need, you could use something like this to split them back up:
  
  =NL("First",NP("Split",D5,"|")) =NL("Last",NP("Split",D5,"|"))
  Does this make sense?
  
  Regards,
  Hughes

• 0
  
  Jet Reports Historic Posts July 17, 2013 15:26

  Hi fhilton, thanks for the quick response!
  
  Makes perfect sense, I was just stuck on the requirement of a field.
  
  I'll give it a got and let you know how I get one :)
  
  Cheers
  Andy

• 0
  
  Jet Reports Historic Posts July 17, 2013 16:13

  Spen a while producing a response and the timeout on this site just lost me my whole post…. :evil:
  
  I'll try again…
  
  I'm getting an error in the cell with the NP("Difference") etc. When I click Debug I get "An array is not a valid argument for the FilterField parameter of this function."
  
  Have I made a silly mistake, or have I misunderstood something completely?
  
  This is what I have so far, although I should point out I changed the second dataset in my original post to be a filter for Sales Line that have had shipments against them.
  
  Dataset1 - List of Sales Lines
  Dataset2 - List of Sales Lines with shipments (in the date range)
  
  So…
  
  I17 =NL("Filter","Sales Line","=NF(,""Document No."")&""|""&NF(,""Line No."")","Shipment Date","08/02/2013")
  I18 =NL("Filter","Sales Line","=NF(,""Document No."")&""|""&NF(,""Line No."")","Shipment Date","08/02/2013","Link=Sales Line",$J18)
  J18 =NL("Link","Sales Shipment Line",,"Posting Date","08/02/2013","Order No.","=Document No.","Order Line No.","=Line No.")
  I19 =NL("Rows","Sales Line",,NP("Difference",$I17,$18),"=NF(,""Document No."")&""|""&NF(,""Line No."")")
  
  Thanks again
  Andy

• 0
  
  Jet Reports Historic Posts July 17, 2013 17:46

  Hi Andy,
  
  You almost got it right. Try changing your rows function to this:
  
  I19 =NL("Rows",NP("Difference",$I17,$18))
  Then you would have something like this:
  
  J19 =NL("First",NP("Split",I19,"|")) K19 =NL("Last",NP("Split",I19,"|")) L19 =NL(,"Sales Line",,"Document No.",J19,"Line No.",K19)
  Does that work for you?
  
  Regards,
  Hughes