Validation for the start date to greater than Max(End date)

Dear all,

I have a grid in the Employee details which has the payroll details.For Every employee id ,salary details are saved.But we need to get the history of salary details.So i made a field start date and end date .I managed to do the validation for everyline of the grid to check end date to be greater than start date.But also i need to add an other functionality.

For every employee there can be multiple lines of grid line having each time change in salary .This can be done through start date and end date.So i need to write a validation method for the start date should be greater than the Max(end date) of that particular employee id.

Please let me know if my explanation is not clear.

You need to find the record having maximum end Date and use the date for validation.

An example -

select

firstOnly TransDate from vendTrans order by transDate desc;

Thanks Kranthi for your reply.But this has to be for particular employee id where the start date to be greater than the max of end date like empl id Em001 can has 3 lines of grid.But when we add a new line it should nto allow user to select the date which is already given in the end date of the particular employee.

Yes you should find a relevant record for that employee by using a local table buffer(the select statement provided is an example). You need to include the relevant predicates in the where clause.

public boolean validateWrite()
{
boolean ret;
CaePR caepr1;
ret = super();

if (ret)
{

if (this.caeEndDate <= this.caeStartDate)
{
ret = ret && checkFailed("@CAE98");
}

caepr1 =CaePR::find(this.EmplId);

select firstonly caeEndDate from caepr1 where this.EmplId == caepr1.EmplId order by caeEndDate desc;

if (caeEndDate > = this.caeStartDate)

{

ret = ret && checkFailed("@CAE98");

}

}
return ret;
}

ya i wrote already with where clause but its throwig some error like syntax error…am new to AX so i cldnt find to sort out the issue with the above code.

The select should be like this

select firstonly caeEndDate from caepr1 order by caeEndDate desc where caepr1.EmplId == this.EmplId ;

Thanks for the quick response.I think in all the cases desc is not working…Also an other issue is on the first line it should accept start date and end date

with the condition start date should not be greater than end date and from the second line it should check start date should not be greater than max(end date) which i wrote the method as shown above .

But with the above query it always throws validation message if i add first line also with start date and end date since its alwasy taking firstonly enddate

Hi i found a solution to eliminate the above issue

select

MAX(caeEndDate) from dbo.CAEPR where emplid=‘E003223’ having COUNT(RECID)>1

but please let me know what will be the work aound for having in AX since it doesnt have ‘having’ clause

max i wrote as maxof but having i dont know whats the alternative.

Hi i found a solution to eliminate the above issue

select

MAX(caeEndDate) from dbo.CAEPR where emplid=‘E03’ having COUNT(RECID)>1

but please let me know what will be the work aound for having in AX since it doesnt have ‘having’ clause

max i wrote as maxof but having i dont know whats the alternative.

Hi

For the above issue i can fix by writing the following query in the AX

select

MAX(cEndDate) from dbo.CPR where emplid=‘E03’ having COUNT(RECID)>

1

But what will be the alternative for Having in AX since it doesnt has having clause.

Max i can write as Maxof but i am stuck up with Having

I don’t understand why you can’t simply write it this way:

select maxOf(caeEndDate) from caepr1 where caepr1.EmplId == this.EmplId;

if (caepr1.caeEndDate  >= this.caeStartDate) { ... }

By the way, AX2012 supports Having in its Query framework (see http://msdn.microsoft.com/en-us/library/hh745337.aspx, for example).

Thanks for the reply.But i cant write in this way because i the grid the user should be allowed to select the start date and end date on the first line. but when i writte the above code always there will be recordset in the above query like whatever he selects as end date that will be the max date and further user cant select the date in the first line itself.

If we talk about inserts, there is not a problem - the record is not saved yet. If you need it also updates too, you just need to filter out the current record:

select maxOf(caeEndDate) from caepr1
    where caepr1.EmplId == this.EmplId
    && caepr1.RecId != this.RecId;

If you have AX2012, you may also consider using Date Effective Data.

Thanks martin for theresponse.Now i have changed my solution to check box.Anyway thanks a lot.