How to validate that imported text contains two decimal places?

[K1200]

I receive a line of text via an import that is supposed to contain a dollar amount. The agreed "convention" is that it will either be a whole number amount (like 46 or 5600) or will contain two decimal places (e.g., 46.20 or 5600.88). Any other value, like 46.2 or 46.875, is not acceptable and must be flagged as an import error.

Short of matching the parsed fractional amount against a value list of the 100 possible 2-digit decimals, is there a simple® way to validate this field?

Thanks in advance for any help.

[jamesducker]

Yes. Use the code below in a calculation field (or a script step) to return your dollar value if importField is valid, or an empty result if importField is not valid.

if (

// check field contains only numbers, decimal points and minus signs

filter( importField ; "0123456789.-" ) = importField

and

// check there is exactly one decimal point

patterncount ( importField ; "." ) = 1

and

// check there is no more than one minus sign

patterncount ( importField ; "-" )

and

// check that the point is in third from the end

left ( right ( importField ; 3 ) , 1 ) = "."

, Getasnumber( importfield ) , "" )

Hope that helps.

James

[jamesducker]

Thinking about it, you should also check that if there is a minus sign, it comes at the beginning, not in the middle or at the end...

patterncount ( importfield , "-" ) = patterncount ( left(importfield,1) , "-" )

James

[Lee Smith]

Take a look at this Thread Link that has a calculation by DJ, and comment. It can be modified in many ways to take the raw number and adjust it to your needs.

Lee

Edited September 19, 2009 by Guest

[K1200]

James, thank you.

That does help me get on a good track -- especially the point about being on the lookout for dual decimal points or minus signs.

[comment]

I believe all these tests could be rolled into =

Exact ( Int ( text ) ; text )

or

Exact ( Int ( text ) & SerialIncrement ( ".00" ; 100 * Mod ( Abs ( text ) ; 1 ) ) ; text )

Not sure what's the purpose here, though. 46.2 or 46.875 are perfectly valid numeric values.

[K1200]

... is supposed to contain a dollar amount.

Just to clarify, the problem is that 46.2 and 46.875 represent key entry errors.

[comment]

OK, but if someone can enter "12.3" or "12.345" instead of "12.34", then they can just as easily enter "12.43" or "1234" - so this method is neither here nor there.

[Lee Smith]

What is the next step?

How do you know what the correct number should be?

Lee

[K1200]

How do you know what the correct number should be?

Not to be trite, but it's not a problem of knowing what the number should be. Rather, the problem is to flag numbers that are obviously not dollar amounts. Yes, a transposition will be accepted, but an extra or missing digit in the fractional part won't be.

From the earlier suggestions, I think I'll be able to catch most of the entry problems most of the time.

Thanks for the assistance on this.