Trying to pick last record

kolaboy

Hi,
I am trying to pick last date from a different table and subtract it from th e current date, but i an getting errors ie its say "date is not valid"

The idea is trying to get the date last payment was made for each employee in a table called benefit Payment and subtract it from the current date. This difference in months should be multiplied by the monthly payment of each employee. Here is my caode:

//Code to Count Months

IF (FromDate <> 0D) AND (ToDate > FromDate) THEN BEGIN
    Calendar.RESET;
    Calendar.SETRANGE("Period Type",Calendar."Period Type"::Month);
    Calendar.SETRANGE("Period Start",FromDate,ToDate);
   Months := Calendar.COUNT - 1;
END ELSE
   Months := 0;

//Ben.GET(Empid);
IF(InitMonth <> DATE2DMY(Ben."Payment Date",2) ) THEN BEGIN
NumPayments := NumPayments +1;
InitMonth:= DATE2DMY(Ben."Payment Date",2)
END;

MonOutStand := Months - NumPayments;

can anyone edit this or give me a code that can do what i want please.
Thanks

kolaboy

I am using this code on the employee table. Can somebody help me out with this please, i need it work urgently.
i trying but no luck yet
thanks

Stardust77

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

kolaboy

I have seen the error using the debugger.

Now i want to pick the last payment date for each employee on a table called benefit payment. here is what i have:

Ben.GET("Employee No.");
Paymtdate := Ben."Payment Date";

I am trying to use the GET function to get each employee using their employee No., but employee no. and entry no. are the primary key for the benefit payment table.

when i run the report with the debugger on, i have error saying "the expression code cannot be type converted to an interger value". the error is pointed to "Employee No.". any problem?

i also would like someone to add some code that can get me the last payment date for each employe on the code above. can someone help me out please?
thanks

Stardust77

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";

kolaboy

Thanks Stardust77 for the quick reply,
i have tried your code but am have this error "A field from a record variable was expected. For example, record.Field Customer.Name"
Is there any variable to be included or defined.
Thanks

Stardust77

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.

kolaboy

I have done the changes and it complains of employee no. to be defined which i did on the global variable with type code. the report is compiled and i run it, but its not showing anything. I.e no output of the last Payment date for each employee.

any ideas?
Thanks

Stardust77

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

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

kolaboy

I have tried that but no output. No result is showing. i did not see any result of the code
Thanks

Stardust77

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";

kolaboy

Am writing the code on the employee dataitem. the report is using the employee dataitem, i am just trying the get the last payment date from the benefit table.
the benefit table has two primary keys that the employee no. and the entry no. I hope this will help you to help me
Thanks

Stardust77

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.

kolaboy

the keys in the Benefit payment table are "Line No." and "Employee No.".
How do you see this code:

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

Its still not giving me anything.
Thanks

Stardust77

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.

kolaboy

I have defined "Payment Date" as a new primary key in the Benefit table and i have also include it in the SETCURRENTKEY. When i run the report, Its still not giving me any result. I even tried to put the textbox on different section of the employee table but to no avail.
Anything the problem?
Thanks

Alisha

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.

kolaboy

Thanks Alisha and Stardust77, i am getting the last date now. Now my problem is the date subtraction see below:
NoMonth := Employee."Date Filter" - Paymtdate;

NoMonth is interger. i am trying to subtract paymtdate from date filter on the employee table. The idea is when i am running this report, i use the date filter to run the report. I want the subtraction but its telling me "the date is not valid"

Anything the problem.

Alisha

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

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.

Alisha

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.

kolaboy

thank you very much. It working
=D>