Database Relationships, Unique Columns and validation rules make it easy to validate your data. This creates much more maintainable applications and helps users to do the right thing the first time.
Some of the feedback we received from blog readers and developers for Access/SharePoint integration was to have better data platform features to ensure the data integrity they expect from a relational database.
In SharePoint 2007 validation such as required fields and min/max values are only enforced through the browser user interface. This means that applications talking to SharePoint via the SOAP Web services or the Object Model will be able to bypass those rules.
The data platform improvements we included in SharePoint 2010 are enforced at the at the data layer to make sure that no matter how an application/user interacts with the backend lists (Browser, Office Client,
Discount Office 2007, Web Service,
Windows 7, Object Model), the rules will be enforced.
List Relationships
In SharePoint you can create lookups between lists, in 2010 lookups can enforce cascade delete and restrict delete relationships between the two lists.
In Access you can use the lookup wizard to set up a relationship.
You can use this to create simple 1:Many or Many:Many relationships between lists that are enforced at the data layer.
TIP: Use the lookup wizard to create SharePoint supported relationships in Access 2010. If you are working against a published web database—you will need to sync the table after creation before you can create lookups
TIP: Web Databases do not support relationship view. So if you want to see a view of all your relationships use the database documenter or create a dummy query with all the tables.
Unique Column Constraints
You can use unique constraints to make sure that duplicate values are not entered in specific column. Multiple unique constraints can be defined on a table and they allow for NULL values.
You can use these to enforce rules such as making sure an order number is unique across all orders. These constraints are enforced at the data layer.
The property is set through the Table Tools | Fields | Field Validation | Unique check box in the ribbon.
Tip: Unique columns are not supported for attachments, table based lookups (foreign keys), multi-value lookups,
Microsoft Office 2007 Professional, memo, hyperlink and Boolean fields.
Data Validation
As in Access,
Microsoft Office Pro 2010, validation can be enforced at the table/list level and at the field/column level. You can supply an expression that must be true whenever you enter or change data. This allows a developer to validate data before it goes into the database. If the condition is not true the developer can provide a customer error message. The properties are set through the Table Tools | Fields | Field Validation | Validation dropdown in the ribbon.
When you are in SharePoint you use Excel expressions to create your rules but in Access you continue to use the familiar Access expressions and we handle the translation back and forth from the server.
In the browser we remote simple expressions to JavaScript to notify the user of any issues before they try to commit the record. This creates a more responsive user experience and reduces calls to the server.
This is our Country field in the browser
If I enter invalid data and tab off the field is automatically highlighted
When I place my focus back into the field the validation message is displayed so that I know how to correct my issue.
More advanced expressions like the sample below get evaluated at save and produce an error prompt.
Tip: You can use NOT, <>, <, >, <=, >=, IN, BETWEEN, LIKE (?, *,
Office Professional Plus 2007 Key, # as wildcard characters) and IS NOT NULL as comparisons on your expressions. Table and field validation rules are enforced at the data layer.
One of our testers and author of Access 2007 Inside Out, Jeff Conrad, sent me this great sample;
In the EmailAddress field, we want to be sure the e-mail address provided by the user appears to be a valid e-mail address. We can verify the e-mail address meets most standards of valid syntax by using a combination of the LIKE operator and wildcard characters in a field validation rule. In the blank text box at the top of the Expression Builder dialog box, type Is Null OR ((Like "*?@?*.?*") AND (Not Like "*[ ,;]*")) for the field validation rule.
This field validation rule ensures that every e-mail address provided by the user starts with at least one character followed by the @ symbol, contains at least one more character following the @ symbol, and contains the dot symbol followed by at least one more character after the dot symbol.
Also, this field validation rule does not allow a space, a comma, or a semi-colon anywhere in the email address.”
Enforcing SharePoint validation at the data layer
SharePoint also has a couple more validation options for required fields and min/max values for numbers. For Access Services lists we enforce those at the data layer as well.
Tip: There is a list OM property called SPList.EnforceDataValidation that gets or sets a Boolean value specifying whether required fields and min/max values are enforced at the data layer. You can set this on any SharePoint 2010 list to get this behavior.
More Reading
List Relationships and Unique Columns from the SharePoint perspective(
More info on data validation in general for Access from 2007 (
<div
Microsoft Office Pro 2010 Data Platform Improvemen
the youngest
Linear Mode
