Sorting quote marks

building reports desktopintelligence
1

We are sorting on an alphanumeric field that has three potential formats.
Some data has single quote marks around it, some data has double quote
marks around it and some is plain characters without quote marks.
We want the column to sort on the character data and ignore the quote marks.
It is doing the right thing when the column begins with a single quote. It
appears in the correct order with the non-quoted data. However the double
quote marked data is appearing at the top of the list rather than where it
would sort to if it were without quotes.

Is there some parameter I can set to let B/O know how to treat double
quote marks? Obviously we can do a custom sort, but I don’t want my users
to have to do a custom sort every time they pull this column into a report.

Any suggestions short of changing the data?

We are on B/O 4.1.1, Sybase 11.9.

Debbie Kelly
Museum Informatics Project
University of California, Berkeley

Listserv Archives (BOB member since 2002-06-25)

2

In a message dated 99-02-19 14:03:16 EST, you write:

(stuff deleted)

Is there some parameter I can set to let B/O know how to treat double
quote marks? Obviously we can do a custom sort, but I don’t want my users
to have to do a custom sort every time they pull this column into a report.

Any suggestions short of changing the data?

Instead of changing the data, can you create a variable to remove all quote
marks from the data once it gets into the report? That way you would be
presented with “naked” data, no quotes to get in the way. Of course, if the
quotes are important, then this is not a likely solution.

If your object is called , then a variable would look something like:

= If (Pos( ,“'”) <> 0) Or (Pos(,Char(34))<>0) Then SubStr( ,2
,Length()-2) Else

One assumption: the quotes are always at the beginning and the end of the
string. If a quote is found, then the string is replaced by the same string
with the first and last character chopped off.

Note that you cannot put a double quote in a formula in a variable; the double
quote is used only to surround text values. So the Char() function is used
instead. Char() returns the character specified by the number, and character #
34 is the double quote. You can verify this by putting the formula =char(34)
into an Excel spreadsheet.

Again, if a single or double quote is found in the value, this formula assumes
that it is a quoted string, with quotes on both ends. It removes the quotes
(whether single or double), and should therefore allow you to sort / process
as you see fit.

This is not really “changing” the data, just changing the way it is displayed
in your BusObj documents.

Regards,
Dave Rathbun
Integra Solutions
www.islink.com

Listserv Archives (BOB member since 2002-06-25)

3

You can create a variable with the formula specified and include it in
report but hide it (goto table properties, select the object on right side
list, click Hide) then you can put a sort on this hidden object.

Listserv Archives (BOB member since 2002-06-25)