0

Return first value of a list (NL("FIRST" not applicable)

Jet Reports Historic Posts

June 17, 2009 10:42

None

Follow

Hi,

we sell from blanket order. the order os sales is not related to the date of the contract (Later contracts can be sold first). I would like to find the oldest (datewise) blanket order no. in a preselected selling period. If I use NL(first) I get the first sale entry in th evalue entries which is not necessarily the oldest contract sold).

any ideas?

Thank you,
Gideon Mandel

• Facebook
• Twitter
• LinkedIn

5 comments

• 0
  
  Jet Reports Historic Posts June 17, 2009 11:08

  How about sorting by date first…
  
  =NL("First","Value entry",,"+Posting date",PeriodFilter,…Other Applicable Fields and Filters…)
  
  Does that work?

• 0
  
  Jet Reports Historic Posts June 19, 2009 08:43

  Hi Hans,
  
  I think this solution won't work. your formula sorts value entries by thier posting date. I am looking for the oldest contract used during the month regardles when it was sold during this period.
  
  I will give short example:
  
  I have 4 contracts/blanker orders"
  # 1 dated Jan. 09,
  # 2 dated Feb 09
  # 3 dated Mar 09 and
  #4 dated Apr 09
  
  During May I had two sales: I sold 10 kg from contract #4 on May 1st and on May 5th I sold 10 kg from contract #2.
  
  I am looking for a formula that will return contract number 2 being the oldest contract used in the sales during May. If I sort by posting date the formula will return contract contract #4 which was sold first.
  
  Hope this is clesar.
  
  thank you,
  Gideon

• 0
  
  Jet Reports Historic Posts June 19, 2009 17:11

  Correct me if I'm wrong: from all the sales with contracts during a period you want to know which contract was the oldest. Right?
  
  As I have no idea which tables and fields exactly you are using I will write names here that you will have to translate to your own data set. That's the best I can do:
  
  This will give an array of all the contract that were sold from during a period. Suppose it is in D4.
  =NL("Filter","Value Entry",ContractNoField,"Posting date",Periodfilter … other applicable fields and filters)
  
  This will retrieve the oldest contract no. from the blanket order table:
  =NL("First","Sales header",,"Document type","Blanket order","No.",D4,+ContractDateField,"*")
  
  Does this work?

• 0
  
  Jet Reports Historic Posts June 30, 2009 14:38

  Dear Hans,
  
  I followed your method and created the following two functions:
  
  1) Filter Array in cell E5:
  =NL("Filter","Value Entry","Blanket order no.","item ledger entry type","sale","Posting date","01/05/2009..31/05/2009","+blanket order no.","*")
  
  2) result Function in Cell D5:
  =NL("First","Sales header","No.","+No.",E5,"+document date","*")
  
  It works and does not work… what I mean is that I get a strange result and I am not sure what i am doing wrong.
  
  Initially Excel returns the following number: SO.9.8163. We created a sales order from a blanket order but did not record the sale yet (Pro-forma invoice). The number above happens to be the first existing order no. dated 29/6/2009.
  
  If I then stand on cell D5 and go to function wizard and click on evaluate the window returns the correct sales contract number and it is displayed then as well in cell D5 correctly. However when I change the date filter the result in Cell D5 is again the same order # until I evaluate via the wizard.
  
  Any idea what is going on?
  
  Reg.
  Gideon

• 0
  
  Jet Reports Historic Posts June 30, 2009 17:40

  Gideon,
  
  Anytime you have a NL("Filter"), NL("AllUnique"), or "Link=", referenced in your formulas, the result may not be the correct result until you run the report. All of these functions have internal "scan limits" of the number of records they read while in design mode. When you run the report, the scan limit is removed.