Alpha and Numeric Question

[bv0719]

Trying to resolve a formula error with a field that include both Alpha and Numeric data. There are some system generated text that I can account for but there's also user entered text that can be just about anything. I need to convert the field to a number field using the tonumber command which works until I run into an Alpha character. Below are the formulas I'm using to determine the data types in the report. The first one @ILGDNO is the problem one. The second line fails because of the multitude of options that users enter in the field when entering data in the system. Bit of a nightmare for a reporting perspective. Basically what I want is if the {itmldg.ilgdno} field is any alpha character then 1 else tonumber {itmldg.ilgdno}.

// PI3030 equals and inventory transacation.
// If not inventory then is it alpha or numeric
@ILGDNO
IF {itmldg.ilgdno}= "PI3030" THEN 0 ELSE
IF {itmldg.ilgdno}<> "PI3030" THEN TONUMBER ({itmldg.ilgdno})

@WHATISIT
//[00001 TO 50000] equals stock transfer order
//[50001 TO 99999] equals purchases
//[100000 TO 500000] equals sales
//[500001 TO 999999] equals production

IF {@ILGDNO} IN [00001 TO 50000] THEN 1 ELSE
IF {@ILGDNO} IN [50001 TO 99999] THEN 2 ELSE
IF {@ILGDNO} IN [100000 TO 500000] THEN 3 ELSE
IF {@ILGDNO} IN [500001 TO 999999] THEN 4 ELSE 0

@WHAT_ANSWER
IF {@WHATISIT}=1 THEN "STOCK TRANSFER" ELSE
IF {@WHATISIT}=2 THEN "PURCHASE" ELSE
IF {@WHATISIT}=3 THEN "SALES" ELSE
IF {@WHATISIT}=4 THEN "PRODUCTION"

@RECEIPT
// Is the transaction a receipt?
IF {@WHATISIT} <> [1 TO 4] AND
{ITMLDG.ILGRCN} >1 THEN 5

 

[kray4660]

I do not see a isnumeric check, at least that will determine if there is a non-numeric character in the data. Then the question is how to you want to do when there is a non-numeric in the data.

 

[bv0719]

How to you incorporate and 'isnumeric' check into the formula? Or is this a separate formula?

If it's not numeric I want to apply a number value to it for reporting/selection purposes.

--- Made some progress using the formula below.

IF {itmldg.ilgdno}= "PI3030" THEN 0 ELSE
IF {@ISNUMERIC} = TRUE THEN TONUMBER ({itmldg.ilgdno})

 

[kray4660]

So for the IF {@ISNUMERIC} = TRUE THEN TONUMBER ({itmldg.ilgdno}) ELSE "What do you want to do here". It appears the data is preceded by a letter (i.e, PI3030). If you just want the numeric part then tonumber(mid({itmldg.ilgdno},2)). If it multiple letters or letters embedded into the number, then that gets a bit more difficult.