Number formatting question

[Michael Lepore]

Hi All,

I need help with a ticket number problem I am having. I want to have our CSRs put in just a number and have it have a letter in it. An example would be "H2345". I would like to have them enter "82345" and have FM convert the "8" or any other number that is there, to a corresponding letter of the alphabet. Is there a way to do this or should I start thinking of another way to do this.

Thanks in advance

Michael

[Lee Smith]

See if this does what you want.

Substitute ( YourField ;

["A"; 1];

["B"; 2];

["C"; 3];

["D"; 4];

["E"; 5];

["F"; 6];

["G"; 7];

["H"; 8];

["I"; 9];

["J"; 10];

["K"; 11];

["L"; 12];

["M"; 13];

["N"; 14];

["O"; 15];

["P"; 16];

["Q"; 17];

["R"; 18];

["S"; 19];

["T"; 20];

["U"; 21];

["V"; 22];

["W"; 23];

["X"; 24];

["Y"; 25];

["Z"; 26]

)

HTH

Lee

[LaRetta]

I saw it as a bit of the reverse, wanting to change the beginning number (single digit) to an alpha letter. Something more like this?

[color:blue]Choose ( Left ( theNumber ; 1 ) ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "I" ; "J" ) & Right ( theNumber ; Length ( theNumber ) - 1 )

If the number starts with 0, it will produce the A etc. If you are sure you'll never need to replace greater than 9 then the above would work (if I understand you correctly); otherwise you'll need to go into more detail on the rules.

UPDATE: Oh, and the field should be text and not number and should be established as an Auto-Enter with'Do Not Replace Existing Value' UNCHECKED.

LaRetta

Edited August 17, 2007 by Guest
Added update

[Lee Smith]

Hi LaRetta,

Good Catch,

I miss read the question.

Lee

[David Jondreau]

sbg2 had a nifty idea in a another thread .

I've used that idea here:

NewCRS =

Let([

LetterConvert = Middle ("ABCDEFGHI" ; Left(OldCRS;1) ;1 );

RestOfNum = Right(OldCRS; Length(OldCRS) - 1 )];

LetterConvert & RestOfNum

)

[Lee Smith]

If the number starts with 0, it will produce the A etc.

Interesting point. With LaRetta's calculation, the leading 0 is A, and with David's calculation, the 0 is ignored.

Lee

[LaRetta]

Thanks for noticing that, Lee! Both calcs can be adjusted either direction depending upon the need but Michael should be aware of it for sure.

Nicely done, David! I like that better than Choose() here and it's certainly easier to construct as well!

Let us know if you need help adjusting either calc, Michael.

UPDATE: And thanks, sbg2, for bringing that to David's attention and now mine!

LaRetta

Edited August 17, 2007 by Guest
Added Update

[Michael Lepore]

Thank to you all... I will see how this works

Michael