Hello - I am having a bit of trouble with the nl last function. I am looking to return the last creation date of payroll batch. I built two functions that are exactly the same except the first is a "rows" and the second is a "last".
the first function is an nl rows function that a returns a list of payroll batch creation dates:
=NL("Rows","G/L Register","Creation Date","Journal Batch Name","PAYROLL*","Company=", B3)
The results of this function are:
9/30/2013
10/5/2013
10/7/2013
10/8/2013
The second function is the exact same except it uses "last", it is:
=NL("Last","G/L Register","Creation Date","Journal Batch Name","PAYROLL*","Company=", B3)
The results of this function are:
9/30/2013
I'm wondering why isn't this returning the last creation date (10/8/2013)?
Date
Votes
8 comments
-
Jet Reports Historic Posts Official comment Hi,
This is by design, although it may seem a little strange. The NL(Rows) function always automatically sorts by whatever fields are being returned. So in this case, it will sort by Creation Date. NL(Last) will not sort at all by default, so if you want a sort, you have to specify it. The same is true of NL(First), NL([blank]), or NL([number]). For speed, these are not sorted by default. So your NL(Last) function is sorting by either the primary key of the table or possibly by some key that matches your filters better. So if you want your NL(Last) sorted by the field being returned, you have to add that sort manually like this:=NL("Last","G/L Register","Creation Date","Journal Batch Name","PAYROLL*","+Creation Date","*","Company=", B3)
Does that make sense to you?
Regards,
Hughes -
Jet Reports Historic Posts Thanks Hughes. That worked. I appreciate the clarification so that I know to sort my NL first and last functions from now on.
-
Tara Miller I am adding that + to beginning of the date but it still doesn't return the most recent.
-
Tara Miller NL("Last","Employee Rate","Effective Date","Payroll Rate Code",[@[Employee Rate - Payroll Rate Code]],"Employee No.",[@[No.]],"Effective Date","*")
Any assistance would be appreciated.
-
Harry Lewis Hello Tara -
You mentioned that you are adding the plus sign (+) to the date field, but your function does not show that.
Is this what you need?
NL("Last","Employee Rate","Effective Date","Payroll Rate Code",[@[Employee Rate - Payroll Rate Code]],"Employee No.",[@[No.]],"+Effective Date","*")
P.S.,
I understand what you are doing with this filter:
"Employee No.",[@[No.]]
but I'm not clear about what you are doing here:
"Payroll Rate Code",[@[Employee Rate - Payroll Rate Code]]
However, if that part is working for you... great!
-
Tara Miller If i put that in exactly the way you have it, it is still coming back with the oldest date and not the most recent. If I drill down on it, it says there is no records to display. No matter with the Last or First, +, -, ive done all combinations, it still pulls the old date.
The payroll rate code is just another filter but that works fine. Basically there is a rate per employee, per type of employee (payroll rate code), and effective date. The effective date is the day the new rate went into effect, which is why I am looking for something that will put the most recent date. Right now, i can do an entire employee master report via NLs but have to do a table build for the rates and do a vlookup based on excel formula of a maxif because I can't get the jet function to pull the most recent date.
-
Harry Lewis Hi Tara -
If you enter an employee number in cell C4 and then enter this function in cell D4:
=NL("Last","Employee Rate","Effective Date","Employee No.",C4,"+Effective Date","*")
and then run the report... what is returned by the NL(Last) function?
-
Tara Miller Oh I see. I wasn't actually "running" the report. Most times it will update on its own but guess for these it didn't. Thanks for the help!
Please sign in to leave a comment.