How to add years to a date based on a choice value field

Hi Peter

At a very simple an an example to start you off

Let us assume your two fields are as above “ManufactureDate” and “RetirementDate” 

in the Trigger After Update on the the first field put a script as follows

“RetirementDate” := “ManufactureDate” + 365

this will give you one year and auto fill the retirment date

date script functions are in the manual (page 70) which you can get from the help forum - so you can day/week/year etc.

ie year(date)

month(date)

Weekday(day)

year etc

Peter

also you don“t have to have this on the first fieild if you don”t want it to auto generate.

you could put the script onto a button and trigger it manually when it suits.

for example i do it this way when i am approving a job for invoicing and this then posts a review and approved date into the job

Mel 

So from this you should be able to workout that if a choice is in a field of x then do this in the other field

ie thie ManufacturDate filed is say the actul choice field

then in its trigger on update you wiil need to test for (not the names but 0 ( = blank) 1 (= 1st choice) 2 etc.

so if 1 date in retirement is x

if 2 date in retirement is y etc

Another possibilty: since you need to add either five or ten years, you could define two buttons. The code for each button would be:

let StartDate := max(MfgDate, FirstUseDate);
EndDate := date(year(StartDate) + 5, month(StartDate), day(StartDate))

and:

let StartDate := max(MfgDate, FirstUseDate);
EndDate := date(year(StartDate) + 5, month(StartDate), day(StartDate))

The second script should contain “+ 10”, of course...

Peter

Hmm 5 choices .....

Another way to do it could be have a start date field (ManDate) - have a first use date field (FirstUseDate

and year duration field (Yduration) set as a number field and make sure it it set for whole numbers

then your retirement date field (RetireDate)

and put the script on the trigger after update on the duration field (or whatever you want to call it) if you want it to auto trigger.

let myDate := max(ManDate, FirstUseDate);
RetireDate := date(year(myDate) + Yduration, month(myDate), day(myDate))

The script will trigger when you key in the year duration term and as per Alain“s example it will test both the manufacturing date or first use date is the higher then add x years to it in Retirement date

you could still put this on a button if you want it to activate only when you are ready.

You could expand on this - ie do you need to store the result of retirement date? or could it be a formula field to calculate virtually?

if you use a choice field you will have to test for all the possiblilities (remeber you don”t test on what is stored in text you test on the choice field index ie 0 (null) - 1 choice you see might be “1 year” or “blue car” etc.

Using a number field will allow you to enter nothing !! (so the trigger won“t happen!) or 0,1,2,3,4,5,6,7,8,9,10 years and so on

of course this assume you want to use whole years - you could test on 1.5 years or thing about calc on 12,24 months etc.

I guess it”s whahever works best for you

Mel

Let“s suppose that your choices are 1, 2, 3, 5 and 10 years. First define a choice field, with as options the texts you want to have displayed, like for example ”1 year“, ”2 years“, etc. Be careful not to delete a choice, as this would destroy the numbering of the choices, which will be needed.

Then the retirement date field can defined be as a formula:

let terms := [1, 2, 3, 5, 10];
let StartDate := max(MfgDate, FirstUseDate);
date(year(StartDate) + item(terms, number(Choice) - 1), month(StartDate), day(StartDate))