I have contact list with "date_of_birth" field and "date_of_death" field.

 

I would like to have a field that sorts the contacts by the remaining days to their birthdays, excluding the persons who have died, in which case, the field should show the remaining days to their memorial or annual "death anniversary".

 

Appreciate your help.

Number of days should be a calculation field (result number):

 

Let(

[

~cd = Get(CurrentDate);

~NextBirthday=

     Case(

           Month(date_of_birth) > Month(~cd) ; Date(Month(date_of_birth); Day(date_of_birth); Year(~cd) );

           Month(date_of_birth) = Month(~cd) and Day(date_of_birth) > Day(~cd); Date(Month(date_of_birth); Day(date_of_birth); Year(~cd) );

           Date(Month(date_of_birth); Day(date_of_birth); Year(~cd) + 1)

     );

~NextAnniv=

     Case(Month(date_of_death) > Month(~cd); Date(Month(date_of_death); Day(date_of_death); Year(~cd)) ;

           Month(date_of_death) = Month(~cd) and Day(date_of_death) > Day(~cd); Date(Month(date_of_death); Day(date_of_death); Year(~cd) );

           Date(Month(date_of_death); Day(date_of_death); Year(~cd) + 1)

     )

 

];

Case(

IsEmpty(date_of_birth); "";

IsEmpty(date_of_death); ~NextBirthday - Get(CurrentDate);

~NextAnniv -Get(CurrentDate)

)

)

 

 

EDIT:  corrected and simplified calculation

Or a bit more compact:

Case (
  Count ( dob ; dod ) ;
    Let
      ( [
      d = Get ( CurrentDate ) ;
      theEvent = Max ( dod ; dob ) ;
      eventThisYear = Date ( Month ( theEvent ) ; Day ( theEvent ) ; Year ( d ) ) ;
      eventNextYear = Date ( Month ( theEvent ) ; Day ( theEvent ) ; Year ( d ) + 1 ) ;
      nextEvent = case ( eventThisYear < d ; eventNextYear ; eventThisYear )
      ] ;
    nextEvent - d
    )
  )

@eos:

 

Very elegant.

Thanks very much. The calculations are great. I created a field which add calculated value to the current date in order to show the date of the coming event.

 

Is there any way to add a phrase to the value to show automatically whether the coming event is a birthday or a death anniversary.

 

Like : Birthday on 10/26/2013

          Memorial on 10/28/2013

 

Appreciate it

[…] I created a field which add calculated value to the current date in order to show the date of the coming event.Is there any way to add a phrase to the value to show automatically whether the coming event is a birthday or a death anniversary.

 

Calculation type text, unstored:

Case (
  remainingDaysCalculationField ;
  Let 
    ( [
    eventType = Case ( not IsEmpty ( dod ) ; "Memorial" ; "Birthday" ) ;
    nextEvent = Get ( CurrentDate ) + remainingDaysCalculationField
    ] ;
    eventType & " on " & nextEvent
    )
)

where remainingDaysCalculationField is your other new field.

This is exactly  what I need.

 

I'm so grateful.

Thanks very much. The calculations are great. I created a field which add calculated value to the current date in order to show the date of the coming event.

 

Is there any way to add a phrase to the value to show automatically whether the coming event is a birthday or a death anniversary.

 

Like : Birthday on 10/26/2013

          Memorial on 10/28/2013

 

Appreciate it

 

Can the format be modified to show the numerical year of the event such as

 

         5th birthday on 10/26/2013

   or     2nd memorial on 10/28/2013

I think that except for the teens, the ordinal suffix is regular, so try this:

Case (
  remainingDaysCalculationField ;
    Let 
    ( [
    eventType = Case ( not IsEmpty ( dod ) ; "Memorial" ; "Birthday" ) ;
    nextEvent = Get ( CurrentDate ) + remainingDaysCalculationField ;
    yearOrg = Year ( Max ( dod ; dob ) ) ;
    theCount = Year ( nextEvent ) - yearOrg ;
    ord1 = GetAsNumber ( Right ( count ; 1 ) ) ;
    ord2 = GetAsNumber ( Right ( count ; 2 ) ) ;
    suf = Case ( ord1 > 3 or ( ord2 > 9 and ord2 < 20 ) ; "th" ; Choose ( ord1 - 1 ; "st" ; "nd" ; "rd" ) )
    ] ;
    theCount & suf & " " & eventType & " on " & nextEvent
    )
)

 

 

ord1 = GetAsNumber ( Right ( count ; 1 ) ) ;

ord2 = GetAsNumber ( Right ( count ; 2 ) ) ;

 

What is "count" field? The calculation needs defining this field.

I replaced "count" with "theCount" and it worked beautifully.

 

Thanks for your great help.

I replaced "count" with "theCount"

 

Right, my mistake. Good it works for you now.

 

While you're at it, replace 

ord2 > 9 and ord2 < 20

 with

ord2 > 9 and ord2 < 21

One more thing - and hopefully the last - related to this subject.

 

Many people show only their birthdays as "dd/mm" without the year. Is there a way to incorporate these data in your calculation formula to show their upcoming birthdays or memorials in the same manner......

Many people show only their birthdays as "dd/mm" without the year.

 

Are you saying that you don't have the year data for those people, or that you would like to display it in that format?

I do not have the year data.

Perhaps you could use something like:

http://fmforums.com/forum/topic/48048-birthday-reminder-how-can-i-do-it/

 

 

Note also that in order to calculate the upcoming birthday of a person, the year of birth is of no consequence - so there is no reason why you couldn't use the same methods suggested above for these people.

Edited October 21, 201312 yr by comment

In my scenario, I only have the birthday as day and month, in other words I can not enter the birthdate as a "date" field, so this calculation needs to be modified.

In my scenario, I only have the birthday as day and month, in other words I can not enter the birthdate as a "date" field, so this calculation needs to be modified.

 

Here's sample file that uses individual date components, rather than the date itself.

 

It's not exhaustively tested, but you should be able to adapt it further as required.

MemorialRemainingDays_eos.fp7.zip

Fantastic work .......  eos

 

I think your attached file solves most if not all potential problems related to upcoming events and age calculation.......

 

I recommend this discussion and your attached file for all users who need to implement age and annual events in their database

 

Thanks again