1 2 Previous Next 26 Replies Latest reply on Apr 5, 2017 2:43 PM by chaseholden

    how to find birthdays

    pia

      How can I create a field that flags an upcoming birthday 10 days before the birthday. I have a database of contacts with family members' birthdays in mm/dd/yyyy format (date field) and want to have a flag when it's 10 days before someone's birthday so I can send them an info message.

       

      thanks for any suggestion,

       

      Pia

        • 1. Re: how to find birthdays
          Mike_Mitchell

          Try:

           

          Date ( Month ( birthDate ) ; Day ( birthDate ) - 10 ; Year ( birthDate ))

          • 2. Re: how to find birthdays
            Extensitech

            To calculate 10 days before the person's next birthday, use the following calc, but in the let statement put the date of birth field in as the "birthDate":

             

            Let ( [

            birthDate = GetAsDate ( "12/28/1969" )

            ; currentDate = GetAsDate ( Get ( CurrentHostTimeStamp ) )

            ; offset = 10

            ; birthdayThisYear =

                   Date (

                        Month ( birthDate )

                        ; Day ( birthDate )

                        ; Year ( currentDate )

                   )

            ; birthdayNext =

                   Case (

                        birthdayThisYear > currentDate

                        ; birthdayThisYear

             

                        ; Date (

                             Month ( birthDate )

                             ; Day ( birthDate )

                             ; Year ( currentDate ) + 1

                        )

                   )

            ] ;

             

             

            birthdayNext

            - offset

             

            )

             

            Chris Cain

            Extensitech

            • 3. Re: how to find birthdays
              siplus

              What if you don't check your computer 10 days before, because it's sunday or n other reasons ?


              A better option could be "give me all the people having their birthday next week", which would give you the same list of people regardless of being invoked on monday or on thursday of the current week.


              (And I'd boldface the ones changing decade, for a more "prominent" greeting)

              • 4. Re: how to find birthdays
                Extensitech

                Excellent point.

                 

                Better to focus on the "birthdayNext" calculation, and handle the rest with finds, scripted or otherwise.

                 

                If, on the other had you're searching for a particular birthday, you can search for, for instance, 10/11/*, and not do a calculation at all. If it's just a few target days you could script a find that had a request for each day, 10/11/*, 10/12/*, etc. Can't do a range in this model, though, so if you want to use ..., >, <, etc., having the next occurrence of the birthday calculated will give you a field to use that in.

                 

                Chris Cain

                Extensitech

                • 5. Re: how to find birthdays
                  dtcgnet

                  This will give you the number of days to the next birthday, WHEN the next birthday is 0-10 days from the current date. Zero = Today is the birthday, 1 = Birthday in 1 day, etc.

                   

                  Create a new field called "DaysToCloseBirthday". Make it a calculation field, unstored, type is Number. The calc uses the Birthdate field in your database. This will allow you to do a find on this field, where you're searching for any value >= 0.

                   

                  Let (

                  [

                  ~CurrentDate = Get ( CurrentDate ) ;

                  ~BirthdayThisYear = GetAsDate ( ( Substitute ( Birthdate ; Year ( Birthdate ) ; Year ( ~CurrentDate ) ) ) ) ;

                  ~NextBirthday =

                    Case (

                    ~BirthdayThisYear < ~CurrentDate ; GetAsDate ( Substitute ( ~BirthdayThisYear ; Year ( ~BirthdayThisYear ) ; Year ( ~BirthdayThisYear ) + 1 ) ) ;

                    ~BirthdayThisYear

                    )

                  ] ;

                  Case (

                  ~NextBirthday  ≤  ~CurrentDate + 10 and ~NextBirthday  ≥  ~CurrentDate ; ~NextBirthday - ~CurrentDate

                  )

                  )

                  • 6. Re: how to find birthdays
                    Extensitech

                    Or you could change the result in my calc to currentDate - birthdayNext.

                     

                     

                     

                    Chris Cain

                    Extensitech

                    • 7. Re: how to find birthdays
                      Menno

                      Maybe you can just create an unstored calculation when the NextBirthday of a person will be:

                      Let ( [    // dob = date of birth

                        tdy = Get ( CurrentDate ) ;

                        bdt = Date ( Month ( dob ) ; Day ( dob ) ; Year ( tdy ) ) ;

                        bdn = Date ( Month ( dob ) ; Day ( dob ) ; Year ( tdy ) + 1 )

                      ] ;

                        If ( tdy > bdt ; bdn ; bdt )

                      )

                      and then use ExecuteSQL to find all persons that have their birthday coming up:

                      ExecuteSQL ( "SELECT Member " &

                                          "FROM YourList " &

                                          "WHERE NextBirthday " &

                                          "BETWEEN ? AND ?" ;

                                          "" ; "" ;

                                          Get ( CurrentDate ) ;

                                          Get ( CurrentDate ) + 10 )

                        

                      most of the time I would not advocate to use an unstored calc like this, but since your list won't be much longer then a few hundreds, it'll work just fine.

                      • 8. Re: how to find birthdays
                        Nehme

                        This is the Unstored calculation that I use in my solution:

                        Where:

                        - Date of birth field =

                        - xBirthday reminder after = number field (in which you can insert any value: 10 in this example)

                         

                         

                        Let (

                        [

                        @birth = GetAsDate ( Date ( Month ( Date of Birth ) ; Day ( Date of Birth ) ; Year ( Get ( CurrentDate ) ) ) );

                         

                        @Current = GetAsDate ( Get ( CurrentDate ) );

                        @CurrentF = GetAsDate (  @Current  + xBirthday reminder after ) ;

                         

                         

                        @birthF =

                        GetAsDate (  Case (

                        Year ( @Current ) = Year ( @CurrentF ) and @Current  ≤  @birth ; Date ( Month ( Date of Birth ) ; Day ( Date of Birth ) ; Year ( Get ( CurrentDate ) ) )  ;

                        Year ( @Current ) = Year ( @CurrentF ) and  @Current   >  @birth ;  Date ( Month ( Date of Birth ) ; Day ( Date of Birth ) ; Year (@CurrentF) +1 )  ;

                         

                         

                        Year ( @Current ) < Year ( @CurrentF  ) and @Current  ≤  @birth ;  Date ( Month ( Date of Birth ) ; Day ( Date of Birth ) ; Year ( Get ( CurrentDate ) ) ) ;

                        Year ( @Current ) < Year ( @CurrentF  ) and @Current > @birth ;  Date ( Month ( Date of Birth ) ; Day ( Date of Birth ) ; Year ( @CurrentF ) )

                        ))

                        ];

                         

                         

                        Case

                        (  GetAsDate ( @CurrentF )  ≥  GetAsDate (  @birthF )

                        ; 1 ; 2 )

                        )

                        • 9. Re: how to find birthdays
                          siplus

                          here is mine, it only outputs 70, 80, 90 etc if person reaches that age next week, as requested by a client working with elderly people.

                           

                          Let ([

                          cd = Get(CurrentDate); 

                          yc = Year(cd);

                           

                          nextMonday = cd - Mod ( DayOfWeek ( cd ) - 2 ; 7 ) + 7;

                          nextSunday = nextMonday + 6;

                           

                           

                          thisyearBD = Date(Month(BDate); Day(BDate); yc);

                          nextyearBD = Date(Month(BDate); Day(BDate); yc+1);

                           

                           

                          hasBDthisYear = (thisyearBD ≥ nextMonday and thisyearBD ≤ nextSunday) ;

                          hasBDNextYear=  (nextyearBD ≥ nextMonday and nextyearBD ≤ nextSunday);

                           

                          age =  Case (


                            hasBDthisYear; yc - Year(BDate);

                            hasBDNextYear; yc - Year(BDate)+1;

                            0);

                           

                          age = If (age > 68 ; If (Mod(Age;10); 0; age);0)

                           

                          ];

                           

                          age

                           

                          )

                          • 10. Re: how to find birthdays
                            pia

                            I think I mis stated what it was I am trying to do. I would like to “flag” customer records when they or a family member will have a birthday in 1 month, 3 weeks, 2 week and 1 week time. E.g. Mr. X has a birthday 11 April. I want a flag to warn me 11 March, 18 March, 25 March and 2 April (maybe I can’t count days well so sample dates might not be correct) that his birthday is approaching. I am no really concerned with how old he is.

                             

                            I have tried to modify the below but have never done this and cannot make it work:

                             

                            Let ([

                             

                            Cd=Get(CurrentDate);

                             

                            nextMonday=cd-Mod(DayOfWeek(cd)-2;7)+7;

                             

                             

                            nextSunday=nextMonday+6;

                             

                            thisyearBD=Date(Month(Bdate); Day(Bdate));

                             

                             

                            hasBDTenDays=thisyearBD+10 and thisyearBD ≤17;

                             

                             

                            birthday= If (hasBDTenDays = 1; 1;0)];

                             

                            birthday)

                             

                            This will not even let me close the calc box and I can’t figure out what is wrong.

                             

                            and also tried this:

                            If (Month ( Get(CurrentDate) )  and Day ( Get(CurrentDate) ) -30 = Month ( SpouseBirthday )  and Day ( SpouseBirthday ); 30; "")

                             

                            But all I get there is a blank.  Sorry to be so dim.

                             

                            thanks,

                             

                            Pia

                            • 11. Re: how to find birthdays
                              Extensitech

                              Your Date function is missing a parameter.

                               

                              Also, are you trying to determine this based on their birthday or their spouses birthday? Or are there more?

                               

                              You have functions in this thread that will, given a date of birth, tell you either the date you should be notified or the date of their next upcoming birthday. Apparently we're still missing something, but what is it?

                               

                              Chris Cain

                              Extensitech

                              • 12. Re: how to find birthdays
                                dtcgnet

                                With something like this, where what your result is depends entirely on what "today" is at any given moment, the result will have to be unstored. Since an unstored calculation is only calculated for a record when the field is drawn on screen or when a find or sort calls on the result of the calculation, your results will only be "real" for the day in which you cause the calculation to occur.

                                 

                                In other words, if my birthday is 7 days away from Monday the 12th (today), my birthday will not be 7 days away from tomorrow, but my birthday WILL be occuring in the next week.

                                 

                                So, for this to work for you in the manner it sounds like you'd like, you'd need to run a script once per day that would find the appropriate records for you (by requesting a find which would cause the calculation to be evaluated for all records). Your "workflow" would have to be something like, "At 8 a.m., this script runs, finds people with birthdays 7, 14, 21, and 28 days away, and prints a report. I don't think that's what you want. Maybe, but I don't think so. (By the way...see the problem with 1 week, 2 weeks, 3 weeks, 1 month?)

                                 

                                It seems like what you might want is a list of EVERYONE who is having a birthday this week, and anyone who's having a birthday next week, and three weeks out, and four weeks out. You'd run your script every Monday morning, and it would provide you with a list of birthdays THIS week, and NEXT week, and so on. Joe's birthday might be this Tuesday and Bob's would be on Wednesday, and each would be on your list of "This Week's" birthdays. If you do it by exactly 7 days, 14 days, etc., then you'd need to run your script every day. It would run pretty quickly, but it would have to be done every day, without fail.

                                 

                                So...it's an interesting question and you're getting some great help with formulas...this is going to come down to the way you want this work to flow. Outline that a bit, and someone will help you find a good way to do it.

                                • 13. Re: how to find birthdays
                                  pia

                                  I want the different time periods for the same birthday as a trigger for action: I want to notify the customer 30 days before his birthday that we provided a service for him last birthday, so he has time to decide; I then want to follow up at the other intervals (not pestering him obviously) but having the other 3 reminder to the person whose job it is to follow up, so nothing falls through the cracks. Running a script every day should be Ok because I doubt it will ever get to be more than a few thousand records.

                                   

                                  thanks,

                                   

                                  Pia

                                  • 14. Re: how to find birthdays
                                    dtcgnet

                                    Sounds good...So, every day of the week, someone will run this script (it could be automated, too, by having the script run on the server at a specified time every night).

                                     

                                    I suggest that the calculation is important, but what's MORE important is your script. Let's say you use the calculation at the bottom of this post (I've modified my calc, but others will have other ideas which may be more efficient or understandable, too). So every night or day, someone runs the script you create.

                                     

                                    Your script does something like:

                                    1) Find all values of 30 in your calculation field. (this will cause the calculation to evaluate for all records).

                                    2) Do something. Maybe email each person if you have their email addresses. Maybe just create a pdf of the people whose birthdays are 30 days away.

                                    3) Find all values of 21.

                                    4) Do something.

                                    5) Find all values of 14.

                                    6) Do something.

                                    7) Find all values of 7.

                                    8) Do something.

                                     

                                    Your calc is critical to what you want to do with the results of the calc, but your WORK is what you want to accomplish, and that will come through your script, not your calc.

                                     

                                     

                                     

                                     

                                    The calculation below will give you a value of 0-30 for every customer whose birthday is within 30 days of when you force the calculation to be evaluated. If you use the formula (or one like it), then script a find based on the field, you'd be able to find the people you're after.

                                     

                                    Hope that helps.

                                     

                                     

                                    Let (

                                    [

                                    ~CurrentDate = Get ( CurrentDate ) ;

                                    ~BirthdayThisYear = GetAsDate ( ( Substitute ( Birthdate ; Year ( Birthdate ) ; Year ( ~CurrentDate ) ) ) ) ;

                                    ~NextBirthday =

                                      Case (

                                      ~BirthdayThisYear < ~CurrentDate ; GetAsDate ( Substitute ( ~BirthdayThisYear ; Year ( ~BirthdayThisYear ) ; Year ( ~BirthdayThisYear ) + 1 ) ) ;

                                      ~BirthdayThisYear

                                      )

                                    ] ;

                                    Case (

                                    ~NextBirthday  ≤  ~CurrentDate + 30 and ~NextBirthday  ≥  ~CurrentDate ; ~NextBirthday - ~CurrentDate

                                    )

                                    )

                                    1 2 Previous Next