BAQ Calculated Field on Dashboard

Epicor ERP 9
1

Hi,
I have a calculated field on a baq to get the first 150 characters of the comment field and turn them into a string to get rid of the carriage returns so that when it is copied and pasted into Excel the data stays on one line. My field is string(substring(DMRHead.CommentText,1,150)). When I copy and paste from the BAQ designer all the data stays on one line but when I add the query to a dashboard then copy and paste the data from there into excel it still has the strange breaks onto other lines. Any idea how to fix this?

Thanks :slight_smile:
Melissa

2

I’ve run into some weird control characters embedded in a single field’s data. Particularly the RS (Record Separator, Code: 0x1E, 30dec).

When I clean up data that I expect to copy from a table/grid, I always use multiple substitute() calls for

  • Tab - CHAR(07) -> " " (3 spaces)
  • RS - CHAR(30) -> " " (use at least 1 space to keep words from running together)
  • LF - CHAR(10) -> " "
  • CR - CHAR(13) -> " "

I’ll usually end with Trim() to clear extraneous trailing spaces.

EDIT: I’ll occasionally look for - and replace - non standard ASCII chars

  • Non-breaking spaces - Char(160))
  • Em Dashes and Fancy quotes are most often UTF encoded and require special handling
1 Like