Sort of different

[Sandy Garrett]

I need to sort a list in human language rather than computer, and my brain is stalled. The GS/GSD/GSB can be in a different order, but the 1, 2, 3..., 10, 11A, 11B 1part has me stumped. I've attached the table rather than try to describe it.

I've managed to sort the list by assigning other values (9 BA 2 for GSA 11A 2) for lookups, but we want the operator to be able to Find by key "GSA 10" and page up or down in a form.

[Sandy Garrett]

RE: Sort of different

sorry 'bout that

[Stan Mathews]

RE: Sort of different

Sandy,

I think I see your problem even if I don't see your table. There is an inconsistency in the way your data is structured which is causing your sort problem.

1, 2, 3..., 10, 11A, 11B

should be 01, 02, 03..., 10, 11A, 11B, or 001, 002, 003..., 010, 011A, 011B if you want to allow for higher values. Then a sort on the field would be in the order I think you want. If I understand, you are getting sorts with "GSA 10" prior to "GSA 2", which is alphabetically correct.

Since it seems your character prefix can be two or three characters and you may have a character postfix, trying to pick apart the expression with left(), right(), etc could get verrrrrrrry tricky.

Have you considered splitting this field into it's three components, i.e. a field for the prefix, a field for the number, and a field for the postfix? You could then sort more easily, yet still concatenate the parts when you need the "real" value.

Stan

[Sandy Garrett]

RE: Sort of different

You are quite right about where the sort is going wrong and obeying the alphabet instead of what the ID system's author intended. The trouble comes when a user wants to find one of these things. There is a button with a script

PARENT.index_set("NHPC")
topparent.find()
end

The users know the names as GSB 2A 2 (or such), so I have to do the translating. There is an index that works (built on 1 2A 2 instead), but they would still need to enter the GSB 2A 2 in the find box.

[Bill Warner]

RE: Sort of different

Sandy, I have attached your DBF along with the support files and an index. I THINK I've got what you want. Checking the browse, GSA 9 1A sorts before GSA 10 1A. I also included a form on which I put a FIND button. To make this work, I prompted the user 3 times (once for each component of the code). That's not as convenient as it could be, but it seems to work. If nothing else, it might be a starting point that you can expand on.

[Tom Cone Jr]

RE: Sort of different

Sandy, don't overlook the possibility of using a non-indexed find by text routine. If your list is not overly long performance should be just fine. While a bit slower it will be much easier to implement that indexed translation tables, or so it seems to me.

In fact, if the list is relatively static you might load the whole darn thing into an array which could be searched by brute force quite quickly.

-- tom

[Sandy Garrett]

RE: Sort of different

It works!

if val(b)

[Sandy Garrett]

RE: Sort of different

We need the list in order because we are usually guessing when we look something up. We might try a Find for GSA 9 and then Page Down until the feature we needed showed up on GSA 10B 1 - if GSA 10 has a B and/or a 1.

Would an array be helpful? The table is so static it's positively boring.

[John Walsh]

RE: Sort of different

Dear Sandy,

I believe that the following will do what you want.

Create an index named "order" and use the expression "remspecial(gsusa)"

Then create a button on your form. I've attached a text file with the Xbasic syntax for the button. Hope this helps!

[John Walsh]

RE: Sort of different

In my prior post (and included in the text file) I suggested using the index expression "remspecial(gsusa)". Actually, the remspecial should be left off in the index expression and should simply be "gsusa". The Xbasic on the button is the same.

[Sandy Garrett]

RE: Sort of different

The basic trouble remains - gususa doesn't sort correctly. GSA 10 needs to come after GSA 9 - which is most illogical to the program. I think Bill is on the right track, but I need to pull all the items apart and pad them with spaces or zeros.

"Word" looks promising, but I have a bit more reading to do.

[Tom Cone Jr]

RE: Sort of different

Sandy, check out the ui_get_list_array() function. It might be a good choice is your list is not too long, and doesn't change much.

-- tom

[Stan Mathews]

RE: Sort of different

Wild thought late this evening.

Define an index on your sortlist field:

SUBSTR(GSUSA,3,8).

This index looks like what you want except for

GSA 3 1
GSA 3 10
GSA 3 11
GSA 3 2

Maybe your users could live with this.

Best I can tell everything else sorts correctly.

[Stan Mathews]

RE: Sort of different

Wilder thoughts later...

If your list of GS.... items is static, build it once sorted as they want, in record number order, no indexes.

If the list is infrequently changed, build the list, sorted as they want, in Excel, design an import, let them make changes in Excel and reimport as desired, no indexes.

[csda1]

RE: Sort of different

Sandy and All,

A basic rule of database design is that you always want to control the user's input from the way they like to do things to the way it needs to be done internally.

In a find by key for a simple field, they might bring up the index selection, change the index, then bring up the find by key dialog box, start typing text and do the find.

But, since, as a database designer, you might want to rename the index name, or change the index expression, or other such changes as the design requires, you always want the user to pass through your code, even, if you do what amounts to nothing but pass the values through. That way, if you do make a change, the user does not percieve any operation change, even if you manipulate his input along the way.

For your case, the index expression should be something like;

PADR(WORD(GSUSA,1),5," ")+PADL(WORD(GSUSA,2),3," ")+PADL(WORD(GSUSA,3),2,"")

This provides the sort that you want (or something close - the final expression is left up to you). Now when the user inputs something, they are not (and probably will not) remember to put in the correct number of spaces (nor should they have to). That means that you need to take their input and apply the same translation to get the key value and then do the find-by-key.

Their input could be 1 input that you parse, several separate values that you concatonate or even radio buttons, but whatever your dialog boxes get from them, you have to translate it to your index expression.

I have attached your database with a simple form with a button that has some sample code. Look at the index definition for the table as well.

Alpha 5 (current version 4.5 and earlier) may not always permit you to have your code executed because there may be places that you can't customize (possibly lookup tables in the field rules, print preview etc) and that's a problem, but there are often ways to get around these problems if you work at it.

Another point I'll mention is that many times you want a table to be sorted a particular way that has no appropriate expression that can be derived (for me this is most typically the most likely records 1st, least likely last). When this occurs, I add an "order" field to the database that I index to force the order to whatever I desire. The user never sees this index or field, but it controls what they see.

Regards,

Ira J. Perlow
Computer Systems Design & Associates
[email protected]