Trying to pick last record

If you switch the Debugger to Active, on which line of the above code is showing the error?

In this case, you should use a code like this:

Ben.INIT;
Ben.SETRANGE("No.","Employee No.");
IF Ben.FIND('-') THEN
  Paymtdate := Ben."Payment Date";

Most probably because it should have been

SETRANGE("Employee No.","Employee No.")

instead of

SETRANGE("No.","Employee No.")

depending on how is defined the field in the Benefit table.

If you need the last payment date for each employee, then use:

IF Ben.FIND('+') THEN 
  Paymtdate := Ben."Payment Date";

I do not know the structure of your Benefit table, but I could figure out if you could tell me its structure and the keys.

Anyhow, in priciple you should follow the following steps:

Ben.RESET;
Ben.SETCURRENTKEY(myKey);
// Where myKey is a key that contains "Employee No.", "Payment Date", "Entry No."
// If it does not exist already, you should define it
Ben.SETRANGE("Employee No.","Employee No.");
Ben.SETFILTER("Payment Date",'<>%1',0D);
IF Ben.FIND('+') THEN
  Paymtdate := Ben."Payment Date";

Ok, the most important thing is to use a key that will sort your data in the Benefit table as I wrote above. You need the data sorted ascendig by "Payment Date".

The second is to ensure that you filter the payments from this table. You do this by using appropriate SETRANGE and/or SETFILTER instructions. Last thing, you pick the last record ('+').

It is what I tried to suggest with my code above.

But you should include the "Payment Date" in the key, to sort the table according to this field, such as SETCURRENTKEY("Employee No.", "Payment Date", "Line No."). Only in this case you can select the last payment.

I assume you will have to define this new key.

You should not change the Primary Key of the Benefit table, what you need it to add secondary keys.

That said, I think what you need is a key (secondary) with this fields:

"Employee No.", "Payment Date"

On the OnAfterGetRecord of the Employee table, you should write something like this:

Ben.RESET;
Ben.SETCURRENTKEY("Employee No.","Payment date");
Ben.SETRANGE("Employee No.","No.");
Ben.SETFILTER("Payment Date",'<>%1',0D);
IF Ben.FIND('+') THEN
Paymtdate := Ben."Payment Date";


This will give you the last Payment date for each employee, that you should should in a Body Section of the dataitem Employee.

If nothing is showing, it's because you don't have any data or you are applying a filter somewhere.. you can follow the report with the debugger and see what's happening.

The field "Date filter" is flowfilter type of field, it does not contain any data, so you can't do that operation.

To obtain the data the user is putting there, you need to do

VarDateText := Employee.GETFILTER("Date filter"), on the OnPreReport.

This will give you a text , to convert it into a date, you can do:

IF NOT EVALUATE(VarDate,VarDateText) THEN; (it won't crash if they don't put any filter or they put something like 01/01/07..31/12/07).

And then you can do the substraction:

NoMonth := VarDate - Paymtdate

Before doing this, you must check both dates contain data, if not you will get an error and the report will stop:

if (VarDate <> 0D) and (Paymdate <> 0D) THEN
NoMonth := VarDate - Paymtdate

kolaboy wrote:

Is this what you are talking about.


IF NOT EVALUATE(VarDate,VarDateText) THEN;
NoMonth := "Date Filter" - Paymtdate;

IF (VarDate <> 0D) and (Paymtdate <> 0D) THEN
NoMonth := VarDate - Paymtdate

I defined VarDate as Date and VarDateText as Text datatype
I ran but still telling me Nat valid.
Can you please take me through step by step.

Just remove the line in bold, you don't need it.