Export data to .xls and MEMO datatype

[huytonscouser]

I have a formula that "Joins" data to create a single string.

uppercase(Join(data,","))



This formula can return strings in excess of 255 characters.



But when exported to excel, if the 1st row is not > 255 characters then the field
type is String[255] and some data is truncated.



When the 1st row(or when i force it) is > 255 characters, then the field type is
Memo and all the joined strings are present.



So whats the best way to always ensure the formula field always produces at least 256 chars,
so that the export field will yield a Memo type?



1. Pad the data with "spaces" upto 256 characters.
2. Is there anyway to force a memo type ?



Thanks in advance.

 

[pmax9999]

I am not aware of any way to control the way the data gets exported natively. There are probably 3rd party applications that could do it for you.

The simplest way would be to pad the data out to a minimum of 256 characters, and the easiest way would be as follows:

{@Your Formula} + ReplicateString(' ',256)

This simply adds 256 spaces to the end of whatever is returned by your formula.

You could take it a bit further by only adding on the number of characters required to increase the length of the string to 256, with something like this:

If      Len({@Your Formula}) < 256 
Then    {@Your Formula} + ReplicateString(' ',255 - Len({@Your Formula}))
Else    {@Your Formula}

Cheers
Pete

 

[huytonscouser]

Thanks Pete, either of those options worked.

Sure would be nice for the export function of crystal to flag it detected > 255 characters, and the data has been truncated as the 1st rows was < 255 characters..

 

[huytonscouser]

Ok, now i've corrected the data, and it exports fine as a MEMO type. I am able to add the Memo field in the details section.

Now i cannot create a group on the memo type

Is this a known Crystal restriction ?

i am using CR11.

Do i need a new forum entry for this ?

Thanks in advance.

 

[pmax9999]

As I understand it, it isn't possible to group on a Memo field.

It may not be acceptable for your requirements but I would try grouping on the original database column itself, but displaying the result of the formula rather than the actul group name.

Cheers
Pete.

 

[huytonscouser]

No problem Pete, if i create a new formula from the Memo field, i can then group on it. Problem solved. Thanks