Hi,

Here's one option that could work (although I've not tested it).

Create a new Crystal Report.

Use this query for the report:
SELECT bpc.BPCNUM_0, clb.CLOB_0
FROM SEED.BPCUSTOMER bpc
INNER JOIN SEED.ACLOB clb
ON bpc.BPCNUM_0 = clb.IDENT1_0
WHERE BPCNUM_0 = 'BobForTheWin'


Since the customer notes / comments are stored in ACLOB then you can get to it with a straight SQL read. The only problem is, the values are stored in rich text format. So, perhaps Crystal has a library built in to auto-magically read RTF and convert it for you? No clue. But, if it does, you can then export out to csv or another format you choose from Crystal.

If you don't care about the formatting you can query the data directly in SQL, but I don't think you'll like it.

Another option that might work. I haven't tried this, but Excel might be able to read the RTF directly and convert it. So, in this case, you could make a SQL view of the same data. You can make the view with X3 if you like, or simply direct in SQL like the following:


CREATE VIEW SEED.vGetMyComments AS

SELECT bpc.BPCNUM_0, clb.CLOB_0
FROM SEED.BPCUSTOMER bpc
INNER JOIN SEED.ACLOB clb
ON bpc.BPCNUM_0 = clb.IDENT1_0
WHERE BPCNUM_0 = '000500000001'

Then, you just pull the data into Excel pointing directly to the view. That might just work, let me know if it does.


I haven't looked at a way to do this inside X3 just yet. Just thought I'd offer this in the short run in case it helped you and it's pretty easy to do.

Cheers,
Bob

An Excel cell can hold 32,767 characters. However you would have to handle carriage returns, line feeds, tabs, and any other goofy stuff that may exist as part of your export for it to work in Excel. Side-note, exporting this initially to something other than Excel like Notepad++ or the like will make it easier to look at as you try to track down characters.

The real question is how long is your existing data. Once you strip out that stuff, particularly the CR/LF/Tabs then you'll have a better handle on what you've got (you could use the Len() function in Excel). If nothing is over 250 then it's fine. If you've got lots more than that and you don't want to split it up, then a note makes a lot of sense. If you don't want a note you could make a plain text field on BPCUSTOMER using the data type ACPLAIN. On the screen you can set it to be a multiline text field (and plain text). Of course you'll have to put that field on whatever reports or whatever else you're doing, so it is an additional layer of complexity.

Crystal can display RTF or HTML (although I'm not sure if it'll convert it on export). Right-click the field in question and pick Format Field. On the Paragraph tab there's a drop-down entitled "Text Interpretation". It will interpret none (for plain text), RTF or HTML.

Thanks James and Bob for your replies.

Unfortunately our organization doesn't have a workstation with Crystal Report Designer installed (which has been a huge crutch for us), so I cannot write an SQL statement in Crystal. It appears to be an Excel-based issue, but I tried the query you suggestion (Bob) and the Notes field is not stored in our DB in the ACLOB file, so that didn't work. Is there an out-of-box Crystal report which lists all Notes? I feel like this is my last hope without splitting the strings into multiple columns.

Thankxs

Hi,

Maybe I misunderstood where you were at exactly? Would you be so kind as to give the version you are on and exactly where you see the comments you are referring to in your customer screen (GESBPC)?

Thanks,
Bob

No problem.

The version is V6.5 PU32.

Here is the field layout on GESBPC.

When I hit the F6 key, it shows Data type Alphanumeric (A) (and internal type alphanumeric), with  length of 242 (which is strange because in the table dictionary it states 250). Screen is [M:BPC2].

Hi,

Yes, I checked v7 initially, and I was looking at a different notes section in the customer task. Now that I know you are on v6 I can say yes, this note is definitely not ACLOB. This is the BPCREM_0 field. In standard, this is only dimensioned to a value of 1, which means it will hold 250 characters total, in one field.

Are you storing any characters that are acting like carriage returns and line feeds? And does this happen for all records?

Something you said earlier made it seem like you are already using Excel, and perhaps this is just a display issue in Excel? Maybe try trimming the data that comes back from SQL, see query below. Maybe also try setting some word wrap options in Excel, and also try adjusting the Excel field or column data type.

Here's a query to check how many records where there are more than 250 characters in length:
-- Number of records greater than 250 characters in length (non-unicode check). Should be zero.
SELECT COUNT(*)
FROM DEMO.BPCUSTOMER
WHERE LEN(BPCREM_0) > 250

-- Trim the results
SELECT BPCNUM_0, LTRIM(RTRIM(BPCREM_0))
FROM DEMO.BPCUSTOMER where BPCNUM_0 = 'KIMS2'

Hi Bob,

Thanks for that little query. I did run it with a zero count as expected. I think you are right in that some characters in the string are acting like carriage returns and feeds. Sometimes it appears to be ":" and sometimes "/". I have been reading on how to set word wrap options in Excel, however, I was able to pull a crystal report (Customer list) into Excel and splice it , do a Vlookup to get the notes into my file , so all is good!

Thanks again.

Brilliant!