Hi all - I'm trying to return the last Posting Date for a given Store Code. However, my query doesn't work as is.
My query looks like this: =NL("Last","Posted Deposit Header","Posting Date","Store Code",C6,"-Posting Date","*")
I know the records are out of order and the value that is being returned is the last record in the data set, not the most recent date which is what I'm looking for. Maybe I don't have my query set up right?
Hi Jake -
If you want the "Last" to mean "most recent", your function looks correct. (if you want "oldest", you'd want to switch your sorting order).
If there are more recent posting dates within the data set defined by the Store Code you could try":
1) using ShowQuery= to see what query is being sent to the database
=NL("Last","Posted Deposit Header","Posting Date","Store Code",C6,"-Posting Date","*","ShowQuery=","TRUE")
2) Submit a support request so that you can share your query and results for research
Thanks, Harry.
So my query doesn't seem to work. I went into the Posting Deposit Header table in NAV, pulled all of the records out of the table after filtering out the Store Code = 100. Once I got all of the records in Excel, and sorted them, I see the last posting date is 7/23/2018, which is the record I want. But it's returning 10/1/2009, which before I sorted all of the records in Excel by posting date was the very first record. Interestingly, the last record at the end of the data set was 7/14/2014.
I tried your showquery method but I got an error.
Thanks for trying. I might put a ticket in.
Jake -
Are you running the report or simply seeing the results in Design mode? You'll have to run the report in order for the NL(Last) function to work.