Has anyone written code to insert a value into the mail_purpose field in T_EADDRESS without overwriting the existing values in that field? This field has 10 positions and I want to insert a value into one of the positions without erasing what's there. So the SET command won't work. I'm thinking there is a way to identify the position of the value you want to enter.
Hi Gloria,
Here is a simple function that adds and removes a single single purpose. It takes the current purposes string, the purpose to add or remove, and a mode parameter to tell it whether to add or remove. It returns the updated purposes string.
Jon
I think you're my hero Jon!
So I would need to include this function in a procedure, right (excuse my ignorance with functions)? What is the mode parameter - 1=add, 2=remove?
Then I can run this for a list?
I found that this works:
UPDATE t_eaddressSET Mail_Purposes=Mail_Purposes+'3'WHERE customer_no = 26824 AND CHARINDEX('3',Mail_Purposes)=0
But it doesn't put the purposes in the right order.
Do the values have to be in the right order?
You what create this function in your database. The mode parameter is 0 for remove and 1 for add. This could be run for a list example:
update t_eaddress set mail_purposes= dbo.LF_UPDATE_EPURPOSES(mail_purposes,'3',1) where customer_no=2187966
update
t_eaddress set mail_purposes= dbo.LF_UPDATE_EPURPOSES(mail_purposes,'3',1) where customer_no=2187966
This adds the 3 purpose to my email address purposes record.
Yes it puts them in the correct order.
No the order does not have to be in the correct order. Tessitura adds them in the correct order. This fucntion ensures the order as well. One thing to note is it only add/removes one purpose at a time.
Thanks so much Jon. This works great.
Glad I could help.
This was exactly what I needed today. Thank you!
Great! I am glad it helped.