I use Serenic Navigator and in our employee rates table, we make a new effective date each time the employee's rate changes. I need to pull a report with their current rate on it but everyone has a different effective date for their rates and I don't know how to tell it to retrieve the newest one for each employee.
Thanks for any help anyone can give me with this.
Holly
Date
Votes
6 comments
-
Jet Reports Historic Posts Official comment Hi Holly,
So I don't know exactly what your Employee Rate table looks like so I'll make a few assumptions here. Assuming you have the employee number in cell C3, you could write a formula like this:=NL("Last","Employee Rate","Rate","Employee No.",C3,"+Effective Date","*")
This will sort the rates for each employee by the effective date and then retrieve the last rate in the table, which should be their most recent rate. I may not have all the field names correct, but does something like this work for you?
Regards,
Hughes -
Jet Reports Historic Posts Thanks Hughes,
This works using a "-" sign. Thanks! -
Cara Kirkham I have a similar request and the solution from Hughes is not working.
I am trying to get the most current Posting Date from the Job Ledger Entry table based on Entry Type = Sale, Job No. = H12
=NL("last","Job Ledger Entry","Posting Date","+Posting Date","*","Job No.",H12,"Entry Type","Sale")
I have changed the parameters to be - and first and + and last and nothing changes the Posting Date that is being returned.
Cara
-
Heather Rowe One thing that often trips people up with this type of function is that they expect to see the date correct in design mode. This is one of the formulas that requires you to run the report to have Jet properly calculate the result.
-
Cara Kirkham I can't even believe it, that works....i feel rather silly!
Thanks for your quick troubleshooting Heather!
-
Heather Rowe no worries Cara, we've all been there :)
Please sign in to leave a comment.