Number field - Format mask and validation

partlycloudy

APEX 4.2.1

When a page item with source=DB column uses a Number type and a currency format mask (FML999G etc), when the page is rendered, it properly formats the number.

The built-in Item is numeric validation appears to be handle this reasonably ok. i.e. both formatted values and only-digits both validate ok.

But any after-submit validations choke on the non-numeric characters in the field! Please don't tell me I have to a) create shadow items and/or b) replace() out the non-numeric characters in the validation.

After so many years and versions, why doesn't APEX have a simple, declarative, elegant solution to handle such a basic function?

Am I missing something?

Thanks

fac586

VANJ wrote:
APEX 4.2.1

When a page item with source=DB column uses a Number type and a currency format mask (FML999G etc), when the page is rendered, it properly formats the number.

The built-in Item is numeric validation appears to be handle this reasonably ok. i.e. both formatted values and only-digits both validate ok.

But any after-submit validations choke on the non-numeric characters in the field! Please don't tell me I have to a) create shadow items and/or b) replace() out the non-numeric characters in the validation.

After so many years and versions, why doesn't APEX have a simple, declarative, elegant solution to handle such a basic function?

After so many years and questions, why haven't you posted full details of what these after-submit validations are supposed to do, the code involved, how they "choke"—error messages, debug trace etc—and sample data. Or of course the ubiquitous example on apex.oracle.com.

Us old-timers should be setting a good example here.

Howard (... in Training)

Superficially at least, this sound very similar to the "date" problem discussed here: {thread:id=2368328}. In both cases, the data is displayed correctly but there seem to be data operation/validation problems caused by the formatting.

I'm hoping to have time to look into this.

VANJ: There must be thousands and thousands of instances where this works. Rather than gripe about APEX, let me look at it from another perspective.

Why / how is it so easy for me to get it wrong?  
What don't I know about APEX -- that if I knew it -- 
would have allowed me to avoid this error?

???

Kind regards,
Howard

partlycloudy

Sigh, ok my bad. I should have known better. I will take the time to put up some examples on apex.oracle.com to demonstrate why I was frustrated.

Howard (... in Training)

Sorry. I really didn't intend to be or sound critical. That's why I phrased this as "me"!

Why / how is it so easy for me to get it wrong?  
What don't I know about APEX -- that if I knew it -- 
would have allowed me to avoid this error?

partlycloudy

Howard - No apology necessary, I understand.

OK here is a very, very simple example https://apex.oracle.com/pls/apex/f?p=57688:6

Item is defined as follows
1. Display as = Number
2. Required = Yes
3. Format mask = FML999G999G999G999G990D00
4. Source (Only../Static...) = 1234567.89
5. Page level validation that does some checking based on the value; EXISTS

select null from dual where :P6_X < 10000

a. When page first renders, the field gets displayed without the formatting i.e. 1234567.89 instead of $1,234,567.89. Is this because it is not a DB column field? Not very clear. Under some circumstances, I have seen it initially rendered with the currency formatting.
b. Entering plain digits without any formatting passes the validation
c. Entering it as per format mask (e.g. $1,000.00) throws a Invalid Number error on the validation.
d. If there is a Fire on Page Load=True DA that uses the value of the item decode(:P6_X,100,'foo','bar') to do some stuff, that throws the same Invalid Number error

What am I missing? I have a simple DML form with some money amounts and I use the out-of-the-box Number item type and Format Mask to tell APEX how I want it to be displayed but I expect all the programatic components to take the plain numeric value of the item with no further effort (i.e. translates and shadow items, et. al) on my part.

Thanks

Howard (... in Training)

Well, now that I consider it, I don't know what the semantics of a formatted field are! I suppose you type in a decimal but I would NOT expect anyone to type dolllar signs and commas. My expectation is that the process would add that formatting. So, if the user enters the data, I would not expect to see the formatting unless a DA fired to redisplay the field with it. Or it was a retreived record. And I would expect the DB operations to save the native data type (numeric, date) without formatting.

The "problem" with that is that if I'm adding formatting to character data, then I would likely want those characters saved in the DB. So, I'm inconsistent. ???

And if it were a numeric (or date) validation, I'd expect it to be done on the unformatted value. Why? Because it would work that way! Ah. Maybe we need two kinds of formatting. "Masks" should only exist in the HTML and should not perturb the underlying data. Validations and data operations would operate on the intrinsic value. Other "formatting" operations -- not sure what to call them -- would actually modify the underlying data.

Howard

partlycloudy

Howard - Yup, that's exactly the way it should work. To phrase it in APEX terminology, for items defined as Number with a format mask, the APEX rendering engine should show the formatted value in the page item regardless of the Source field (i.e. for both DB and non-DB columns). If the page has some further client-side scripting or APEX dynamic actions to re-format the value after user tabs out of the field, so be it. And finally, the APEX processing engine should use the "raw" value of the item in validations and processes. Stated differently, any item reference using the APEX-style :P_X bind variable notation should, automagically, discard all the formatting and use the numeric value of the item. Moreover, numeric format masks are used primarily as a visual aid for large numbers and no one enter the currency and group separator characters when entering data but if a accountant-type user happens to do that (or just changes one digit in a already formatted number), so be it, the APEX engine shouldn't choke on it (as in, Invalid Number) during processing.

These semantics are pretty basic where numbers representing currency amounts are concerned in a data-entry framework like APEX so when I saw that 4.x has introduced a declarative item type called Number with Settings like Format Mask & Minimum/Maximum, I thought things would work just like we are describing. Can we write REPLACE/TRANSLATE functions to handle this? Sure. But we shouldn't have IMHO.

Am I missing something? Thanks

Scott Wesley

As described in the item help
"Select or enter a format mask. Note that format mask is applicable only to items of source type Database Column."

In my experience, and this goes for the great web experience, not just the application you're designing - I've found the most data entry fields avoid formatting.
Relevant information is often shown item labels.

Any occasional the business has specifically asked for formatting, I've taken the same approach as I've needed to for years - before web development (and every time you use the to_date/to_char functions for date conversions) - validate the information as per expectation. If you think the user will be adding punctuation to a numeric field, then you must validate accordingly.
This usually means adding TO_CHAR(value_entered, expected_format_mask) to your validation control, instead of presuming APEX will read the user's mind and know the content of that numeric field may not be numeric.

Date entry does the same thing - ultimately there is a session date format that is implicitly used by Oracle to convert an entry of 12-10-2012 to the appropriate date -- ie, is it October or December?

Have a think about the various websites you visit personally - plane bookings, concerts, ebay, etc - how many expect entry to be purely numeric? My recollection is most, except maybe some credit card fields, but most use some sort of regular expression to sort that out. And you can tell the validation is coded respectively.

Generally, I reserve this sort of primping to reporting - even then it's made to be subtle - just like I'd prefer makeup to be ;-)

Most users just care about the data, not how it looks.

Scott
blog: [url grassroots-oracle.com]grassroots-oracle.com
twitter: [url twitter.com/swesley_perth]@swesley_perth

-- please mark any useful posts as helpful or correct, in the end it helps us all

Howard (... in Training)

VANJ,

Actually, how did you get what you described in the initial block of the thread. When I go back to examples I have created, I see a format mask but only for reports (where the columns are display, of course). For record processing fields that I have created using the wizzard, I don't see a formatting option.

If you go here http://apex.oracle.com/pls/apex/f?p=21997:3 Dever \ Ima9Dever
there are two regions on the page. I only see the formatting option for the lower report region.

Regards,
Howard

partlycloudy

Item Help - "Select or enter a format mask. Note that format mask is applicable only to items of source type Database Column."

This help text appears in the field Source > Format Mask. I am referring to the Settings > Format Mask field that shows up when Item Display As is set to Number

I've found the most data entry fields avoid formatting.

I disagree. When rendering data-entry forms that contain large amounts (e..g millions), the formatting is essential to avoid mistakes.

See https://apex.oracle.com/pls/apex/f?p=57688:6

The SAL item is set to Type=Number, Format Mask = FML99.., Min = 0, Max = 50000


1. When I enter just digits without any formatting, it is accepted as a number. Good.
2. When I enter just comma but no $, there is a format error. I suppose this is reasonable. But there is no way to change the validation message that shows up Sal does not match number format FML999G999G999G999G990D00 (Example: $1,251.49). Is there?
3. There is a on-change DA on the Sal field that attempts to use the numeric value of the column in some computations. For the purpose of his example, it simply set the value of the yellow display item to

select 'Salary is '||:P6_SAL from dual

Clearly, this presents a challenge because if the SAL field contains formatting (note that the built-in validation in #1 above does accept this as a valid number!), the DA will raise a Invalid Number error. So the DA code has to replace/translate/to_number out the non-numeric characters. Show stoper? Not at all. Annoying? Yes!

Am I missing something?

Thanks