need sorting suggestion

[martinwcole]

i need to add a field(S) to a table to use for sorting.
the first 3 digits would identify MANUFACTURERS
001 thru 999

the second two digits would be product STYLE
01 thru 99

and the last two digits would be for TYPE
01 thru 99

if I use a character field, will I have any problems with duplication, or sorting complications.

since the data is captured in character format, I could
pad left with zeros and cancatenate.

003 01 05

since there will be many complicated queries, i want to provide for the easiest and most versatile method.

query.filter="substr(sortingfield,1,3) '003' .and. substr(sortingfield,4,2)='03' .and. between(dtoc(sale_date),dtoc(beginningdate),dtoc(endingdate)) .and. substr(zipcode,1,1)

[crimmelcp]

RE: need sorting suggestion

I have always tried to keep the following rule.
Use a numeric field only if I need to Add,Subtract, Multiply, or Divide the numbers.
All other time i try to use character fields.
I the use substr(xxxx) to separate the sorts.
You could have some problems if you allow blanks or Letters.
Mask the field to onle allow Numbers and you should be OK

[martinwcole]

RE: need sorting suggestion

thanks - the values will be drawn form an auto increment field for each catagory - first customer is 1 - then 2, etc.

[Stan Mathews]

RE: need sorting suggestion

Martin,

Only benefit I see to having three fields would be less "wordy" query expressions. Easier to specify field1 = "003" than your substr() example.

Make your manufacturer field character, first value "001" not "1". Auto increment will work the same and you won't have to worry ablout padding.

Stan

[martinwcole]

RE: need sorting suggestion

thanks

[whanigsberg]

RE: need sorting suggestion

Martin,

I too have to sort on a wordy string. If you have to do lots of sorts on substr("your_string", 5,3) you could write a custom function. This has the effect of cleaning up your code and making it easier to read later on.

For example, I have a long field, course_section_no, and I have to get the term (semester) of the course out of it. Unfortunately, there are two different term formats, 3 char and 5 char both of which are substrings of course_section_no.

I wrote two custom functions, term3 and term5. Later, when I read term3(course_section_no) it is much clearer that substr(course_section_no, 2,3).

Bill

[martinwcole]

RE: need sorting suggestion

i've never written a function yet. but now might be a good time to learn.

would be interested to see one of your functions