0

Purchase Order Expected Date

Jet Reports Historic Posts

May 21, 2009 06:29

None

Follow

Hi Jet Reporters,

I'm having a bit of trouble with a report that our purchasing department is wanting.
its basically a back-order report (Rows where sales orders Lines have outstanding QTY <>0)
but he wants an expected receipt date on it…which in itself isn't too hard.
But it needs to be a active receipt date.

=NL("Lookup","Purchase Line","Expected Receipt Date","-Type","*","-No.",$U8,"-Variant Code","@@"&$V8,"-Drop Shipment","*","-Location Code","*","-Expected Receipt Date","*")I've put in all the Filter Pairs to keep with the sort Key.
So if a Customer orders 50 of Item A Variant A this morning and the purchasing department run the Back Order report before they have ordered the item,
the reports gets the last order (which could be an order placed & receipted 2 weeks ago)

I can seem to get my head around getting it to show a blank cell if there isn't an active order.

Please help me :?

-Bromy

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts June 12, 2009 14:23

  Another late reaction:
  Try instead of the filter "*" this:
  NL(etc….;"Expected Receipt Date";NP("Eval";"=NP(""Datefilter"";;TODAY())").
  Now I'm thinking of it, TODAY() isn't useful in your case; Expected Receipt Dates can be in the future.
  Instead of TODAY() you should put a date far in the future to cover all your PO's. like "31-12-2999";NP("Datefilter";;"31-12-2999")
  
  This will show all the Orders including the ones without the Expected Receipt Date.
  
  The asterix filter can sometimes give these problems with filtering dates with 0D or empty that's why I use the NP construction.
  
  Hope this will help.
  Greetings
  Hans Dütting

• 0
  
  Jet Reports Historic Posts June 12, 2009 15:07

  Bromy,
  
  Is this being used in an options page? I noticed that you are using NL("Lookup"). NL("Lookup") should only be used in an options page and not in the report page. If you are trying to use this formula in the report worksheet, then you should be use NL("First") instead.

• 0
  
  Jet Reports Historic Posts June 25, 2009 03:46

  sorry for the late reply, I was interstate.
  the formula isn't on an options page it is only there to return a value.
  During my initial Jet training I was informed that I could use either 1, "First", "Lookup"
  and that they all returned the same value
  
  am I doing it wrong??

• 0
  
  Jet Reports Historic Posts June 25, 2009 14:56

  You should never use NL("Lookup") except for the options page when you are creating an options windows.
  
  In fact, if you use NL("Lookup") in place of NL("First"), this will break your reports in Jet Reports 2009.