Before me sits 1,555 returned brochures (that is, they were undeliverable via postal mail ... yuck!) and the daunting task of going through this pile of loveliness and designating these folks in Tessitura with the appropriate mail restriction for each corresponding record.
Luckily, each brochure has the constituent ID number above the address, so I've made quick work of keying a spreadsheet in with all the appropriate IDs. I've also used List Manager and Extraction Manager to weed through folks that shouldn't have been mailed to begin with (this has been a data disaster--around 460 constituents that were Inactive or already had a Mail Restriction somehow ended up on the list ... Luckily for me, I'm not the responsible party--i.e--not my list, though I get the pleasure of cleaning up after it), and before I set to manually updating each remaining individual record, I wonder if there was a significantly smarter/easier way to do this.
I have a limited working knowledge of (and, more dangerously, full access to) SQL (I use it SO infrequently that I spent more time reacquainting myself with it than actually performing the task at hand), but I'm not afraid to dive into my test server to try (break) a few things if that's what it takes. I'm not averse to staying within Tessitura if there's an easy route there as well ... I've already imported my spreadsheet via List Manager (which of course ferrets out the Inactives off the bat), any thoughts on this are MUCH APPRECIATED!
Hi Brian,
I'm a SQL newbie so know the feeling of not being all that comfortable. I think this should do it though:
Update T_CUSTOMER
set mail_ind = [enter id_no from TR_MAIL_IND that you want to change it to]
where customer_no in (select customer_no from T_LIST_CONTENTS where list_no = [your_list_no_here])
Tash
Do you do your mail restrictions as an attribute or on the general tab? If you use attributes you could run the Manage Attributes report against the list and do it that way. If not try something like
update c
set mail_ind=<whatever the id is for the restriction you need, get this from tr_mail_ind>
from t_customer c
inner join t_list_contents l on l.customer_no=c.customer_no
where list_no=<that list you created>
Brian,
Here is a script that I use to update the mail restriction.
use impresario
begin tran
update t_customer
set mail_ind = '5' — Enter your Id_no
from t_customer a
JOIN T_list_contents B (NOLOCK) on a.customer_NO = b.customer_no
where b.list_no = **** (Your list number)
rollback tran
--commit tran
Thanks,
Bobby Moseley
601 Preston
Houston, TX 77002
From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Brian Jones Sent: Thursday, March 08, 2012 3:00 PM To: Moseley, Bobby Subject: [Tessitura Technical Forum] Altering a collection of constituent records
This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical 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!
We took this same basic script and turned it into a procedure that I use all the time
ALTER PROCEDURE [dbo].[LP_UPDATE_RESTRICTIONS_BY_LIST] ( @list_no int, @restriction_type varchar(30), @restriction int)
ASBEGINSET NOCOUNT ON;IF @restriction_type = 'tr_mail_ind' BEGIN UPDATE t_customer SET mail_ind = @restriction FROM t_customer c WHERE c.customer_no in (select customer_no from t_list_contents where list_no = @list_no) ENDIF @restriction_type = 'tr_phone_ind' BEGIN UPDATE t_customer SET phone_ind = @restriction FROM t_customer c WHERE c.customer_no in (select customer_no from t_list_contents where list_no = @list_no) ENDIF @restriction_type = 'tr_emrkt_ind' BEGIN UPDATE t_customer SET emarket_ind = @restriction FROM t_customer c WHERE c.customer_no in (select customer_no from t_list_contents where list_no = @list_no) ENDEND
You all rock! I've been pulled away from this for a few days due to some illnesses around the office (we all wear so many hats, don't we?), but really appreciate your responses. This has made life SOOOO much easier in this regard, I'm definitely keeping this all in the cache!
THANK YOU!!