Adding Sort Field from Existing Data

[swichman]

I have a problem and if anybody could help I would really appreciate
it.
I have a field called subcondwgnumber which has entries such as PL100,
RS2A, BP35A etc.
I want to run some code that on an afterupdate of the subcondwgnumber
takes the users entry and makes it an 8 or 9 digit sort number and
then stores it in a field such as sortnumber such as the entries above
would be PL000100, RS000002A, BP000035A. Please note that not all
entries have a letter at the end so that this creates some
difficulty. I am thinking:
1. Test subcondwgnumber user entry for letter at end
2. If there is, strip it
3. Make standard 8 digit number by adding appropriate number of zeros
at end of initial group of letters
4. Add letter back on
5. Store in field sortnumber
If anybody could help I would be extremely greatful.

 

[mray29]

I'm unclear on the rule you want to use, and if your data is consistent
enough to create a function to do this.
PL100 becomes PL000100 (the first two letters plus the last 3 digits plus
enough zeroes to make 8 digits. The other examples are the first two
characters plus the padding zeroes plus the last ore three characters.

Can we assume that that if the third character is a number, the next two
charaters will be numbers as well? Are there any differnt cases like a PL10A,
etc? I think you are basically correct in your approach as long as the data
is consistent. Although your step 3 doesn't really work for PL100, because
you aren't adding zeroes at the end; you're adding them between the "L and
the "1".

 

[swichman]

I'm unclear on the rule you want to use, and if your data is consistent
enough to create a function to do this.
PL100 becomes PL000100 (the first two letters plus the last 3 digits plus
enough zeroes to make 8 digits. The other examples are the first two
characters plus the padding zeroes plus the last ore three characters.

Can we assume that that if the third character is a number, the next two
charaters will be numbers as well? Are there any differnt cases like a PL10A,
etc? I think you are basically correct in your approach as long as the data
is consistent. Although your step 3 doesn't really work for PL100, because
you aren't adding zeroes at the end; you're adding them between the "L and
the "1".





- Show quoted text -

The only difference would be a letter at the end and either two or
three characters at the beginning. But the beginning does not screw
me up order wise only the extra letter at the end. For instance, if
there are two letters at the beginning PL19, PL200 or PL20A, I want
the order to be PL19, PL20A, PL200. If there are three letters at the
beginning, OBG19, OBG200 or OBG20A, I want the order to be OBG19,
OBG20A, OBG200. So for all six records, the order is OBG19, OBG20A,
OBG200, PL19, PL20A, PL200. By adding zeros with reference to an
eight digit number (after the last letter is stripped, if necessary),
then this can happen. OBG00019, OBG00020A, OBG00200, PL000019,
PL000020A, PL000200. This is the best way I can explain it. Hope
somebody can help.

 

[John W. Vinson]

I have a problem and if anybody could help I would really appreciate
it.
I have a field called subcondwgnumber which has entries such as PL100,
RS2A, BP35A etc.
I want to run some code that on an afterupdate of the subcondwgnumber
takes the users entry and makes it an 8 or 9 digit sort number and
then stores it in a field such as sortnumber such as the entries above
would be PL000100, RS000002A, BP000035A. Please note that not all
entries have a letter at the end so that this creates some
difficulty. I am thinking:
1. Test subcondwgnumber user entry for letter at end
2. If there is, strip it
3. Make standard 8 digit number by adding appropriate number of zeros
at end of initial group of letters
4. Add letter back on
5. Store in field sortnumber
If anybody could help I would be extremely greatful.

I'd take quite a different approach: since subcondwgnumber apparently consists
of three logical attributes (prefix, number, and suffix), either temporarily
or permanently migrate it to three fields, text, number and text respectively.
You can then sort on the three fields, and concatenate them for display
purposes.

John W. Vinson [MVP]