Birthday Reminder - custom list & workflow


Badge +11

Hello all,

I wanted to create a small custom list where it will act as a reminder for people's Birthday.

So I created a custom list as following in SP2013:

  • Username                           > People or Group
  • Birthday                              >  Date Only format
  • Next Birthday                      > Calculated field        
  • Days until B'day                  > Calculated field

Next Birthday contains following formula:

=DATE(YEAR(Birthday)+DATEDIF(Birthday,TODAY(),"Y")+1,MONTH(Birthday),DAY(Birthday))

Days until B'day contains following formula:

=IF([Next Birthday]=TODAY(),"Happy Birthday","has birthday in "&DATEDIF(TODAY(),[Next Birthday],"d")&" days")

However, the actual custom list is only updated when an entry is created and then it remains static.
Although there is some kind of "AJAX refresh options" but they seem not to be doing anything.

So my next thought was, to create a SITE WORKFLOW which will be scheduled to run every day at 00:00
My thoughts are stuck and not sure which function to use in workflow!

Ideally, would be great to have an update function which re-runs the actual formula and the fields will be updated.
For instance; the remaining days will be recalculated and the next birthday will show next year 2017 etc etc.

Any suggestions please?


20 replies

Badge +11

Hi Bim Bimi,

You can create a site workflow. Within that, query for all the list items from your Birthday list. Get all the ID's in a collection.

Configure a For Each loop for these ID's. Then update some field within this For-Each loop. When the item gets updated your calculations will refresh. This would be a shorted way to do it.

If you want, you can move all the calculations within the site workflow as well and then update the fields on the list with this workflow. Hope it helps.

*Accidently marked this as assumed answered. I'm requesting Frank Field to please unmark it as "Assumed Answered".

Userlevel 5
Badge +12

Hello Bim Bimi,

Another way to solve this is to do exactly what you were thinking... simply use the Update Multiple Items action and update a column on all items in the list.  This will cause the calculated field to refresh daily.   Create a simple column in the list and use it as your target to update.     Is this the most efficient methods?  No, but it certainly is one of the quickest and easiest to implement with your scheduled workflow. happy.png

Badge +11

the problem is that I am not that advanced and even struggling to make the first move.
if you find the time, would appreciate if you demonstrate via screenshot or upload the workflow file for me to look at.

cheers

Badge +11

Hey Mike

I am running short of option with the Update Multiple items and here is why: I think it is because I have 2 normal fields [username and birtdhay] and then I have 2 x calculated fields [days until + next Birthday]

Now, in the Update Multiple Item it gives me the following options:

* the Message field was added for testing purposes and wanted to implement the formula in there but no luck sad.png

The NAME field is there by default when the list was created and cannot be deleted (I find it very silly)
The USERNAME is a group & person field and the Birthday is a date only field.

So, as already said other 2 fields are calculated fields hence why they don't appear in the field section.

My trouble is now, how to make this refresh the calculation every day at 00:00

Cheers

Badge +9

Hi,

As a beginner, I suggest to follow this link, first.

Best regards,

Christophe

Badge +11

thanks, I already went through them!!!
Though, none of the tutorial explain what I have asked above ...

never mind, thanks for trying.

Badge +11

I do not see the ID field in the drop down in the query action. It has to be there for a list. When you say Name was there by default, I don't think Name is created by default. Click on the 'Name' column settings and check the URL. What does it show towards the end for Field=...

Mike M  has suggested the easiest way. That would refresh your calculation.

Irrespective of what you are trying or if you are a beginner or an expert, we are here to help each other, even Christophe Raucq. If you do not like someones reply, please refrain from suggesting it. happy.png

Userlevel 5
Badge +12

Hello Bimi,

The way I suggested really removes all complexity from it.  Using the Update Multiple Items action will allow you to update a new field in your list which will cause your calculated columns to refresh daily.   You could create a text column for example that is called "PerformUpdate" and then use the Update Mutiple Items action to set this value to "Yes".  

Then you simply schedule your workflow to run on a daily basis as you were already intending to do. 

The nice thing about this approach is that the entire workflow consists only of a single action:  Update Multiple Items.

Let me know if that makes sense happy.png

Thanks,

Mike

Userlevel 5
Badge +12

Just to drive my point:  You are not using the Update multiple item action to update the calculated fields directly, because Sharepoint will take care of that work for you, you are however going to use the Update Multiple Item action to update the new column that you create to be used for this purpose.   The reason being, Sharepoint triggers its calculated field mechanism when a change is made to an item, so we are just using this as a workaround to get that change triggered.

Thanks,
Mike

Badge +11

Don't give up!!!! Mike and the others have great ideas.

Badge +11

You could also in a scheduled workflow do a calculation on today's date from the birthday to get days remaining. That would be the number of days remaining. And not have the days remaining a calculated field. I did something similar but it wasn't for a birthday.

fn-DateDiffDays({WorkflowVariable:NextBirthday},{Common:CurrentDate})

Badge +11

hi Burk,

thanks for the advise but I seem not to get it working sad.png
see the above reply to Mike's suggestion.

Cheers

Badge +11

Hey Mike

the MultipleUpdate function has done the trick, finally happy.png after I had a few trial & error.
One thing which I am not quite happy yet is the "days until next birthday" calculation field.

This is the result after the Site-Workflow has updated the field.

Then I manually updated the Birthday date of the second entry so it be todays birthday.
Now, in the calculated field, I did add this formula:

=IF(NextBirthday=TODAY(),"Happy Birthday","has birthday in "&DATEDIF(TODAY(),NextBirthday,"d")&" days")

So it should ideally say "Happy Birthday" since it should compare the field with NextBirthday.
Though it is ignoring it and says "has birthday in 365 days".

HINT: I think it is because the NextBirthday is pointing to 10/10/2017 for strange reason, and it should say 10/10/2016 instead.
Well, in this case, I have altered for the birthday to be tomorrow... now, I will see if I get the appropriate message by next update.

Userlevel 5
Badge +12

Glad I could help you Bimi,  if you could mark my answer as correct I'd appreciate it.    Just as a suggestion, could you use the DATEDIF function  to compare the two dates, and  if the number returned is 0 then you'd have a true condition (BIRTHDAY).

Userlevel 5
Badge +12

***Meaning  compare the results of the DATEDIF to 0 to check for equality.

Badge +11

Hey Up Mike,

Yes, I keep trying to sort this out with the DATEIF but I get no-where.
Strangely, the below formula works fine in Excel.  Wherby the = can also be <>

=IF(TODAY()=(B3),"Happy Birthday","has birthday in "&(DATEDIF(TODAY(),B3,"d")&" days"))
Userlevel 5
Badge +12

I haven't tested this, but I was thinking something along the lines of the following - give it a try and see if it works :

IF(((DATEDIF(TODAY(),B3,"d") = 0),"Happy Birthday","has birthday in "&(DATEDIF(TODAY(),B3,"d")&" days"))
Badge +11
=IF((DATEDIF(TODAY(),B3,"d")= 0),"Happy Birthday","has birthday in "&(DATEDIF(TODAY(),B3,"d")&" days"))

yes, will have to test this one happy.png once the Site-Workflow is running.
Although, I have a feeling that although the code works but it won't do what I want it to because;

1. the site-workflow will make both calculated field run the update

2. the daysUntilNextBirthday field is calculated based on the date what is being shown in nextBirthay

3. when both fields are getting updated - in my logic - it will never show 0 but 365 when it reaches the date.

if you know what I mean ... anyway, I will have it a go and let you know

Badge +11

Ahhhhhhhh!!! As I thought!

Here is the result ....

but then .... as I was typing this, I said to myself "well why not saying if 365 than say Happy Birthday" and then boooom... the results shown here:

Now it does what I wanted it to grin.png

THANK YOU MIKE

Userlevel 5
Badge +12

Excellent!  You are most welcome, and glad to have helped.  

Reply