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.
I would have thought you could search and replace CRLF in the string with CRLF + <br>.
REPLACE(pm_description, (char(13) + char(10)), (char(13) + char(10) + ’<br>’)).
You'd need to test though.
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?
Assignments as well?
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
1 of 1 people found this helpful
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!
I think that i'll use the following script as an example for the Response Level notifications in the lc_notification_template table :
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
Any way to get the Call Number on that query?