Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General Discussion » View Thread |
Messages 1 to 10 of 32 total |
Sorting on Alphanumeric Fields |
Thu, May 26 2011 8:07 PM | Permanent Link |
Adam H. | 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. |
Thu, May 26 2011 9:34 PM | Permanent Link |
Jan Ferguson Data Software Solutions, Inc. Team Elevate | 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. |
Fri, May 27 2011 1:24 AM | Permanent Link |
Adam H. | 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. |
Fri, May 27 2011 4:08 AM | Permanent Link |
Uli Becker | 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 |
Fri, May 27 2011 5:52 AM | Permanent Link |
Jan Ferguson Data Software Solutions, Inc. Team Elevate | 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. |
Fri, May 27 2011 10:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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] |
Fri, May 27 2011 12:09 PM | Permanent Link |
Uli Becker | Roy,
> SUBSTR(SalesNumber,1,1) > SalesNumberSuffix > CAST(SUBSTR(SalesNumber,2,Length(SalesNumber) AS INTEGER) Nice! Regards Uli |
Mon, May 30 2011 12:40 AM | Permanent Link |
Adam H. | 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! |
Mon, May 30 2011 12:42 AM | Permanent Link |
Adam H. | 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. |
Mon, May 30 2011 5:48 AM | Permanent Link |
Jan Ferguson Data Software Solutions, Inc. Team Elevate | 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. |
Page 1 of 4 | Next Page » | |
Jump to Page: 1 2 3 4 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |