• Welcome to the upgraded JDELIST forum and thank you for your patience.
    Please restrict discussions and issues regarding the new forum software to the Off Topic forum. We will be monitoring that forum for issues.
    If you have trouble logging in, please reset your password using the forgotten password form: https://www.jdelist.com/community/index.php?lost-password/
    If you are unable to successfully reset your password, please contact us: Click here!
    We hope that you enjoy the upgraded forum.
  • Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

How to Access Media Objects Text from Crystal


Since I mentioned on a thread several weeks ago that we do this I have had several private requests for "how do you do that?". Here's the answer in brief:

1. first thing to do is to determine the Media Object Data Structure for the Document text you want. For example Address Book is ABGT, Item Master is GT4101, SO Detail is GT4211A, ... You can browse these definitions in OMW by searching for objects beginning with GT* (98% begin with GT).
2. use UTB to open F00165 and look at the data. Type the data structure name into the GDOBNM field and press Find. You should then see the media objects for whatever type of document/object you selected.
3. Now look at the GDTXKY (Generic Text Key) field. This is a string field that contains the key value appropriate to the parent object/document. If the parent is the address book then this will be an address book number. If more than one field is needed (such as in SO Detail) multiple field values are concatenated into this TXKY field, separated by |. Here's an example for SO Detail / GT4211A: "42016|SO|00022|1.000" (SO# + Order Type + Company + Line Number).
4. The table structure for F00165 changed starting at the 8.9 release. If you are on 8.0 or below the actual Text is in the GDTXVC field. If you are on 8.9 and above the text is in the the GDTXFT field in UniCode format.
5. Now you can use this information in a Crystal report. For F00165 you must use a sub-report. In the main report construct the value of the Generic Text Key field using a formula. Remember that you must convert numeric values to strings and that the format must look exactly like what you see in UTB. Pass your Generic Text string formula to the subreport as a subreport link. In your subreport you should only have 1 table - F00165. Record Selection should be based on: a) GDOBNM (MO Data Structure code), b) GDTXKY (that u pass in from main report), c) If version is below 8.9 then GDTXTL > 0, Else GDGTITNM = 'Text'. (GDTXKL contains the approximate length of the text - if its 0 then the Media Object is not text).
6. Place GDTXVC / GDTXFT as the only field to show in the detail section of the subreport. Change the Paragraph Formatting of the field so that "Text Interpretation" is set to "RTF Text"

Try it - it should work!

Under XE in a database view I manipulated the returned text to truncate some garbage characters which JDE always appended at the end of a text attachment. I think I may have also converted the text to a large VARCHAR field.
Under 8.9 the F00165 table changed quite a bit and due to UNICODE format I had to manipulate the text field substantially in order for Crystal 8 to view it (later versions of Crystal understand Unicode).


This sounds relatively simple and I was able to work through it until step 6. I could confirm that the key I was using to link was correct by temporarily placing it on the subreport, but when I went to place GDTXFT on the subreport, I find it is defined in the field list as a Picture. I was unable to put it on the subreport, and even if I had, the paragraph formatting option would not have been available. (CR v 10) Any words of wisdom on this problem would be much appreciated.