I'm wanting us to send birthday cards to volunteers, key donors and prospects, and the like. While I can look at individual records, I cannot seem to find a way to pull a data set (name, address, birth date, etc.) based on a birth date range. For instance, a list of records that have birthdays in the month of January.
Anyone doing something similar, whether for birthdays, or some other data point?
Thanks.
William
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.