CALCDATE - Backward Calculation

ChessExpert

Hi there experts. I am writing a code to calculate a date that will go backward instead of forward.
For example:
NewDate := CALCDATE( MyTable.DateFormulaField, WORKDATE() );
My ‘MyTable.DateFormulaField’ contains a value of ‘6D’ for six days.
I want them to put a positive number on that field so that it would be easier for them.
If my WORKDATE() is 2010-11-26, how will I be able to calculate six days before that?
Expected NewDate is ‘2010-11-20’.
Thank you.

ChessExpert

I was able to make a workaround for this:

TempFutureDate := CALCDATE( MyTable.DateFormula, WORKDATE());
TempDaysDifference := TempFutureDate - WORKDATE();
NewDate := WORKDATE() - TempDaysDifference ;

This works.

David_Singleton

A better solution would be to train them properly how CALCDATE works in Navision and have them enter -6D. Otherwise one day someone is going to enter a more complex formula in there and it will all apart.

Belias

i just found some standard code that exactly do what you want...
it's in report 120, calcdates() function

EVALUATE(PeriodLength2,'-' + FORMAT(PeriodLength));

where periodlenght2 and periodlenght are 2 dateformula variables

Slawek_Guzek

ChessExpert wrote:

I was able to make a workaround...

David Singleton wrote:

A better solution would be to train them properly how CALCDATE works in Navision and have them enter -6D. Otherwise one day someone is going to enter a more complex formula in there and it will all apart.

Technically (from C/AL point of view) the code will never fail, as long as date formula entered is correct. Solution is OK as long as you want to allow only 'shifting' dates by some constant periods..

But in general David is right - it is better to put some more effort in training (or write a good tooltip instead of code). Test this with CM value as date formula - it will work but results are going to be funny.

David_Singleton

Slawek Guzek wrote:

Technically (from C/AL point of view) the code will never fail, as long as date formula entered is correct.

I guess we have a different definition of fail.

take just about any formula and you will see that the '-' will almost never work.

CD+WD7-2D

7D+1M

CM

it will fail every time.