Little problem with a range of dates in calculation
I'm just startign to learn how Filemaker works. Creating my first db.
It's a really simple one to keep track of our customers anniversary. There's a calculation field where I write the date 6 months prior to their anniversaries (multiple of 5 only), so the person who will check the database will know easily which one to send an email to and when.
It works great, showing a date in this field when anniversary is 5-10-20-50, etc... and showing nothing when the anniversary is 3 years, 16, 22, etc...
My only problem is : when the creation date of the company is after 1990, the field doesn't go back to blank if the anniversary is not a multiple of 5.
The Spice Shop was founded in 1982. This year, it's his 30 anniversary, so I would see a date in my calculation field. Let's say it was founded in 1980. This year would be it's 32nd anniversary, so the calculation field would be blank.
Now, if the dates were 1992 and 1990 instead, I would see a date in the field for the 1992 dates (20 years), but the field would not be blank if the foundation date was 1990, as it would be if it was 1980. I really don't get it. I've check the manual and the forum, no luck...
Hopefully it's a really easy solution, a begginer's mistake!
My code :
Si ( Position ( "5,10,15,20,25,30,35,40,45,50,55,60,65,70,75,80,85,90,95,100,105,110,115,120,125,130,135,140,145,150,155,160,165,170,175,180,185,190,195,200,205,210,215,220,225" ; Anniversaire ;1 ; 1) ≠ 0; Date (Mois (Date de fondation) ; Jour (Date de fondation) ; Annee (Obtenir ( DateActuelle ))) - 180 ; Si ( Position ( "4,9,14,19,24,29,34,39,44,49,54,59,64,69,74,79,84,89,94,99,104,109,114,119,124,129,134,139,144,149,154,159,164,169,174,179,184,189,194,199,204,209,214,219,224" ; Anniversaire ;1 ; 1) ≠ 0; Si ( Position ( "01,02,03,04,05,06" ; Mois ( Date de fondation ) ;1 ; 1) ≠ 0; Date (Mois (Date de fondation) ; Jour (Date de fondation) ; Annee (Obtenir ( DateActuelle ))) - 180 ; "");""))
PS - I know I probably could put the numbers (5-10-15...etc...) in tables and just refer to them, but I'm not there yet!!)
Also, the 4,9,14,19,etc... is because I want to know 6 months prior to the x5th anniversary, in case it's in the first six months of the year.