0

NL(last) not returning the last record

Jet Reports Historic Posts

May 18, 2016 17:21

None

Follow

I have searched on here and read the answers provided, but I don't seem able to make them work for me.

I'm trying to get the last vendor we purchased a specific item from. I have the list of items, but I can't seem to get the last record. I've entered the -/+ posting date every way I can think of.

This is what I've used so far.
=NL("LAST","ITEM LEDGER ENTRY","SOURCE NO.","Document Type","Purchase Receipt","Item No.",B3)

Any help would be greatly appreciated.

Rachel

• Facebook
• Twitter
• LinkedIn

9 comments

• 
  Jet Reports Historic Posts May 20, 2016 15:24 Official comment

  I can't believe it, I forgot to refresh my data! It works now. I'm so sorry for wasting your time. Thank you for your help though. My original worked:
  
  NL("LAST","ITEM LEDGER ENTRY","SOURCE NO.","+Posting Date","*","Document Type","Purchase Receipt","Item No.",B3)
  
  Again, thank you!
  
  Rachel

• 0
  
  Jet Reports Historic Posts May 18, 2016 19:30

  Have you tried…
  
  =NL("LAST","ITEM LEDGER ENTRY","SOURCE NO.","+Posting Date","*","Document Type","Purchase Receipt","Item No.",B3)

• 0
  
  Jet Reports Historic Posts May 18, 2016 19:52

  Yes I have, and no it still didn't work. :(

• 0
  
  Jet Reports Historic Posts May 18, 2016 21:16

  What does that function return?

• 0
  
  Jet Reports Historic Posts May 18, 2016 22:01

  The first record.

• 0
  
  Jet Reports Historic Posts May 19, 2016 16:28

  If you use this function:
  
  =NL("FIRST","ITEM LEDGER ENTRY","SOURCE NO.","+Posting Date","*","Document Type","Purchase Receipt","Item No.",B3)
  
  or this function:
  
  =NL("LAST","ITEM LEDGER ENTRY","SOURCE NO.","-Posting Date","*","Document Type","Purchase Receipt","Item No.",B3)
  
  What is returned?
  
  Also, what is returned by this:
  
  =NL("COUNT","ITEM LEDGER ENTRY","SOURCE NO.","Document Type","Purchase Receipt","Item No.",B3)

• 0
  
  Jet Reports Historic Posts May 20, 2016 13:47

  The first 2 functions return the first record. The third functions returns the number 45.

• 0
  
  Jet Reports Historic Posts May 20, 2016 15:01

  OK.
  
  Let's take a look at this.
  
  I've put together a rudimentary report that simply uses the functions we've already mentioned and also generates a list of all the matching records.
  
  Copy the report to your system, place the item number in cell B3, refresh the report, save it in REPORT mode (not Design mode, since a report must be run in order for "Last" and "First" records to be accurately found), and then attach the report to a reply here?
  
  Thanks!

  • Test.xlsx
• 0
  
  Jet Reports Historic Posts May 20, 2016 17:16

  Great to hear, Rachel!
  
  Thanks for letting me know.