Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Case - calculate date based on date, age and type criteria


Parken26
 Share

Recommended Posts

I am developing a vehicle management solution in whihc I need to display the date of the next coming MOT surveys for a large number of vehicles - in a portal.

For this I need to have FM return a date to a field based on the following

If a vehicle is a truck it must be surveyd once a year

If the vehicle is a car it must be surveyed every second year BUT only if the vehicle is older than four years.

 

I am trying to work this out with a Let function as per below - however I just keep getting stuck in understanding the correct way of construction the Case

 

NB! Personbil and Varebil" is local for car and "Lastbil" is for truck and it would probably be just as easy to only operate with truck in the Let.

 

Any help on tis issue is much appreciated.

 

Let(

[

vehicleAge = Year( Get( CurrentDate ) - RegistrationDate );

isOlderThanFourYears = ( Get( CurrentDate ) - RegistrationDate )

isLastbil = VehicleType = "Lastbil";

isPersonbilVarebil = VehicleType = "Personbil Varebil"

]

;

 

Case ( vehicleAge ≤ 5 ; RegistrationDate +4 )

)

Link to comment
Share on other sites

Let(

[

vehicleAge = Year( Get( CurrentDate ) - RegistrationDate ); make sure your calcfield is UNSTORED or it won't assess Get(CurrentDate) correctly

isOlderThanFourYears = ( Get( CurrentDate ) - RegistrationDate )

{you don't need this}[/color]

isLastbil = VehicleType = "Lastbil"; I personally hate raw booleans; your mileage may vary

{you don't need this}

isPersonbilVarebil = VehicleType = "Personbil Varebil"

you don't need this}

]

;

 

Case ( vehicleAge ≤ 5 ; RegistrationDate +4 )

that's four DAYS

)

 

Let(

vehicleAge = Year( Get( CurrentDate ) - Year(RegistrationDate );

Case ( VehicleType="Truck"; RegistrationDate+365;

vehicleAge ≤ 5 ; RegistrationDate +730;

vehicleAge > 5; "Beats Hell Out of Me... Never???"

)

)

 

 

click storage tab of the calc field; set storage to "Do not store calculation results. Recalculate as needed"

Edited by AHunter3
Link to comment
Share on other sites

Hi John,

 

Thank you very much for contributing - your suggestion looks promising - however, when I choose a car and pick a purchase date 19-10-2012 ( which will also be the first survey date) the next survey date is displayed as 19-10-2014 which is 2 years too early since the car is less than 4 years old.

A car purchased in 2012 will not need survey before 2016

 

I see the idea with a separate table to hold the surveys and this might be a good idea which I never thought of - I just thought of making a calculation in the same table as the vehicles.

 

Could you look into the misunderstanding with the car being between 0 and 4 years = survey only after 4 years have elapsed

and

if more than 4 years it must be surveyed in 2 year increments.

 

brgds Martin

Link to comment
Share on other sites

  • Gold Members

This should work:

 

Let(

 

[@PurchaseDate = VEHICLES::date_purchase;

@LastSurveyDate = Case(IsEmpty(SURVEYS::date_surveyed); @PurchaseDate; Max(SURVEYS::date_surveyed))];

 

Case(

 

VEHICLES::type = "Truck";

 

Date(Month(@LastSurveyDate); Day(@LastSurveyDate); Year(@LastSurveyDate) + 1);

 

Date(Month(@PurchaseDate); Day(@PurchaseDate); Year(@PurchaseDate) + 4)

 

Date(Month(@LastSurveyDate); Day(@LastSurveyDate); Year(@LastSurveyDate) + 6); //If car is more than 4 years old

 

Date(Month(@LastSurveyDate); Day(@LastSurveyDate); Year(@LastSurveyDate) + 2) //If car is less than 4 years old

 

)

 

)

Link to comment
Share on other sites

There is still an issue with the very first record - if I clear the records and add a new record ie. jan. 1. 2010 - next survey should give me Jan 1. 2014 but it gives me 2016.

Which again is kind of correct if there had been a survey in 2014 -

Can I kindly ask if it is possible for you to look into this - it will be greatly appreciated.

Link to comment
Share on other sites

There is something odd goin going on - if I make a new record and set this for "car" and the purchase date is jan. 1 2017 date of next survey displays as 01-01-2019 whihc is incorrect since the car is less than four years it shall not be surveyed before 01-01- 2021

Now if I change the last two lines in the calculation you have been so kind to fabricate, from 6 to 2 and 2 to 4

 

These two lines

Date(Month(@LastSurveyDate); Day(@LastSurveyDate); Year(@LastSurveyDate) + 6); //If car is more than 4 years old

 

Date(Month(@LastSurveyDate); Day(@LastSurveyDate); Year(@LastSurveyDate) + 2) //If car is less than 4 years old

 

However if I put in historic data as a car is purchased on 01-01-2010 next survey is 01-01-2012 - something is correct because it adds 2 years to a car whihc is older than four years but obvioulsly the next survey cannot be in 2012 since we are now in 2017

If I then add a survey in 2014 as I am supposed to next survey is in 2018 which again is correct.

 

What am I getting at here - I think the last change I made with the two last lines made the difference - could you please spend a minute or two and confirm to me that the change I made will not affect the entirety of the calculation.

 

Much appreciated.

Link to comment
Share on other sites

  • Gold Members

We'll get this eventually. Try this:

 

Let(

 

[@PurchaseDate = VEHICLES::date_purchase;

@LastSurveyDate = Case(IsEmpty(SURVEYS::date_surveyed); @PurchaseDate; Max(SURVEYS::date_surveyed))];

 

Case(

 

VEHICLES::type = "Truck";

 

Date(Month(@LastSurveyDate); Day(@LastSurveyDate); Year(@LastSurveyDate) + 1);

 

Date(Month(@PurchaseDate); Day(@PurchaseDate); Year(@PurchaseDate) + 4) > Get(CurrentDate) and IsEmpty(SURVEYS::date_surveyed);

 

Date(Month(Get(CurrentDate)); Day(Get(CurrentDate)); Year(Get(CurrentDate)) + 6); //If car is more than 4 years old

 

Date(Month(@LastSurveyDate); Day(@LastSurveyDate); Year(@LastSurveyDate) + 2) //If car is less than 4 years old

 

)

 

)

Link to comment
Share on other sites

I am sorry but this looks exactly the same to me, as the last one.

am I mistaken?

Anyhow it is now putting out 2023 but also adding 10 months and 20 days (seems to be today) if I purchased a car on 01-01-2017

 

NB! My system date is running European format - DD/MM/Year is this tricky or will FM adjust for that?

 

thanks for staying on board and helping me out - much obliged.

Link to comment
Share on other sites

  • Gold Members

Let(

 

[@PurchaseDate = VEHICLES::date_purchase;

@LastSurveyDate = Case(IsEmpty(SURVEYS::date_surveyed); @PurchaseDate; Max(SURVEYS::date_surveyed))];

 

Case(

 

VEHICLES::type = "Truck";

 

Date(Month(@LastSurveyDate); Day(@LastSurveyDate); Year(@LastSurveyDate) + 1);

 

Date(Month(@PurchaseDate); Day(@PurchaseDate); Year(@PurchaseDate) + 4) > Get(CurrentDate) and IsEmpty(SURVEYS::date_surveyed);

 

Date(Month(@PurchaseDate); Day(@PurchaseDate); Year(@PurchaseDate) + 6); //If car is more than 4 years old

 

Date(Month(@LastSurveyDate); Day(@LastSurveyDate); Year(@LastSurveyDate) + 2) //If car is less than 4 years old

 

)

 

)

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use