|
carriage returns and line feeds in SQL cause new rows in mail merge word table
Posted: 12 Apr 10 1:14 PM
|
SalesLogix ver 7.2.2.1871
apologies in advance for this long post, but I need some serious help. I've been battling this problem for weeks with no progress!
I have a mail merge word doc used by salespeople as a Quote form. It merges data from opportunity and product tables into a word table using the following SQL query:
select p.actualid as 'item', p.name as name, replace(replace(replace(isnull(convert(varchar(2000), p.description), ''), char(9), ' '), char(13), ' '), char(10), ' ') as 'description',
isnull(op.quantity, 0), isnull(op.calcprice, 0) as 'Unit Price', isnull(op.extendedprice, 0) as 'Ext. Price' from opportunity_product op
left join opportunity_contact oc on (op.opportunityid = oc.opportunityid and oc.contactid = :ContactID)
left join product p on (op.productid = p.productid)
where oc.contactid = :ContactID and oc.opportunityid = :OpportunityID
order by op.sort
now, this query works fine with no errors, however, I have some unhappy sales people. Via the SalesLogix client when the sales people have entered product descriptions they have typed rather long descriptions and have used the "enter" key to create blank lines in between paragraphs. The problem is that they want the quote form description to look exactly like the description field that they've filled in on the Sales client.
I realize that the above SQL code is stripping out Horizontal Tab, Carriage Return, and Line Feed characters and replacing them with a ' ' (space). The reasoning behind putting these replace statements in is to combat what happens if they're not there. During the mail merge when the CR or LF is encountered it creates a new row and starts the second paragraph of the description in the first cell in the new row. This totally throws off the formatting so that something that should look like this:
Item Name Description Quantity Unit Price Ext. Price
part1 part1 line one. 1 $3.50 $3.50
line two.
line three.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
...instead looks like this....
Item Name Description Quantity Unit Price Ext. Price
part1 part1 line one.
line2.
line3. 1 $3.50 $3.50
My question is... does anyone have any thoughts on how to combat this behavior so that it reads the CR / LF characters and simply goes to a new line inside the current cell in the word table?
Any help would be greatly appreciated. I've been staring at the problem for too long and I could use a fresh perspective. |
|
|