I've noticed a strange issue causing item long descriptions in MAS500 (timItemDescription.LongDesc) to have hidden control characters, specifically line feeds (CHAR(10)) and carriage returns (CHAR(13)). This causes some odd things to happen when reports are generated, especially in Knowledge Sync.
I made a SQL query to fix this issue in the table, so I figured I would share that here:
1. Obtain a list of items:
SELECT timitem.ItemID, timItemDescription.LongDesc, LTRIM(RTRIM(REPLACE(REPLACE(timItemDescription.LongDesc,CHAR(13),''),CHAR(10),'')))
FROM timItemDescription
INNER JOIN timItem ON timItemDescription.ItemKey=timItem.ItemKey
WHERE timItem.CompanyID = '<ENTER COMPANY NAME HERE>'
AND REPLACE(REPLACE(timItemDescription.LongDesc,CHAR(13),''),CHAR(10),'') <> timItemDescription.LongDesc
2. Update all the records to remove carriage returns, line feeds and leading and trailing spaces:
UPDATE timItemDescription
SET timItemDescription.LongDesc = LTRIM(RTRIM(REPLACE(REPLACE(timItemDescription.LongDesc,CHAR(13),''),CHAR(10),'')))
WHERE timItemDescription.ItemKey IN (
SELECT timItemDescription.ItemKey
FROM timItemDescription
INNER JOIN timItem ON timItemDescription.ItemKey = timItem.ItemKey
WHERE timItem.CompanyID = '<ENTER COMPANY NAME HERE>'
AND REPLACE(REPLACE(timItemDescription.LongDesc,CHAR(13),''),CHAR(10),'') <> timItemDescription.LongDesc)
Does anyone else experience this problem? It is occurring in MAS500 7.3, August 2010 update. I'm not sure if it is a coding oversight where there is failure to properly sanitize these characters on the input field, or a modification causing the screen to behave abnormally. I suspect users are copy and pasting from Excel into this field, but not sure.
