Microsoft Office Tutorials and References
In Depth Information
5. To create the Price field, do the same: click Create Column on the List Settings page, and
set Column Name to Price And Type to Currency. Require the field, and set the minimum
value of 5.00. Otherwise, keep the defaults (including no column validation). Click OK.
At this point, you can see that it’s not hard to add field after field to a list (having to
configure the settings can get tiresome, but that’s what site columns are for). However, the next three
fields for this custom list are a little fancier. The first one is a field with validation; then you’ll do
a Customer lookup field. The lookup field will connect to the Customers contact list so that the
data entry person won’t need to type in a customer’s name if it already exists in the Customers
list. Finally, you’ll add the custom site column.
Adding a Field with Column validation
Column validation lets you configure a simple formula on the field; the value that is entered
during data entry must meet the formula’s criteria, or the list item will fail to be created. At that
point, a conigurable error message will appear beneath the offending field in the new item
form, indicating what kind of value is acceptable and requiring that the error be corrected. In
this example, you’ve chosen to allow no more than 10 of each item per order because of shipping
issues. So if any value over 10 is entered into the Quantity field, it should be red-lagged before
the item is saved to the list so that the user can correct it.
1. To create the Quantity field that you are going to validate, click Create Column, and enter
the following values:
Column Name: Quantity
2. Require the field, and set a minimum value of 1 (so there can be no zero quantities).
Otherwise, keep the rest of the defaults; don’t enforce unique value, keep automatic decimal
places, no default value, don’t display as a percentage, and add the field to the default view.
3. To expand the Column Validation section, click the section title or the plus sign next to it.
In the Column Validation section are two settings, Formula and User Message
Figure 7.39). (
The syntax for entering a formula can be a little picky (there is a link to open Help so you
can look for more information on formulas). Even though you are obviously referring to
this field, you must enter the field name in brackets anyway (SharePoint will strip the
brackets off and add an equal sign after you save; although I added it in the figure below,
you’re not obligated to start the formula with equals). Finally, the actual calculation or
argument itself has its own syntax. In this case, you need to be sure that the value of this
field is less than or equal to 10. Remember, for validation, you are entering the value that
defines the parameters that the data in the field has to fall within, so this calculation must
be true to pass, not be the criteria by which the data fails.
4. So in this case, the formula in the Formula field should be entered as follows: