1 Reply by Matt_tullie

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: Memo fields and formatting

A recurring issue for me is the export and reporting of comment fields.

For one reason or another there are a swathe of formatting types and dodgy characters hiding in these fields that regularly distort the output. Currently I dont output the comments fields for this reason - too much hassle.

However, I would like to do a block edit on the comments fields and remove all the excess formatting, tabs, slashes etc etc.

Is their an easy way of doing this?
Within SQL perhaps.

Thoughts gratefully received.

M

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

2 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Memo fields and formatting

The formatting code you're seeing are, I believe, RTF code (don't have Recorder on this computer to confirm that unfortunately). There is a function in the database that can strip RTF codes (I don't recall its name, but I'm pretty sure it is in there). You could run an update query using this function to clean up the comments. Alternatively, you could just use the function whenever you do a report.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

3 Reply by sallyrankin

  • sallyrankin
  • Administrator
  • Offline
  • Registered: 07-04-2006
  • Posts: 371

Re: Memo fields and formatting

The function provided by Dorset Software is listed at the end of the network installation guide on the installation CDs or at http://forums.nbn.org.uk/viewtopic.php?pid=2013#p2013. Mike Weideli has also produced things like this.

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

4 Reply by Matt_tullie (edited by Matt_tullie 12-12-2008 11:59:20)

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: Memo fields and formatting

The function seems to work fine for queries so thanks for that.

Next difficulty...

I would like to update all the comments values in various tables using this function.

To date I havent updated a table directly like this - only cross referenced to another set of table values.

Would anyone care to point me towards the right syntax for changing a cell contents with reference to itself?

update location_designation
set location_designation.comment = dbo. ufn_RtfToPlaintext(COMMENT)

For instance?

M

The syntax below seems to work;

update location_designation
set location_designation.comment = dbo. ufn_RtfToPlaintext(location_designation.COMMENT)

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website

5 Reply by Matt_tullie

  • Matt_tullie
  • Veteran
  • Offline
  • From: Carlisle, Cumbria
  • Registered: 18-05-2007
  • Posts: 166

Re: Memo fields and formatting

What I dont understand - is there would seem to be cells that are populated by RTF formatting only.

Its not that text has been deleted from these - nothing was entered in the first place.
Which is backed up by a test just run on location designations added through the term list, and would seem to be duplicated by other terms.

SO, is that the default behaviour for the term list?
For comment fields more widely?
- to add rtf formatting to the comments by default.

thanks
M

Cumbria Biodiversity Data Centre
Tullie House Museum

Matt_tullie's Website