Wow Larry, thank you. This is really helpful! We'll look into this solution.
Cristina Lichauco
Collections Database Administrator
Asian Art Museum
200 Larkin Street
San Francisco, CA 94102
E: [log in to unmask]
P: 415-581-3676
Search the on-line collection at http://searchcollection.asianart.org/
-----"Felton, Larry@Parks" <[log in to unmask]> wrote: -----
=======================
To: [log in to unmask]
From: "Felton, Larry@Parks" <[log in to unmask]>
Date: "12-19-2014" "09:57PM"
Subject: Re: Truncated text fields in Crystal Reports
=======================
We also encountered this when we moved to TMS2012 and implemented Unicode capabilities. I believe the problem is related to use of nvarchar (required for Unicode) vs. varchar data types normally used for these long text fields. We are using Crystal Reports 11.
Dimitry first suggested use of the Totext({table.column}) function, but that didn't work for us either. He then advised us to "...create a new view where you cast nvarchar fields as nvarchar(4000)..." That was beyond my capabilities, but our SQL wizard created a view in the TMS database named vObjects_text_fields, using the following query (or something similar; I'm not sure this is the full text):
select objectid, cast([description] as nvarchar(4000)) as [description],
cast([CuratorialRemarks] as nvarchar(4000)) as [CuratorialRemarks]
from dbo.objects
This new view successfully returns the first 4,000 characters of each of the original "memo" fields, but requires tweaking of your Crystal Reports to make use of them. You will need to include the new view in the report, joining it to the Objects table on the ObjectID fields. To print the longer values in your report, use the version of the field included in the new view, rather than the original field in the Objects table.
We've since added several additional long text Objects table fields to the view:
Chat
CreditLine
Exhibitions
Inscribed
Markings
Medium
Notes
We've also added a comparable view for the TextEntries table as well.
Kind of cumbersome, but it works...
Larry Felton
California State Parks
________________________________
From: The Museum System (TMS) Users [[log in to unmask]] on behalf of Cristina Lichauco [[log in to unmask]]
Sent: Friday, December 19, 2014 3:53 PM
To: [log in to unmask]
Subject: Re: Truncated text fields in Crystal Reports
Thanks Jennifer! Just tried this with curatorial remarks and unfortunately it doesn't work for us. Same clipping unfortunately :(
Julie, the field type in Crystal reports for these fields in our case is showing as Memo, not 255.
Cristina Lichauco
Collections Database Administrator
Asian Art Museum
200 Larkin Street
San Francisco, CA 94102
(415) 581-3676
CURRENTLY ON VIEW
Roads of Arabia: Archaeology and History of the Kingdom of Saudi Arabia
October 24, 2014 – Janaury 18, 2015
http://www.asianart.org/exhibitions_index/roads-of-arabia
SEARCH THE COLLECTION:
http://searchcollection.asianart.org/
From: Jennifer McCarthy <[log in to unmask]>
To: [log in to unmask]
Date: 12/19/2014 03:00 PM
Subject: Re: Truncated text fields in Crystal Reports
Sent by: "The Museum System (TMS) Users" <[log in to unmask]>
________________________________
We have had to use a formula to display the content of those fields in their entirety.
ToText(fieldname)
I hope that works for you.
On Dec 19, 2014 5:55 PM, "Cristina Lichauco" <[log in to unmask]<mailto:[log in to unmask]>> wrote:
Hi everyone -
We are having an issue with certain TMS fields being truncated around 250 characters when used in Crystal Reports. GS support has not been able to figure it out.
The affected text fields include Curatorial Remarks, Conservation Overall Testing & Analysis, Text Entries, among others. Any attempt to pull these into a Crystal Report (even the stock ones) ends with the field contents being clipped. These are fields where we often have lengthy text, so it is a problem.
Other text fields, for example the Conservation Line Item text fields, are not affected.
Has anyone else experienced this and been able to identify where the problem may be?
Cristina Lichauco
Collections Database Administrator
Asian Art Museum
200 Larkin Street
San Francisco, CA 94102
(415) 581-3676<tel:%28415%29%20581-3676>
CURRENTLY ON VIEW
Roads of Arabia: Archaeology and History of the Kingdom of Saudi Arabia
October 24, 2014 – Janaury 18, 2015
http://www.asianart.org/exhibitions_index/roads-of-arabia
SEARCH THE COLLECTION:
http://searchcollection.asianart.org/
To unsubscribe, send an email to [log in to unmask]<mailto:[log in to unmask]> with the following commands in the body of the email:
signoff TMSUSERS
// eoj
You will receive a confirmation that your subscription has been removed.
To unsubscribe, send an email to [log in to unmask]<mailto:[log in to unmask]> with the following commands in the body of the email:
signoff TMSUSERS
// eoj
You will receive a confirmation that your subscription has been removed.
To unsubscribe, send an email to [log in to unmask]<mailto:[log in to unmask]> with the following commands in the body of the email:
signoff TMSUSERS
// eoj
You will receive a confirmation that your subscription has been removed.
To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:
signoff TMSUSERS
// eoj
You will receive a confirmation that your subscription has been removed.
To unsubscribe, send an email to [log in to unmask] with the following commands in the body of the email:
signoff TMSUSERS
// eoj
You will receive a confirmation that your subscription has been removed.
|
