8 Replies Latest reply on Jun 28, 2012 2:21 AM by gramsay

    Script to batch change to html email format

    jasoncadman Expert

      Hi

       

      Is it possible to change the formatting of the text to include html tags using and update script in the database

       

      I'm just rummaging around and it looks like the table lc_notification_template contains the notifications so i'm thinking that I could be lazy and add a <BR> here and there and it would be ready to go on a basic level

       

      Thanks

       

       

      Jason

        • 1. Re: Script to batch change to html email format
          Expert

          Hi Jason,

           

          You would use something like the REPLACE command in SQL, combined with  LEN and CHARINDEX to locate the positions you want to add these tags and  insert them. You also need to triple confirm you are updating data in the right table before editing the database directly. I know support would probably suggest you don't edit the database directly unless absolutely necessary.

           

          Batch updating these via SQL wouldn't really work though because unless your notifications are identical in every part of the process, both assignments and reminders, you would have different character lengths to accomodate before inserting a <BR> or any other tag. The only way a scripted method would work would be A) Script every notification individually or B) Use the exact same notification text in every process notification.

           

          The time spent developing such a script would probably be longer than just editing the notifications manually, which poses another question- If these are Process notifications why don't you simply enable HTML on those fields and format them? If they are SLA notifications it is even easier to modify them so probably not worth the time and effort developing a script.

           

          Cheers,

          Hadyn

          • 2. Re: Script to batch change to html email format
            JulianWigman Expert

            I would have thought you could search and replace CRLF in the string with CRLF + <br>.

             

            Something like:

             

            REPLACE(pm_description, (char(13) + char(10)), (char(13) + char(10) + ’<br>’)).

             

            You'd need to test though.

             

            Julian

            • 3. Re: Script to batch change to html email format
              jasoncadman Expert

              I've thought about this some more and you can also use a <PRE> and </PRE> tags as well. So adding this to the beginning and end of each notification would make us ready for the html switch over.

               

              Is it only the lc_notification_template table that contains the notifications?

               

              thanks

               

              Jason

              • 4. Re: Script to batch change to html email format
                dmshimself ITSMMVPGroup

                Assignments as well?

                • 5. Re: Script to batch change to html email format
                  jasoncadman Expert

                  Good question Dave!

                   

                  pm_process_assignments is just individual instances of assignments rather than the template

                   

                  lc_assignment_template was empty

                   

                  in fact i can't find it in any table like

                   

                  assign

                  notif

                  template

                   

                  Any ideas?

                  • 6. Re: Script to batch change to html email format
                    dmshimself ITSMMVPGroup

                    The lifecycle action value table is the one to look at.  You can look at it in LDSD which is handy.  The value attribute has values in it for reminder summary and details as well as assignment.  The action allows you to see if the action is a reminder or an assignment for the process.  Add i nthe lifecycle used and you can narrow things down quite quickly.  I usualyl exclude any vlaues beginning with things like $currentuser, Process/, Incident/ to make it easier on the eye.

                     

                    Such a query is very handy to see all the subject lines to check for spelling and all the detail lines for the exact contents of the HTML. 

                     

                    It will not show an entry if there is no plain text in the default assignment action value in the process at that spot, but if there is, it just shows the plain text so you can spot what needs to be changed and use that for your scripting.

                     

                    However ... I persoanlly don't script this as it's a bit easy to get it wrong.  (Esp when you type as badly as me).  Instead I open the lifecycle action value window with the text in it with *all* the other attributes on that window read only and copy/paste from elsewhere.  Your milage may vary!

                    1 of 1 people found this helpful
                    • 7. Re: Script to batch change to html email format
                      jasoncadman Expert

                      Thanks eveyone

                       

                      I think that i'll use the following script as an example for the Response Level notifications in the lc_notification_template table :

                       

                      UPDATE lc_notification_template

                      set lc_body=CAST('<PRE>' as nvarchar(max)) + CAST(lc_body as nvarchar(max)) + CAST('</PRE>' as nvarchar(max) ) 

                       

                      That should make them html compliant

                       

                      Then use the query that Dave suggested as a check that I have edited all the notifications after going through my processes one at a time

                       

                      I think that there is probably too many variations to ensure that you're scripting the correct Value line in the Lifecycle Action Value table

                       

                       

                      Here goes!

                      • 8. Re: Script to batch change to html email format
                        gramsay Specialist

                        Any way to get the Call Number on that query?