Hi all,
I'm not sure if this is an 'all Tessituras do this' or specific to our implementation. When a new account is created through our website, the new record's address is created twice and is modified by the WebAPI instantly, with the duplicate entry being inactivated. So, all new records from the website have an inactive duplicate of their mailing address.
For a while, a couple of us on the team would just update each record as they came in. Because we're kinda small, the volume isn't impractical, but this does mean we're spending time deleting something instead of doing something more productive. Does anyone know a setting or piece of code that is responsible for this that I could update in our system? This wouldn't replace real cleanup, but would be great at not needing to click into each and every new record.
Thanks!
Anthony
Opera Colorado
There's a setting for TNEW -- in T_DEFAULTS, I think -- that saves a copy of the address if the user makes a change on TNEW. So this is standard behavior if you have that option turned on. We have it turned on because otherwise if a patron updates their address on TNEW it will overwrite the existing address and you lose the old address. For us, we would rather have the duplicates come in at account creation and be assured that we have a history of addresses on the account. It comes up sometimes about where something was sent / when or why an address was changed. We find the extra level of detail useful.
We'd also prefer to maintain the old address. Thanks to you & Chris Jensen for pinpointing where that is. That's a really frustrating feature as it does clutter the database with completely redundant information.
Anthony Berkley said:That's a really frustrating feature as it does clutter the database with completely redundant information.
Amen.
Do you do much work in SQL Server? It would be pretty easy to set up a SQL Server Agent job to just delete any of these that have cropped up every night. Okay, not completely trivial: you'd need to clean up attached phones at the same time, but not too hard. Sounds like this would be a useful thing for a lot of TNEW users. I'd never heard of this setting (we don't have it on), and I'm actually going to discuss it with our departments (and organizations) in case they would like it turned on, as we do prefer to keep a record of people's past addresses.
I have a little SQL experience, but with something as significant as automatically update constituent data on an ongoing basis. I was honestly hoping someone else had this issue and they had written a solution to share, but I'll start looking into a process like you describe.
If you end up going this route... I think every SQL database person has their own semi-personalized database procedure for nightly clean-up. I just had a meeting with some internal people that reminded me that it has been a while since I looked at my own and need to update it. There are always certain business rules that preclude anything from being truly universal in this regard. What I will say there is that, as long as you keep it limited to things upon which you are confident, it can be highly effective at getting rid of "everyday issues".
For obvious reasons, do first in your TEST database. But doing so can definitely limit the number of complaints you get from your Box Office/people do actually do the manual clean-up.
Oh, and Contact Point Purposes.
I observe that we have about 14.6k inactive duplicates in our database, and I think it's likely that we might decide to turn on this TNEW setting, so I'm going to go ahead and start working out a procedure.
Hi all, I love a good discussion on data cleanup! :)
Dealing with duplicate addresses (however they get into Tess) has been a concern for our 20+ organization consortium, so years ago I created a script that would set the address type on similar-looking non-primary addresses to a "hidden" address type that only sys admins could see. (The address type is hidden simply by using a control group that no consortium org has access to.) This works for us because then we're not deleting addresses that may be slightly different, since that could have adverse effects, e.g. apartment numbers or postal codes that are off by one character may look similar, but most likely only one is correct.
My script also deletes exact address matches, but as Gawain mentioned you need to be careful about deleting (or updating, for that matter) records since they could be referenced elsewhere in the system. You can check the dependencies in SSMS, but off the top of my head, besides the places that Gawain mentioned, orders and affiliations are other places that reference addresses, so you'd need to look for those references and then either update them to the "kept" address or leave those addresses alone.
Having an official script from TN that could do some robust clean up on these addresses, or even avoid them entirely, at least from TNEW, definitely sounds like an Idea I'd vote on!
Katie Lachance-Duffy said:Hi all, I love a good discussion on data cleanup! :)
Hear, hear!
Katie Lachance-Duffy said:My script also deletes exact address matches,
TNEW leaves their address dupes inactive, which is also helpful re: finding them.
Katie Lachance-Duffy said:Having an official script from TN that could do some robust clean up on these addresses, or even avoid them entirely, at least from TNEW, definitely sounds like an Idea I'd vote on!
Me, too. :-)
Ugh. I count 15 tables with an "address_no" column. That includes T_ADDRESS and T_PHONE, three working tables, one DataImport table and one local (custom) table, so 8 (9 for us) to deal with. But that does not include TX_CONTACT_POINT_PURPOSE since that isn't using the name address_no. Probably that's the only example, but I suppose I should see I can query foreign keys as well.
My staff deplores an inactive address that is a duplicate of an active address. Even if it is not an exact equivalency (which is the only one I am satisfactorily okay with deleting via nightly procedure), they do not want it there. Clean-up is fun!!! Or something like that.