Hi all,
Does anyone know of a quick way of updating Constituent Interests based on a list? At the Royal Exchange, we have a list of people who have signed up to email in an external email system and we'd like the Constituent Interests for Data Protection to reflect that.
I've created them as a List in Tessitura so hoped there would be a handy update report (similar to the 'adding an attribute' one). If not, would anyone be happy to share the sql? With Ean Burgon, the only other similar example we found was much more complicated than we need (http://www.tessituranetwork.com/COMMUNITY/forums/t/4082.aspx).
The logic of what we're trying to do is: add a tick to Email_Yes (one of our Constituent Interests) based on a list but leave all the other Constituent Interests the same. It's the logic of 'adding new rows' to the tx_cust_tkw that we're struggling with.
Thanks
Mary
I believe you could use something like:
insert into TX_CUST_TKW(customer_no, tkw, selected)select customer_no,123, -- the tkw # you wat to insert'Y'from t_list_contentswhere list_no = 1234 -- your list_nowhere customer_no not in ( select customer_no from TX_CUST_TKW where tkw = 1234 -- make sure they don't already have tkw)
Hi Chris,
Thank you so much! We made a couple of minor tweaks and this worked perfectly for us (code below for anyone finding this post).
Thanks again
insert into TX_CUST_TKW ( customer_no, tkw, selected) select customer_no, 48 , -- the tkw # you want to insert 'Y' from t_list_contents where list_no = 2554 -- your list_no and customer_no not in ( select customer_no from TX_CUST_TKW where tkw = 48 -- make sure they don't already have tkw
insert
(
select
48
'Y'
from
where
You're welcome, glad to help.
Thank you both. This is exactly what i was looking for to add interests to a number of lists.
Margaret
and thanks again, these years later! Tessitura Forums- the gift that keeps giving :-)
Ditto. Thank you all.
Wahoo! Thanks.
I know this is an old thread, but I saw that people have responded recently and seem happy with this bit of SQL. I'm having a bit of trouble with the end of the query that checks to make sure the constituent doesn't already have the interests. This bit is giving me an error in SQL Server Management.
The query works just fine when I remove that section and setup my list to remove people who already have the keyword. This is fine, but I would prefer to avoid setting up all of my list like that in the future.
Any advice on this is greatly appreciated. Thanks.
Is it because the code pasted in Mary's forum post is missing the last paren?
Actually, the first one is also a problem because they might have the interest, but it is not selected, so you need an update for that.
Nick Reilingh, do you have a thread here with insert/update conditional handling?
Gawain Lavers said:he first one is also a problem because they might have the interest, but it is not selected
A hazard of posting code in the forums. Never do it! ;-)
Today, if writing something new to update TX_CUST_TKW, I'd probably use (or crib from) WP_UPDATE_INTERESTS. It appears to account for all possibilities.
I could have swore I posted it before, but anyway here's the snippet:
https://bitbucket.org/snippets/TN_WebShare/8ez87x
(I set that snippet as public so you shouldn't need a login to see it. As far as I'm concerned, if the network wants it to be private, they should set up SSO :-P
I embody the conditional handling you mention with a MERGE. This is written as a stored procedure that takes @list_no and @tkw_no. Easy-peasy.
Nick, thank you for providing access to what you wrote. Thanks for chiming in with your insights Chris and Gawain. I greatly appreciate the help. I'll let you all know how this works out for us.
Thanks for this script, worked a treat down here, FYI I did a bulk import of new constituents, saved them as a list then ran the script to update all the users in the list with the required 'interet' updated.