Hi,

In an existing DBISAM applicaiton of mine, I allowed users to have Sales
numbers such as:

S1
S2
S3
S10
S11
, etc

The problem I had was that when sorting - the above would sort as follows:

S1
S10
S11
S2
S3

(Understandably).

To combat this, I created a second 'sort' field and assigned an integer
value to it, so the data looked like:

S1, 1
S2, 2
S3, 3
S10, 10
S11, 11

This way I could sort on the second field and the customers would have
the sort order they desired.

Now looking at doing the same for a new project in EDB - am I best to
continue this method (with a second field), or does EDB have some sort
of trick in it that I can use to simplify, and eliminate the need for
the second field?

Cheers

Adam.

Adam,

Why not use S01, S02...S10, etc. This will then sort correctly.
--
Jan Ferguson


Adam H. wrote:

> Hi,
>
> In an existing DBISAM applicaiton of mine, I allowed users to have
> Sales numbers such as:
>
> S1
> S2
> S3
> S10
> S11
> , etc
>
> The problem I had was that when sorting - the above would sort as
> follows:
>
> S1
> S10
> S11
> S2
> S3
>
> (Understandably).
>
> To combat this, I created a second 'sort' field and assigned an
> integer value to it, so the data looked like:
>
> S1, 1
> S2, 2
> S3, 3
> S10, 10
> S11, 11
>
> This way I could sort on the second field and the customers would
> have the sort order they desired.
>
> Now looking at doing the same for a new project in EDB - am I best to
> continue this method (with a second field), or does EDB have some
> sort of trick in it that I can use to simplify, and eliminate the
> need for the second field?
>
> Cheers
>
> Adam.

Hi JB,

Thanks for your reply...

> Why not use S01, S02...S10, etc. This will then sort correctly.

Because this software will be used by USERS who don't understand that
concept.

The other issue is what to do when they roll over to the next digit (ie
when they get to 100, or 1000, etc)?

I'm trying to design the software to be as flexible to the end user as I
can in that regards.

Cheers

Adam.

Adam,

>> Why not use S01, S02...S10, etc. This will then sort correctly.

Jan's suggestion is the only way, you can modify it to S0001, S0002 etc.

If you dont' want the user to enter the value like this, just create a
generated or calculated field, populated by a trigger, that converts
e.g. S1 to S0001.
Then you can sort this columnn.

Regards Uli

Adam,

> > Why not use S01, S02...S10, etc. This will then sort correctly.
>
> Because this software will be used by USERS who don't understand that
> concept.

I think they would be less likely to understand the concept that they
must add another number (integer) in a sequential order to be able to
sort the first salesman "number" they enter. What would happen if they
get out of order in assigning the integer to a salesman's number? You
could then end up with S1/1, S5/2, S2/3, S4/4, etc. Even if you are
using a generated (autoinc) number for the sort field, what would
happen if they entered the salesman's number incorrectly (out of
sequence)?

I don't know your design so I'm just playing devils advocate. It just
seems to me that a second sort field would tend to confuse an end-user
more than not.

--
Jan Ferguson


Adam H. wrote:

> Hi JB,
>
> Thanks for your reply...
>
> > Why not use S01, S02...S10, etc. This will then sort correctly.
>
> Because this software will be used by USERS who don't understand that
> concept.
>
> The other issue is what to do when they roll over to the next digit
> (ie when they get to 100, or 1000, etc)?
>
> I'm trying to design the software to be as flexible to the end user
> as I can in that regards.
>
> Cheers
>
> Adam.

Uli

>Jan's suggestion is the only way, you can modify it to S0001, S0002 etc.

Oh no it isn't

Use ElevateDBs features. Create two new columns

SalesNumberPrefix VARCHAR(1)
SalesNumberSuffix INTEGER

both as computed columns. The SQL for them would be


SalesNumberPrefix
SUBSTR(SalesNumber,1,1)

SalesNumberSuffix
CAST(SUBSTR(SalesNumber,2,Length(SalesNumber) AS INTEGER)

Then set a compound index on those two columns.

This also takes care of changing the prefix or allowing a different prefix. If you're never going to do that then just have SalesNumberSuffix


Roy Lambert [Team Elevate]

Roy,

> SUBSTR(SalesNumber,1,1)
> SalesNumberSuffix
> CAST(SUBSTR(SalesNumber,2,Length(SalesNumber) AS INTEGER)

Nice!

Regards Uli

Indeed - thanks Roy. However, I don't think I need to do this for my
situation. The second 'sort' field will be invisible to the user -
solely used for sorting purposes.

I like the idea of populating it by a trigger Uli - I didn't even think
of that. This would save me having to make the script in every instance!

Hi JB,

Thanks for your reply.

Shouldn't confuse the user. The 'Sort' field may not even be visilbe to
the user - it's sole purpose would be to provide the desired sort order
by the end user.

As for them entering the number out of sequence - that would affect the
sort order too. I've used this sort order approach in DBISam before and
it works well for what I need. I was just wondering if there was a more
efficient way of doing it in EDB, and as per Uli's post - I should check
out triggers!

Cheers

Adam.

Adam,

Understood. As stated, I don't know your design so I was just throwing
things out there for consideration.
--
Jan Ferguson


Adam H. wrote:

> Hi JB,
>
> Thanks for your reply.
>
> Shouldn't confuse the user. The 'Sort' field may not even be visilbe
> to the user...
>
> As for them entering the number out of sequence - that would affect
> the sort order too.