Hi everyone! I’ve been looking without success for a solution to what seems like a really simple problem.
Let’s say I want to send an email to a user on their birthday.
The user’s birthday is stored using a simple date input field (as true date/number, not formatted as a text).
I want to send an email every year, on the user’s birthday. For example: if a user’s birthday is 10/12/88, I want to send an email on 10/12 every year.
When I try and use a recurring event, and go to select the date it should start on, I can’t extract just the month/day from the birthday date. I can only select the full birthday date, which isn’t right.
Thanks @barbufeliciasergiu, but it’s my understanding that if I change the format, it will switch from a “number” to “text”. This would prevent me from doing calculations with the date moving forward.
To clarify - do I do a combination of the two? In other words:
When capturing user’s birthday, do I take date input’s value, extract the year, and store it that way? Or do I simply let them input the date, format it mm/dd as @barbufeliciasergiu suggested, and then when doing calculations, do as you’ve suggested?
No need to store anything but the birthday as a Date value, as you’re currently doing. Then, when you want to check if a user’s BD is “today”, the logic would look something like…
When (extract month from BD date) is [equal to] (extract month from current date) AND (extract date from BD date) is [equal to] (extract date from current date), THEN today is their BD!
The “formatted as” functionality is purely for presentation (output/display) purposes.
Thank you!! Just tested this, and it worked for conditional formating. I had been playing with :extract for a while, but didn’t understand putting the date and month back to back as you suggested.
However, it doesn’t appear to work this way in workflows. For example, if I try to set a recurring event, it asks for the date to begin it on. I can’t find a way of inputting the equivalent of 10/16 that it will “accept”, and I can’t seem to use :extract to make it work either.
Oh, I see. You’re trying to schedule something occur on a user’s BD each year? I’ve not had to do that, but a couple approaches come to mind, which might entail rethinking the logic a bit.
Schedule a daily workflow that retrieves a list of all users whose BD is today, and then process them all.
Schedule the workflow to occur on a “target BD date”. The target date would be constructed by setting Current date/time’s date and month equal to the stored BD’s date and month and the year equal to either this year or next year, depending on whether their BD this year has already passed. When that BD workflow is triggered, reschedule it to recur again in a year.
Perhaps someone else has another approach or some visuals to offer.
Yeah, for example, send an email on a user’s birthday - each year. Thanks for the suggestions! I’m playing with both recurring events, and recursive workflows. For some reason I’m having a tough time wrapping my head around which is best for processing lots of users, and the logic that makes the most sense.
I’ll keep digging, but thanks again for the conditional formatting help! That date :extract was never clear to me.
Thanks, I did look through them but somehow didn’t come across this article.
I’ve figured out a workflow using recurring events. The problem is the date in the DB. When I go to schedule a recurring event, and choose their BD as the date, the event repeats endlessly, until it “catches up” to how many workflows it missed.
E.g., the current date is 10/17/19, the user’s birthday is 10/17/1980, the recurring event sends an email yearly, and start date is user’s birthday (10/17/1980). The email will seemingly trigger 39 times, one for each year it “missed” until it gets to 10/17/2020.
I’m guessing I’m going to have to perform some math in the workflow with the start date to get this to work correctly. I’ll do more research and try to report on what I find in case anyone reads this in the future.
Yes, the start date should simply be their next birthday anniversary - not their actual birthdate. A screenshot of your workflow logic might be useful for others to help troubleshoot.
OK, figured it out. I wish I could mark multiple solutions, because I was trying to solve 2 problems:
How to display a user’s DOB (or any past date for that matter) as a date without the year. For that, @sudsy 's solution worked perfect:
" When (extract month from BD date) is [equal to] (extract month from current date) AND (extract date from BD date) is [equal to] (extract date from current date), THEN today is their BD!"
Then, need to schedule recurring event, yearly, on that DOB (or anniversary, etc.). For that, my solution was to create a recurring event, and set the start date as "[current user’s birthday] > change year to current date/time’s year > change hours to “6” (to do the action at 6am, not midnight).
After doing that, I looked at the scheduler, and saw that indeed only 1 event was scheduled in the future - it didn’t send off a chain of events like before.
Thanks so much everyone, hope this helps someone else.
…except that you missed a part. What happens if their BD was earlier THIS year? It’ll fire off a BD greeting if their BD was in May 2019 and your logic runs in October 2019. I’ve emphasized the part you missed below…
Would you handle that having 2 actions, each with conditions? I can’t figure out to do a “greater than” or “less than” equation that’s working to figure this out.
Nice. Yeah, I was just going to add that it’s probably not necessary in this case to create the “target date” from the current date. Since you already have their actual birthdate, you can simply “change year to” whatever you want in order to create a target date that corresponds with a BD anniversary.
