historical addresses date-stamped?

As you may know, inside the details for each postal address are "Start Date" and "End Date." As I understand it, when you run "Execute an Output Set," if you supply a value for the "Mailing Date" parameter it's supposed to return the address that is/was active on that date, or the primary address if there's no matching address.

However, if your organization is like mine (and everybody else I've talked to about this,) you probably haven't been filling in Start Date and End Date consistently, (if ever!)

Maybe there's an opportunity here for your SQL guru to dig into the audit data as Tom suggests. Perhaps one could script it so that all of your currently inactivated addresses had their start and end dates filled in from the audit data. And then maybe you could then use insert and update triggers to automatically fill in start and end dates as addresses are created and inactivated moving forward?

I haven't tried anything like this myself, but I also would love to hear if you end up with a good solution here.

Yeah, I agree, for example, even though our past home brewed NCOA process did all the work to end date old addresses and create new ones, which seemed like a good idea for these reporting reasons. We saw the same behavior with our users. Just change the existing address in place. Leaving us with no correct record of the past state in T_Address.

Another issue with addresses "as of" is that the standard NCOA process changes the old address in place (just like a user) to the new one without creating a new address that you can go back and look at. This is good for the deliver ability of existing orders and invoices. It may also keep database size down.

Unfortunately, As one of the largest "updaters" of the addresses in our system is the NCOA process. This again means that the only place to find the old address information is in the fragments in the audit logs.

In general, there is often not an intact address record reflecting the past state of an address. To be accurate it would have to be reconstructed.

Another challenge, we have found that multiple addresses were active as of a past date. Figuring out which ones of these was primary or should be attached to a specific order or promotion is a next challenge.

This type of challenges can also happen with membership levels, on membership records, phone numbers, payment schedules, constituency codes. Hold codes, and the list goes on. There are many sections of Tessitura that do not treat data is a transactional manner making it harder to reconstruct past states.

Anyone out there who has solved this class of problem? Or anyone out there with really strong SQL skills, who want to work with me to solve this challenge, and then maybe we can share this with everyone. This is one part of what we call the Holly Grail challenge here at BAM. I'd love to have a good solution for this set of problems.

--Tom

On Thu, Apr 30, 2015 at 8:36 PM, Tessitura Development Forum wrote:

As you may know, inside the details for each postal address are "Start Date" and "End Date." As I understand it, when you run "Execute an Output Set," if you supply a value for the "Mailing Date" parameter it's supposed to return the address that is/was active on that date, or the primary address if there's no matching address.

However, if your organization is like mine (and everybody else I've talked to about this,) you probably haven't been filling in Start Date and End Date consistently, (if ever!)

Maybe there's an opportunity here for your SQL guru to dig into the audit data as Tom suggests. Perhaps one could script it so that all of your currently inactivated addresses had their start and end dates filled in from the audit data. And then maybe you could then use insert and update triggers to automatically fill in start and end dates as addresses are created and inactivated moving forward?

I haven't tried anything like this myself, but I also would love to hear if you end up with a good solution here.
From: Mark Frey
Sent: 4/30/2015 4:26:01 PM
Does anyone know if there is a way to link a constituent’s former address to a particular date? I’m trying to find historical detail on promotions and addresses.
I created a list with a region and a promotion date range, and then ran this through an output set. The detail I get is all based on current address though.
I’m sure this isn’t a common question, but is it possible?
Thanks,
Mark



This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Development Forum. You may reply to this message to post to the Development forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Hey Y'all,

Thanks for all the comments, suggestions, definitions, challenges, etc..  It's all good information and I appreciate it very much.  I don't think we'll be able to do what we want to do at this time, but there is lots to think about for the future.