9 Replies Latest reply on Apr 15, 2016 9:56 AM by PJohns

    Globally replace domain name in email field

    RGarci1 Rookie

      Anyone have a tip on how to globally replace just the domain name in the email field? We have a customer who was bought out by another company and everyone's domain name is going to change e.g. robert.smith@widget.com will become robert.smith@gizmo.com.

       

      p.s. This is a syncing GoldMine environment, so changes need to be sync-aware...

        • 1. Re: Globally replace domain name in email field
          JMessi Apprentice

          I had to do this for a client once.  But you can't do it within GoldMine it has to be done in SQL Studio Manager.  So you would have to do it on each remote.

          Changing Domain section of emails      (BACKUP SQL FIRST!)

           

          THIS WILL SELECT AND MAKE SURE YOU HAVE WHAT YOU ARE LOOKING FOR.  SHOWS THE WAY IT IS IN THE DATABASE AND HOW IT WILL LOOK AFTER YOU DO THE SECOND QUERY.

          select contsupref,

          SUBSTRING(contsupref, 1, charindex('@',contsupref)) + 'group-miki.com'

          from contsupp

          where contsupref like '%@uk.miki.travel%' or contsupref like '%@jp.miki.travel%' or contsupref like '% @no.miki.travel%' or contsupref like '%@mikitravel%'

           

          THIS ONE WILL DO THE CHANGES IF THE FIRST QUERY IS THE RESULTS YOU WANT.

          update contsupp

          set contsupref = SUBSTRING(contsupref, 1, charindex('@',contsupref)) + 'group-miki.com'

          where contsupref like '%@uk.miki.travel%' or contsupref like '%@jp.miki.travel%' or contsupref like '% @no.miki.travel%' or contsupref like '%@mikitravel%'

           

          If you could create a table of the recids then you could run the following sql query that should work to update contlog table.  I am not sure on  this maybe some can pipe in here for this part.  Otherwise you would have to run the update query above on each remote.

          declare @syncstamp varchar (7)

           

          update conttlog

          set syncstamp = @syncstamp,

          logstamp = @syncstamp

          where frecid in

          (select * from recids)

          • 2. Re: Globally replace domain name in email field
            DHunt Specialist

            In a meeting all day today.  Look at it later.

             

            Sent from Outlook Mobile

            • 3. Re: Globally replace domain name in email field
              MBerry1 Rookie

              Building on Jill's WHERE clause, I'd suggest restricting it to E-mail Address records (otherwise it could affect e.g. an "Account Username" where they keep web site logons, which can also contain @ signs):

               

              where RecType = 'P' and Contact = 'E-mail Address'
                 and (contsupref like '%@uk.miki.travel%' or contsupref like '%@jp.miki.travel%' or contsupref like '% @no.miki.travel%' or contsupref like '%@mikitravel%')

               

              My memory on ContTLog manipulation is that you would insert a new record identifying the field you changed. And of course you'd need to initialize @syncstamp with the current date/time converted to a syncstamp.

              • 4. Re: Globally replace domain name in email field
                Shaul.Bel Apprentice

                If you want just to change the contact email addresses I think you better do it one by one by hand even you will have to do it 20 times.

                 

                If it's more then that, it may be better to do it with update query but limit the query with the contact accountno.

                 

                If you want also to update conttlog it's more complicated because on each record that you would want to update you will have to seek the recid in CONTTLOG and see the value of fieldname. If it's zzNew you will just have to update the logstamp and syncstamp field.

                If the value fieldname is zsNew it means the record was already synced and you have to insert a new record into CONTTLOG with new syncstamp and new logstamp update the fieldname and userid.

                The best way is to do it with GM API that will do it all.

                1 of 1 people found this helpful
                • 5. Re: Globally replace domain name in email field
                  KSimps Apprentice

                  There is also another issue that needs to be taken into account.  When the email address is longer than 40 characters, the rest of the email address rolls to contsupp.address1, which means that to search and replace the domain name you need to concatenate contsupref with address1, perform the replace, and then place the first 40 characters in contsupref and the remaining characters (if any) in address1.  You would then need to update tlogs for both contsupref as well as address1.  The use of address1 does not happen a lot, but I do see it more if the email addresses are longer, like firstname.lastname,

                   

                  That being said, Shaul is probably correct in that it's better to just manually update them.  If there are too many to do by hand, then something like DBX 4 GoldMine should do the job, albeit a more costly solution for something that does not typically happen that often.

                   

                  Sorry to muddy the water, but I hope it helps.

                  1 of 1 people found this helpful
                  • 6. Re: Globally replace domain name in email field
                    DCastell Expert

                    I tried to post this last week, but the forum was having trouble, so I e-mailed Ron, directly.  So, here's my reply, completely ignoring earlier replies...

                    Saul Bell posted this script on the old forum a while back and I tucked a copy of it away

                    declare @seekval as nvarchar(40) ='@test.ne.il' 
                    declare @replaceval as nvarchar(40)='@XXX.co.il' 
                    declare @recid as varchar(15) 
                    
                    
                    declare TableCursor CURSOR for select recid from contsupp where rectype='P' and u_contact='E-MAIL ADDRESS' and contsupref + isnull(adress1, '') like '%'+@seekval+'%' )
                    
                    
                    open TableCursor 
                    
                    
                    fetch next from TableCursor into @recid 
                    while @@FETCH_STATUS=0 
                    
                    
                    begin 
                    
                    
                    update CONTSUPP set CONTSUPREF =left(replace(contsupref + isnull(address1,'') ,@seekval,@replaceval),40),address1=substring(replace(contsupref + isnull(address1,'') ,@seekval,@replaceval),41,40),u_contsupref=left(replace(u_contsupref + isnull(u_address1,''),upper(@seekval),upper(@replaceval)),40),u_address1=substring(replace(u_contsupref + isnull(u_address1,''),upper(@seekval),upper(@replaceval)),41,40) 
                    where recid=@recid 
                    
                    
                    fetch next from TableCursor into @recid 
                    end 
                    
                    
                    deallocate TableCursor 
                    go
                    

                    Of course, it's not sync aware... 
                    One could make it sync aware, however, by adding in a call to the sync logging stored procedure included with my CastellRecNoGen product.


                    ... On the subject of sync logs, the problem is that there may or may not be a sync log for the particular record.  If there is one, you'd want to update it, if there isn't, you'd want to insert one...  The stored procedure related to sync logging that's included with my product takes care of that logic. 

                    1 of 1 people found this helpful
                    • 7. Re: Globally replace domain name in email field
                      PJohns Apprentice

                      I am with Shaul on this for the effort require I would copy the domain name and paste in by editing each address. This will take care of long addresses and make it sync aware.

                      • 8. Re: Globally replace domain name in email field
                        RMacha Apprentice

                        Without all this technical stuff, this task could have been handled using MasterMine for GoldMine.  It's synch-aware.  It automatically takes care of both places that e-mails are stored.  It's easy to do (as easy as find/replace in Excel).  It's safe and visually reassuring because you make all the changes in Excel before they happen in GoldMine, so you know exactly what's going to be updated.  And finally, it's easily reproducible, so if this need arises again or even frequently for different sets of e-mail addresses it doesn't require someone to pick through SQL queries to figure out what needs to be changed.

                         

                        This and a thousand other GoldMine data administration tasks are best handled with MasterMine for GoldMine.  Sorry for the product plug, but sometimes it's just better to have a good general-purpose tool than a lot of task-specific advice.

                        • 9. Re: Globally replace domain name in email field
                          PJohns Apprentice

                          Yes of course. Sorry Rob I should have thought of that. REALLY easy with Mastermine !!!