CALCDATE - Backward Calculation

ChessExpert
ChessExpert Member Posts: 103
edited 2010-12-01 in NAV/Navision Classic Client
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.

Answers

  • ChessExpert
    ChessExpert Member Posts: 103
    I was able to make a workaround for this:

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

    This works.
  • David_Singleton
    David_Singleton Member Posts: 5,479
    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.
    David Singleton
  • Belias
    Belias Member Posts: 2,998
    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
    -Mirko-
    "Never memorize what you can easily find in a book".....Or Mibuso
    My Blog
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,692
    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.
    Slawek Guzek - www.yitron.co.uk
    Business Central, MS SQL Server, Wherescape RED;
  • David_Singleton
    David_Singleton Member Posts: 5,479
    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.
    David Singleton

Categories