• Replies 5 replies
  • Subscribers 338 subscribers
  • Views 33 views
  • Users 0 members are here
Related

Altering a collection of constituent records

Brian Jones
$organization

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!

Parents
  • $organization

    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

     

    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!




    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!

  • $organization in reply to Bobby Moseley

    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)

    AS
    BEGIN
    SET 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)
        END
    IF @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)
        END
    IF @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)
        END

    END

Reply
  • $organization in reply to Bobby Moseley

    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)

    AS
    BEGIN
    SET 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)
        END
    IF @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)
        END
    IF @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)
        END

    END

Children
No Data