Data for Birthday Cards

Good afternoon William,

Where are you storing your birth date information?

I know, for example, that one of our organizations is using Attributes to store the birthdates that they collect, and those are being stored as Date data types.

If I create a List, for example, that evaluates that birthdate attribute value between 1/1/1900 and 1/1/2019, I get a list of 195 records that have been tagged with a January birthdate.

Thank you,

Brian

Edit: And I just realized how foolish I was to suggest a List built using that range of dates.

You could potentially build Criteria between 1/1 and 1/31 of every year, but that seems a bit rough.

Another option (if you're on v15) would be to use Analytics and some of those filters to only pull January dates.

Thanks for the response, Brian.  I wish we were on v15, but not yet.  We store birthdays as an Attribute.

It's just rather shocking to me that there isn't a straightforward way within a high-end CRM to accomplish things like this fairly easily.

And in a further note to make myself stick my foot in my mouth, the birthdate attribute isn't something immediately accessible in Analytics (unless I'm just missing it); I'm hopeful we could potentially customize access to that though.

That leaves the last suggestion I have in my mind to be running your List of everyone with a birthday attribute (of any date) through an Output Set that displays that attribute, and then manually adding a month column beside the birthdays that you can use to sort against multiple years.

This would be a great item to request as an enhancement, especially with Tessitura actively working on the constituent records for redesign.

Hi William - our Museum stores birthdays as an attribute as well. You could possibly pull a list of your key constituents, and use an output set for that attribute, then filter by month in Excel. Don't know if you've tried that before, but thought I would mention all the same. Thanks and good luck!

Thanks for the idea.  I've gone down that path, but Attributes are not showing up in my Output Set Editor as an option.  I have an Attributes-Corp/Foundation folder, but not one for individuals, nor does it show up under the other folders.  Any ideas on that?

I think your DBA might be able to add more output set elements for you. I don't know how to attach the document here, but if you search "Output Set Builder Cookbook" on the Network website, and download a word doc called, "03_28_Output_Set_Builder_Cookbook_for_v12_5", page 26 has query elements for Attributes. Thanks again.

William,

If you have access to a DBA you could have them create a relatively quick custom view. And reference this view in a new List Criteria that reference that custom view that shows just the month of a birthdate.  If I was going to do this I’d also add Birth Day of Month, Birth Year, and Age.  Below is a starting point for a DBA.

select customer_no

           ,year(key_value) as birth_year

           ,month(key_value) as birth_month

           ,day(key_value) as birth_day_of_month

           ,DATEDIFF(hour,key_value,GETDATE())/8766 as age

from   VXS_CUST_KEYWORD

where --Based on the Original Standard BirthDate_1 attribute

            keyword_no = 1

            --Cleaning up some bad values stored as birthdate

            and (key_value like '%-%-%' or key_value like '%/%/%')

There is also an Output Set Element discussed in the Output Set Cookbook that can be used to format the Birthdate.

https://www.tessituranetwork.com/Files/Docs/Customizations/Output-Set-Manager-Cookbook

However, Month by itself is not a simple date format.  You would have to do a bit of string parsing as well to just get the month.  This could also be defined in the view above and then used as an output set option.

Sorry that this one is not easy.  Date Parsing is always a bit of a challenge.  If you are trying to do birthday related interactions with patrons.  This relatively small customization would be a good thing.

You might also want to put in an enhancement request to the Network.  Whoever holds the TASK support relationship with the Tessitura Network in your organization can do this with you.

A fairly simple solution may be to use Reminders – formerly called Ticklers.  In fact, check out the Help System topic “Reminders Screen” -- it uses this very scenario as the example.  The free-text Notes field might even suffice to store the address info.  To get your data out, a “Tickler Report” is available in the TASK Shared Reports Library that accommodates begin-and-end date filtering, among other handy parameters. Export it to Excel or save it as a list for all your other reporting or output set needs.

Thanks to everyone for jumping in with the suggestions.  We do not have any IT, much less a dedicated DBA on staff, so getting access that way, while preferred, is challenging.  Until I can figure out that method, Kathleen's suggestion of Reminders will likely fit the bill.  Thanks again!

William,

We do monthly birthday emails to our patrons. We use the following query in an extraction, but it will work in a list too. You just use the Manual Mode or Show Query Mode. By using the wild cards we can pull any year since not everyone gives us years (we just use 9999). You would just change the month (in this case the 10) to what ever month you're looking to pull.

select customer_no
from TX_CUST_KEYWORD
where keyword_no in (1,2,325) -- ids for birthday types
and key_value like '%%%%-10-%%'

Melissa

Hello!  I followed the code provided in this communication string and it worked perfectly!  Question...has anyone enhanced this to exclude deceased or inactive constituents and those who opt out of email communication?  If so, can you share that additional code?  Additionally, does anyone send this weekly and if so, how does that modify the code?  Below is my current list criteria...thanks in advance!