Hi Guys,
We are just starting to incorporate active dates into our postal address TAB in Tessitura and filter by these for mailings.
I can’t seem to see an ‘out of the box’ filter criteria in list manager to use for an extraction.
Do we have to create one? If so that’s fine our SA can do that – but I just wanted to check I wasn’t looking in the wrong area or under the wrong title.
(the logical side of my brain says it should be ‘postal address active date’ or the like??)
Would love some info on this
Cheers!!
Hi Ranie,
Any utility that outputs addresses (like User Defined Format from a List or Extractions) has parameters used to select addresses based on date (as well as address purpose if you choose to use those). If you used criteria in a list or extraction that looked at address dates, you wouldn’t be filtering addresses you would be filtering constituents based on whether or not they have an address that meets the date criteria. That probably would not be the result you want when you consider people who have multiple addresses ( you still would have to choose which one to output) and those who have only one address with no date info.
Kevin Sheehan
Documentation & Learning Resources Specialist
Tessitura Network
1 888 643 5778 ext 329 Office
ksheehan@tessituranetwork.com
I have a similar problem, so my question is, what if we DO want that Kevin?
I'm trying to send a mailing that will only go to constituents who have a Florida address in the fall (Oct or Nov). I know I could do this manually with the spreadsheet that results from my extraction, but I'd really like to track it, as I will be sending the same letter to the remainder of our donor-base at a later date.
Any advice?
For the record, because we're a Florida company and deal with the FL Address v. Northern Address with most of our constituents, we have pretty clean "Month" data.
Hi Andrea,
If you want to pull constituent records based on people with Florida address active in the fall, you will need to add some additional criteria to T_KEYWORD that looks at the active months on addresses. For details on adding criteria to T_KEYWORD see the following help topic:
http://www.tessituranetwork.com/Help_System/Content/System_Tables/T_KEYWORD.htm
I just realized, though, that it might be tricky to add a criterion that looks at the active months, based on the way the data is stored (an array of 12 Ys or Ns). I’m going to ask my coworkers for some thoughts and get back to you with some more specifics. Or if you have good IT resources internally, you could ask them for help while you are waiting to hear back from me.
Senior Documentation & Learning Resources Specialist
+1 888 643 5778 x 329
Hi Kevin,
Thanks for the help! I think we may have added a criterion some time ago called "Month Array Jan thru Dec", I am not sure if this comes standard or not. Yes, you pinpointed my exact issue with the criterion, dealing with the "Y" and "N". I thought trying a wildcard character in the Dec-Sept (the months that don't affect my pull) would work, but it did not.
Unfortunately, we do not have a good IT resource internally. Please let me know if your coworkers have any insights on this issue!
Thank you!
The criterion you mentioned is not standard, so I can’t speak to how it works.
Here’s the solution that my coworker, the talented Ryan Creps, came up with:
You will need to add a separate criterion to T_KEYWORD for each month (so 12 total for Jan-Dec). Based on how the data is stored and the way criteria set work, there isn’t a way to do it with a single criterion where you select the month(s). Instead you add a criterion for each month and then you will choose if you are looking for people where the month is checked (Yes) or not checked (No), which is similar to how the mail purpose criteria function.
Here are the details for the new entries:
Description – Something to indicate which month’s flag the criterion is evaluating.
Data Type – Number
Edit Mask – You can leave blank
Detail Table – vs_address
Detail Col - CASE WHEN CHARINDEX(SUBSTRING(!.months,1,1), 'Y') > 0 THEN 1 ELSE 2 END
You will need to change this a little for each criterion so that it looks at the correct month. The first number in that code is what controls the month. In the example above, it’s set to look at January. To look at October you would change it to:
CASE WHEN CHARINDEX(SUBSTRING(!.months,10,1), 'Y') > 0 THEN 1 ELSE 2 END
Ref Tbl – tr_gooesoft_dropdown
Ref Idcol – id
Ref Desccol – description
Ref Where – code = 1
Ref Sort – leave blank
Category – Address Information (if you want to group it with all the other address criteria, though you can put it in whichever category you want)
Use For List – List Only
All remaining columns, leave alone.
Once you have these new criteria set up, if you want to look at multiple months in an or situation (i.e. an address that is active in October OR November but not necessarily both), you’ll need multiple groupings of criteria, one for each month. If you are just looking at two months, then you can do it in the two panes of a single criteria set. If you are looking at more than one you’ll need to use an extraction set so you can have multiple segments/criteria sets.
Hope that helps and all makes sense.
Hi Andrea
OTOH, if you're only ever going to be interested in pulling people with an active Floridian address in Oct or Nov, you could just create one special criterion, configured the same as what Ryan suggests, except with this Detail Col value instead::
case when (SUBSTRING(months, 10,1) = 'Y' or SUBSTRING(months, 11,1) = 'Y' ) and state = 'FL' then 1 else 2 end
That way, you only need to use the one criterion in your lists for this purpose, which makes things a bit simpler.
Ken