CRM On Demand Learning Blog

Let’s say your users need to enter an 11-digit number in a field. The number will always be purely numerical (not alphabetical or alphanumeric), but it exceeds the 10-digit limit allowed for fields with the Number field type. What do you do?

You can use a field validation on the text field and use the expression:

Len([<IndexedShortText0>]) = 11 AND (FindOneOf ([<IndexedShortText0>], "!
@#$%^&*_{[]:;<>,.?/=qwertyuiopasdfghjklzxcvbnmQWERTYUIOPASDFGHJKLZXCVBNM") = 0)

So if one non-numeric character is found, the expression will evaluate to false, and the field validation error message will be displayed to the user.

As an example, we’ll set up a field validation on an indexed field for products.

1. Click Admin> Application Customization> Product> Product Field Setup. In this example, we’ll edit Indexed Short Text 1.
   
2. Next to Field Validation, click the fx icon.
   

3. Copy the expression listed above and paste it into the Expression field.
   

   In case you noticed, in our example, [<IndexedShortText0>] is actually the correct field value for Indexed Short Text 1. If you were setting up your own field validation, you’d want to replace the field reference in the expression with the correct one for the field. To do this, you’d use the field drop-down list to insert the correct field value.
   

4. Click Check Syntax and fix any errors. If you get no errors, click Save.
   
5. Back on the Field Edit page, enter a Field Validation Error Message.
   

6. Click Save.

Remember, you have to make the fields available on the page layout for you to test the validation.

For more information on building field valitation expressions, see “Creating Expressions with Expression Builder” in Oracle CRM On Demand’s Online Help.