sort by memo field supported in 9 or 10? 1

[LCD]

I've gathered from searching the forum that this isn't doable in 8.5 (what I currently have) since the field I'm working with is 1024 chars.

Can anyone tell me if this is an available option in 9 or 10?

Thanks,
Andrew

 

[Mike Lewis]

Andrew,

I have a report with a memo field in VF 9.0. I just tried to sort it, but the memo field does not show up in the Record Sort Expert, so I guess the answer is No.

However, it might be back-end dependent. My report uses a Visual Foxpro back-end. It might work differently with other databases.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[LCD]

thank you. Guess I'm just going to have to get 10 and see what happens.

 

[synapsevampire]

Do you need that depth of sortation? That's generally considered a poor design to have to sort on that level as it's resource intensive.

If sorting by the first 254 chars (or some block(s) of 254 chars) will suffice, create a SQL Expression which takes a substring of 254 chars and then sort on that.

This is of course database dependent.

-k

 

[LCD]

I've considered doing that as a last option, but I believe it's the only way to solve the problem now. Just working with another companies data and didn't want to change anything in the DB. But it looks to be the only option, so it will be done.
Thank you.

 

[Mike Lewis]

Andrew,

I doubt if it will be any different in 10.0. At least, there is no mention of it in the "what's new" list.

Synapsevampire made good point. It is not always a good idea to sort on an entire memo field. Often, you will get the result you want by sorting on the first so-many characters.

Doing that won't change anything in the database. Just create a SQL expression within CR, and sort on that. Nor is it back-end dependent. It should work with any so-called 'SQL database', not just true client-server databases like SQL Server.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[Naith]

Hey Andrew,

The SQL expression reference is sound advice. Sorting on a full free text field is probably moot, but if you use the cast function (

CAST(Your.Field to VARCHAR(249))

) on your memo field to a varchar, and sort on a substring of that, you should be okay.

Alternatively, you can fool Crystal into sorting on a memo field by creating a SQL expression substring on a string field and then modifying the same expression to switch to memo, which will usually be accepted in most instances where you're using a backend like Oracle or SQL Server.

e.g.

{fn SUBSTRING ( Your."VarcharField" , 1 , 25 ) }

save, then modify the above to:

{fn SUBSTRING ( Your."MemoField" , 1 , 249 ) }

which you will subsequently be able to sort on.

All the best,

Naith

 

[LCD]

---Quote
if you use the cast function (CAST(Your.Field to VARCHAR(249))) on your memo field to a varchar
---/Quote

I ended up modifying the view I was using and added
CAST(My.Field AS VarChar(30)) AS NewFieldName
to it. Thanks for the idea, this SQL n00b wasn't familiar w/ CAST.

 

[synapsevampire]

It does a whole lot more than just substrings, you can use it for correlated subqueries, etc.

I hadn't seen your response wherein you misunderstood what a SQL Expression is/was.

There's a keyword search here, when in doubt, try it, as this is a VERY common request here and you would have instantly found examples.

-k

 

[LCD]

I wasn't have trouble with the expression, just thought I'd thank the guy for pointing out CAST since I've never used it before. Thank you for your help too.