Jump to content

Birthday reminder / how can I do it?


This topic is 3810 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hi all FM fans,

here is my scenario:

We have a database of our employees. In each record is also birthday. What I would like to do is this:

I would like to make one ?calculated? field which will show me all upcoming holidays in next 7 days.

I found some solutions, where you can search for birthdays, but I prefer to have this as field which will ALLWAYS shows on right sight or our main database, so you can allways see who will have a birthdays in next 7 days.

Is that possible?

Thank you for your time.

Link to comment
Share on other sites

Note: All calcs below should be unstored

I use this to place a 'birthday alert' field including age that appears if it's someone's birthday....(also warns if DOB is empty)

If(IsEmpty(Date of Birth);"no DOB";

If(

(Month(Get (CurrentDate))= Month(Date of Birth) and Day(Get (CurrentDate))= Day(Date of Birth) );Year(Get(CurrentDate))-Year(Date of Birth)&" today";"")

)

I use this one to locate customers whose birthday is coming up next month....

If(Month(Get ( CurrentDate ))=Month(Date of Birth)-1;"Birthday next month";"")

Hope this helps - maybe you could adapt to calc day of month being within 7 days of day of birthday?

Link to comment
Share on other sites

Dukes,

thank you so much for your sollution. Portal is fine, BUT it is working only if the birthday is 2006!! So it will show me birthdays of employees which are not born yet.

Is it possible to fix this? Because then it will be best sollution I had seen.

Link to comment
Share on other sites

GCYM,

thank you for your tip. Good idea, but i don't know how to use your calculation for next few days. Do you think is that possible?

I thought it should be easy to do something like that, but it seems to be a nightmare now...

Link to comment
Share on other sites

it will show me birthdays of employees which are not born yet.

LOL, you are setting pretty high standards here. OK, try something like this:

1. In your Employees table, define a calculation field cBirthday (result is Date) =

Date ( Month ( DOB ) ; Day ( DOB ) ; 4 )

where DOB is the field where the actual date of birth is.

2. In the table where you want the view the results (this can be any table, incl. Employees), define an unstored calculation field cNext7Days (result is Date, number of repetitions is 7) =

Let ( [

today = Get ( CurrentDate ) ;

i = Get ( CalculationRepetitionNumber ) ;

d = Date ( Month ( today ) ; Day ( today ) - 1 ; 4 ) + i

] ;

Case ( i ≤ 7 ; d )

)

3. Define a relationship from your viewing table to a new TO of Employees:

YourViewTable::cNext7Days = Employees 2::cBirthday

A portal from your viewing table to Employees 2 will show the employees whose birthday is in the next seven days (including today). To keep it simple, I didn't fix a tiny flaw: in the week before February 28, on a non-leap year, you'll get only six days of heads-up.

Link to comment
Share on other sites

Comment,

wow, that's very interesting.

But I have something wrong, probably. Which field should be in a portal? I put there Name, but it doesn't work.

I created that 2 calc field, and there are strange dates in:

In 1st calculation there is right day and month of DoB but as year, there is 0004

In 2nd calculation there is 27.11.004 fo all field (but it's not Globel fiels...)

Did I something wrong?

Link to comment
Share on other sites

The field to put in the portal is the Name field from Employees 2.

The first calculation should show the person's birthday in the year 0004.

The second calculation should show:

27.11.0004

28.11.0004

29.11.0004

30.11.0004

1.12.0004

2.12.0004

3.12.0004

in any record (this is a repeating field with 7 repetitions).

You don't need these two fields on the layout (except to check the results). Their only purpose is to establish the relationship.

  • Like 1
Link to comment
Share on other sites

  • 4 months later...

Following a discussion in another thread, I have noticed a flaw in the method suggested above. In step 2, the definition of the repeating calculation field cNext7Days should be:

Let ( [

today = Get ( CurrentDate ) ;

i = Get ( CalculationRepetitionNumber ) ;

temp = Date ( Month ( today ) ; Day ( today ) - 1 ; 4 ) + i ;

d = temp - 366 * ( Year ( temp ) = 5 )

] ;

Case ( i ≤ 7 ; d )

)

  • Like 1
Link to comment
Share on other sites

  • 1 year later...

Hello,

I realize this thread is somewhat old but the topic of discusion will add substantial value with my current (& ongoing) solution. Following comments directions to the tee in post 230838, I'm encountering the following problem... when creating the portal I'm unable to select the new TO occurence (in my case named Client_data 2); it is greyed out. I've rechecked the relationship and it reads as

Client_data:: cNext7days= Client_data 2::cBirthday.

This is likely an easy fix but can someone suggest what I may be overlooking? Also, once this inconvenience is resolved, the field "name" that this placed in the portal...is this a first/last or full name?

As always, your guidance is appreciated.

Jack

Link to comment
Share on other sites

Thanks for the info , Comment. Unfortunately, I'm still unable to get this solution to work. For the interim and until my frustration level diminishes : ... I'll shelve this feature temporarily and revisit at a later date (again).

Thanks again,

Jack

Link to comment
Share on other sites

  • 2 years later...

Let ( [

today = Get ( CurrentDate ) ;

i = Get ( CalculationRepetitionNumber ) ;

temp = Date ( Month ( today ) ; Day ( today ) - 1 ; 4 ) + i ;

d = temp - 366 * ( Year ( temp ) = 5 )

] ;

Case ( i ≤ 7 ; d )

)

I am trying to use this calculation and it works but I have two questions

Can Comment or someone explain what this line d = temp - 366 * ( Year ( temp ) = 5 ) is for? I finally figured the rest how it works except that. Also, I have it doing what I want in this file except 14 days coming up but the portal sort is wrong and I am trying to get it to display ascending on December and then ascending on January.

Thank you all for the help you have given me.

birthdays.zip

Link to comment
Share on other sites

what this line d = temp - 366 * ( Year ( temp ) = 5 ) is for?

It corrects the calculated date by rolling it 366 days back if it crossed over to the next year (year 0005) - as will happen when the current date is nearing the end of December. Since the entire method is based on calculating everybody's birthday in the year 0004 ...

This is also why sorting the way you want is difficult. For the purposes of the relationship, the calculation in the people table must be stored - i.e. it cannot consider the current date. You want January to come after December - but you want this to happen only in December, so this would have to be another calculation field (an unstored one), say =

Mod ( Month ( DOB ) - Month ( Get (CurrentDate) ) ; 12 ) + Day ( DOB ) / 100

(result is Number)

---

P.S. in your file, make sure both calculations return a Date result. Also, the relationship should be using the = operator (if you want the people in the next 14 days).

  • Like 2
Link to comment
Share on other sites

I fixed my graph and added the calculation. You are brilliant, Comment. I would not have thought of this in a million years.

A strange thing has happened when I am trying to test different dates. If I change your calculation to use global date, this line "today = Get ( CurrentDate ) ;" to "today = global date in same table; " then the dates do not extend. I only get the first date.

In real file the current date is fine but I cannot understand why it would stop with global. Since global and unstored current date are both 'not indexed or stored' then they should act the same here, right? I was hoping to use this idea in other ways where I enter the date to extend. I am puzzled.

Link to comment
Share on other sites

  • 2 weeks later...

That worked perfectly.

I think it is because gDateField is a field but Get(CurrentDate) Is not? So it has nothing to do with stored or not but fields need to be extended. Is this right, Comment? It took a bit to work through this whole thing (I am not good at this stuff) but I think I now understand it except I want to be sure on this last question if you would confirm what my tests are showing which is that Get(CurrentDate) is special because it is not a field.

Thank you for your patience with me.

Link to comment
Share on other sites

I think it is because gDateField is a field but Get(CurrentDate) Is not?

Yes. The repeating field calculation is looking for the corresponding repetition in the referenced field - unless you have Extend() tell it to use the first repetition for all.

Link to comment
Share on other sites

  • 2 weeks later...

Hi Comment,

I am back. I have tried all I can and re-read everything you said. And I checked my test file and the sort does not work for me. I mean, I can see why it is not working. It is because January is making decimal only and all the other months make larger number but I am clueless on what I have wrong. I swear it worked originally.

Is it possible that you can see why? I would not ask but I have been stuck now for few weeks and realize it is simply above me. I have attached my test file. I also understand if the request is simply impossible. If you can't get it then I doubt it can be done.

If it would just sort in the same order as the repetitions fill in, that would be perfect.

David

birthdaysTest2.zip

Link to comment
Share on other sites

Bingo. I am amazed and dismayed that I missed those. It is perfect now. I was finally beginning to doubt the calculation itself and I should have known better. My apology for taking your time on my blunder.

Is there a way to delete all of my mistake posts out of your otherwise perfect thread, Comment?

:cry:

Link to comment
Share on other sites

  • 2 weeks later...

LOL, I've left a trail of tears and blunders on this forum also ... it is simply part of learning. Ignorance is NOT stupidity. And none of your questions have been stupid. :king:

Link to comment
Share on other sites

  • 1 year later...

Sorry, You can not open a v7 file with v5, you have to have FileMaker Pro 7 or higher.  v7 files were written in a whole different language, so  they are not backwards compatiable. 

Link to comment
Share on other sites

This topic is 3810 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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.