Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Denise Burke]

?I am attempting to use CALCDATE to add a number of days to a user entered date. Specifically, a custom table intended to be used to track equipment calibration and maintenance. So if identified as Calibrated, then take the last calibration date and add 1Y to determine the next calibration date. Where the user can define the time period as needed for the particular record (3M, 9M, etc.). The book I have says that we want to check the code for a reference date type before calculating, but fails to say how.

The compile error is “You cannot base a date calculation on an undefined date, Date: 0D, Formula: 1Y”.   It is an empty table, where I am testing a single record attempt. The formula is indeed set for 1Y, so where is it getting the 0D from? Ideally, I would like to get it to ignore blanks.

This does not work:

SETRANGE(“Last Calibration Date”,1D)

“Calibration Due” :=CALCDATE(“Calibration Period”,”Last Calibration Date”);

SETRANGE(“Last Maintenance Date”,1D)

“Maintenance Due” :=CALCDATE(“Maintenance Period”,”Last Maintenance Date”)

 

And this does not work:

“Calibration Due” :=CALCDATE(“Calibration Period”,”Last Calibration Date”);

“Maintenance Due” :=CALCDATE(“Maintenance Period”,”Last Maintenance Date”)

 

NAV did not like my attempts at an IF, THEN, ELSE bit either.

 What is needed to get this to allow blank records and just to calculate the Due Dates where a Last Calibration Date or Last Maintenance Date is entered by the user?
#NAV2017Developer or DBA

??

——————————
Denise Burke
Owner
Space Information Laboratories, LLC.
Santa Maria CA
——————————

[Kyle Hardin]

I’ll start with a small example so that you can understand what CALCDATE can do. And then we can talk about what you are actually trying to accomplish. I’ll try to use your example table and fields.

EquipmentCalibration: a record variable for your custom table
CalculatedDate: a date variable as a temporary holding place

EquipmentCalibration.”Last Calibration Date” := TODAY;
CalculatedDate := CALCDATE(‘+1Y’, EquipmentCalibration.”Last Calibration Date”);
EquipmentCalibration.”Next Calibration Date” := Calculated Date;

So that does a simple “add a year” to today, and sets that in Next Calibration Date.

Once you involve filtering, it gets a little messier, but SETRANGE and SETFILTER can’t use a CALCDATE string like +1Y directly. You have to turn it into a date first, and then set your filtering. Let’s define the EquipmentCalibration table a little better:

EquipmentCalculation
  Item No.: Code 20 (000001 for our example)
  Last Calibrated Date: Date
  Calibration Period: Text (+9M for our example)
  Next Calibration Date: Date

When you perform a calibration, code should do this:

EquipmentCalibration.GET(‘000001’);
EquipmentCalibration.”Last Calibration Date” := TODAY;
CalculatedDate := CALCDATE(EquipmentCalibration.”Calibration Period”, EquipmentCalibration.”Last Calibration Date”);
EquipmentCalibration.”Next Calibration Date” := Calculated Date;

Next Calibration date will get the value 3/22/19, because the code took today and added nine months.

Note – Calibration Period has to be ‘+9M’, not ‘9M’. If you don’t use the plus (or a minus), the system doesn’t know whether you want to add nine months or go back in time nine months.

As for filtering, I think it is cleaner if you store the Next Calibration date for each record rather than trying to calculate it on the fly as you are doing in your sample. It makes the filtering be something like this:

EquipmentCalibration.RESET;
EquipmentCalibration.SETRANGE(“Next Calibration Date”, 0D, TODAY);

That gets you all records that need to be calibrated again. Either they have never been calibrated, ever (thus the 0D for the lower end), or they just expired today.

——————————
Kyle Hardin
NAV Developer
ArcherPoint Inc.
Atlanta GA
——————————
——————————————-

[Jason Luchka]

?I’m not quite clear on how your code is being triggered, but here’s a basic example that maybe you can use to wrap up your current need.

Let’s say the user has a page with a three fields from your table:
Last Calibration Date (Date)
Calibration Period (DateFormula)
Next Calibration Date (Date)

While validating Last Calibration Date and Calibration Period, add code to call a function (example CalcNextCalibrationDate). This way, if the user changes the Last Calibration Date or changes the Calibration Period, it will execute recalculation of the Next Calibration Date.

In your function, you would do something like this:
“Next Calibration Date” := CALCDATE(CalibrationPeriod,”Last Calibration Date”);

I mocked this up in a processing report with a message to display the new calculated date:


EDIT – NAV Assumes you are adding the date in my code above. If you need to subtract from a date, please see the other example posted at the same time I was replying.

——————————
Jason Luchka
Senior Enterprise Applications Analyst
FFR Merchandising, Inc.
Twinsburg OH
——————————
——————————————-

[Ron Saritzky]

Hi Denise,

The CALCDATE function’s parameters are the first the formula for the number
of days/months etc to be added or subtracted from the reference date
(second parameter). So your sample code is almost correct. Assuming that
the “Calibration Period” field is entered as 3M or 1Y or something like
that, just prepend a plus sign:

“Calibration Due” :=CALCDATE(‘+’ +”Calibration Period”,”Last Calibration
Date”);

You can pre-validate your “Calibration Date” field too by defining a
variable as type Dateformula

IF NOT (EVALUATE(DateformulaVar, “Calibration Date”) THEN
ERROR (‘Error in Calibration date formula’ );

Good luck!

Ron

NAV did not like my attempts at an IF, THEN, ELSE bit either.

What is needed to get this to allow blank records and just to calculate
the Due Dates where a Last Calibration Date or Last Maintenance Date is
entered by the user?
#NAV2017Developer or DBA

??

——————————
Denise Burke
Owner
Space Information Laboratories, LLC.
Santa Maria CA

——Original Message——

?I am attempting to use CALCDATE to add a number of days to a user entered date. Specifically, a custom table intended to be used to track equipment calibration and maintenance. So if identified as Calibrated, then take the last calibration date and add 1Y to determine the next calibration date. Where the user can define the time period as needed for the particular record (3M, 9M, etc.). The book I have says that we want to check the code for a reference date type before calculating, but fails to say how.

The compile error is “You cannot base a date calculation on an undefined date, Date: 0D, Formula: 1Y”.   It is an empty table, where I am testing a single record attempt. The formula is indeed set for 1Y, so where is it getting the 0D from? Ideally, I would like to get it to ignore blanks.

This does not work:

SETRANGE(“Last Calibration Date”,1D)

“Calibration Due” :=CALCDATE(“Calibration Period”,”Last Calibration Date”);

SETRANGE(“Last Maintenance Date”,1D)

“Maintenance Due” :=CALCDATE(“Maintenance Period”,”Last Maintenance Date”)

 

And this does not work:

“Calibration Due” :=CALCDATE(“Calibration Period”,”Last Calibration Date”);

“Maintenance Due” :=CALCDATE(“Maintenance Period”,”Last Maintenance Date”)

 

NAV did not like my attempts at an IF, THEN, ELSE bit either.

 What is needed to get this to allow blank records and just to calculate the Due Dates where a Last Calibration Date or Last Maintenance Date is entered by the user?
#NAV2017Developer or DBA

??

——————————
Denise Burke
Owner
Space Information Laboratories, LLC.
Santa Maria CA
——————————

[Franz Kalchmair]

filtering a date field with setrange does not work with a calc formula as value, instead use a date value.

assuming you use a table calibration with fields id (int|key), desc. (text), calib. date (date), maint. date (date).
first record: calib. date has a start value, e.g. 010318D; 
set calcPeriod (calc formula): calcPeriod := ‘+1Y’;
load the record with 
calibration.get(1);
calibration.”calib. date” := calcdate(calcPeriod,calibration.”calib. date”);

developed some sample using sales header, field order date.
as you can see, some error. why? the record was not loaded, so order date is 0D.

when loading a record we get … see values in infobox right (debugger)

——————————
Franz Kalchmair
Microsoft MVP
Senior Consultant
Austria, Europe
——————————
——————————————-