Dr.Hamed Posted October 18, 2013 Posted October 18, 2013 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.
doughemi Posted October 18, 2013 Posted October 18, 2013 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
eos Posted October 18, 2013 Posted October 18, 2013 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 ) ) 1
Dr.Hamed Posted October 19, 2013 Author Posted October 19, 2013 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
eos Posted October 19, 2013 Posted October 19, 2013 […] 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.
Dr.Hamed Posted October 19, 2013 Author Posted October 19, 2013 This is exactly what I need. I'm so grateful.
Dr.Hamed Posted October 20, 2013 Author Posted October 20, 2013 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
eos Posted October 20, 2013 Posted October 20, 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 ) )
Dr.Hamed Posted October 20, 2013 Author Posted October 20, 2013 ord1 = GetAsNumber ( Right ( count ; 1 ) ) ;ord2 = GetAsNumber ( Right ( count ; 2 ) ) ; What is "count" field? The calculation needs defining this field.
Dr.Hamed Posted October 20, 2013 Author Posted October 20, 2013 I replaced "count" with "theCount" and it worked beautifully. Thanks for your great help.
eos Posted October 20, 2013 Posted October 20, 2013 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
Dr.Hamed Posted October 21, 2013 Author Posted October 21, 2013 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......
eos Posted October 21, 2013 Posted October 21, 2013 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?
comment Posted October 21, 2013 Posted October 21, 2013 (edited) 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, 2013 by comment
Dr.Hamed Posted October 21, 2013 Author Posted October 21, 2013 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.
eos Posted October 21, 2013 Posted October 21, 2013 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
Dr.Hamed Posted October 21, 2013 Author Posted October 21, 2013 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
Recommended Posts
This topic is 4298 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now