(Note: The information below is applicable to Access 2010 web databases. It may not be correct Access 2013 web apps. See the following link for further information: http://rogersaccessblog.blogspot.com/2013/12/access-web-databases-2010-vs-2013.html)
As I mentioned in a previous post (Access 2010 Web Databases: Uploading the Data to SharePoint), in an Access client database, I normally create a Compound Unique Index (that is, a unique index on multiple fields) to insure real world uniqueness for a record. Unfortunately, SharePoint does not support compound indexes, so I'll have to simulate it.
Composite Unique IndexesThere are two ways to simulate the effects of a unique index on multiple fields. Both involve the use of Data Macros, which are similar to triggers in other database systems. Data macros are automation that takes place at the table level.
The first method is used when all of the fields in the "index" are required. You Look Up A Record (similar to a DLookUp) and use a with a multiple Where Condition to test whether those values have already been used. In my database project, the linking table (lnkBooksAuthors) has a unique index on the combination of the two Foreign Keys: AuthorID and BookID. Usually, I will use those two fields as a Primary Key. However, as I showed before, SharePoint does not allow multiple field primary keys. So I created an Autonumber primary key, and now I have to simulate a unique index on those fields.
In the BeforeChange event, use the following (click to enlarge):
The full Where Condition (above), looks like this in the Expression Builder:
The second method is used when all of the fields in the "index" are NOT required. For instance, in my tblAuthors table, I want to create a unique index on AuthorFirstName, AuthorMiddleName, and AuthorLastName to make sure I don't get duplicate records. But there's a problem. Authors often don't have middle names, so the AuthorMiddleName field must allow NULL values. But a NULL in any of the values will prevent the expression to valuating to TRUE, so duplicate records could be saved.
To get around this, simulate the unique compound index with a Data Macro on a Calculated Column. In this case, one that looks like "AuthorName" (below):
Ordinarily, I would advise strongly against using them because they violate the Rules of Normalization. But because SharePoint is not a relational database, you will often have to do some things I'd ordinarily frown upon.
Therefore, your data macro would look like this instead:
Using a calculated column has 3 advantages:
- the Where condition in the macro is shorter and easier to read,
- it handles the NULL value problem, and
- this column can be used throughout your application where you need the full name in "LastName, FirstName MiddleName" format.