4 Replies Latest reply on Apr 24, 2012 11:46 AM by AntoineBoulet

    Little problem with a range of dates in calculation

    AntoineBoulet

      Title

      Little problem with a range of dates in calculation

      Post

      Hello!

       

      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.

       

      Example : 

      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!

       

      Thank you!

       

      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.

      Capture_d%E2%80%99%C3%A9cran_2012-04-24_%C3%A0_10.25.04.png

        • 1. Re: Little problem with a range of dates in calculation
          philmodjunk

          I don't speak your language (French?) and thus must guess as to the English version of the functions you are using. Does Si stand for If?

          I can figure out that Mois = Month, and Jour = Day and Annee = year. but am not totally sure about Si in this expression.

          Also, is Obtenir ( DateActuelle ) French for Get ( CurrentDate ) ?

          And is Anniversairea number field? How does it get a value? I appears to store the number of the next anniversary for a given record.

          I'd use an age calculation to compute the current anniversary for a given record:

          Define Anniversairea as:

          Let ( [ T = Obtenir ( DateActuelle ) ; F = Date de fondation ] ;
                 Annee (T ) - annee ( F ) - ( Date ( Mois ( F ) ; Jour ( F ) ; Annee ( T ) ) > T )
                )

          Then define the "6 months ahead" contact date calculation as:

          Let ( [ T = Obtenir ( DateActuelle ) ; F = Date de fondation ] ;
                   If ( Not ( Mod ( anniversairea ; 5 ) ; Date ( Mois ( F ) - 6 ; Jour ( F ) ; Annee ( T ) )
                )

          • 2. Re: Little problem with a range of dates in calculation
            AntoineBoulet

            Oh, I didn't think about the French Part! Sorry about that!

             

            Indeed, SI is IF, and Obtenir (dateActuelle) is Get (Current Date). Pretty impressice sir!

             

            Anniversaire is the "Anniversaire cette anné" column. It's a calculation field that gets his value by substracting the YEAR of "Date de Fondation (Founding date") form the current year (2012).

             

            I tried your code, and I still get the same error. Althought the Mod function is exactly what I was needing to not enter every multiple of 5 by hand, thank you!

            • 3. Re: Little problem with a range of dates in calculation
              philmodjunk

              It's a calculation field that gets his value by substracting the YEAR of "Date de Fondation (Founding date") form the current year (2012).

              This will be one year in error in cases where the anniversary date falls after the current date. Note how my calculation adjusts for this by subtracting 1 when this is the case. This must also be an unstored calculation field or it will not update correctly as time passes.

              I had to change some parenthesis in the second calculation before it would work.

              It should be: (Removed a ( between Not and Mod. Added a ) at the end of the expression.)

              Let ( [ T = Obtenir ( DateActuelle ) ; F = Date de fondation ] ;
                       If ( Not Mod ( anniversairea ; 5 ) ; Date ( Mois ( F ) - 6 ; Jour ( F ) ; Annee ( T ) ) )
                    )

              When I did that, I entered a date of July 8th, 1990 (Metro's date founded). The Anneversary calculated as 21 and the second calculation correctly returns a null value--leaving the field blank. Entering Dec 9th, 1987 returns 24 and Null. If I change it to Jan 1, 1987, I get 25 and a notification date of 7/1/2011. These are the values I'd expect from this calculation, but I now see the problem. The 6 months notification date is not being returned 6 months in advance of the anniversary date.

              If I remove " - ( Date ( Mois ( F ) ; Jour ( F ) ; Annee ( T ) ) > T )" from the anniversary calculation, then it appears to work as it is needed. I then see 22 and Null for the first date founded and 25 and Sept 6, 2012 for the second date founded.

              • 4. Re: Little problem with a range of dates in calculation
                AntoineBoulet

                Wow, thank you VERY much Phil!

                I still don't understand why my code didn't work, but I do understand yours.

                You helped me big time, thank you again for your time!

                 

                As for the - ( Date ( Mois ( F ) ; Jour ( F ) ; Annee ( T ) ) > T ) in the calculation of the annivesary date, I didn't put it in my code, even after your first message. Basically, we just want to know WHAT anniversary they will celebrte in 2012 no matter if it's before or after today's date.

                 

                Thank you once again!