Composite Key style constraints in SharePoint 2010 using InfoPath

Slalom Consultant Bryan Tharpe

Bryan Tharpe is a Dallas-based Slalom Consultant whose expertise includes Microsoft technologies such as SharePoint 2010, C#, ASP.Net, and Silverlight.

A coworker recently sent out an email asking if anybody had any ideas about enforcing a composite key relationship in SharePoint 2010 without writing code. On our current respective projects, we are both tasked with providing solutions that show off SharePoint’s no-code capabilities. Because of this, I have found myself using InfoPath more and more. When I saw this email, I thought “I bet InfoPath can do this!”

To better explain the composite key relationship I am looking for, I would like the ability for SharePoint to enforce uniqueness on a combination of two fields. These two fields,  when used together, are able to uniquely identify any item in this list. Unfortunately, SharePoint doesn’t support this out of the box.

So with that, off I went to InfoPath to put a sample together. It turned out to be even easier than expected. Albeit, a bit more contrived than I would like it to be (supported from SharePoint’s list settings page.)

The sample I put together was a simple reservation system. Let’s assume that for obvious reasons, the business want’s to restrict reservations such that a product can only be reserved once a day. One list contains information related to products. Another list contains reservation information, including a product and a reservation date.

Here are the two lists I set up:

Products

Rentals

For the rentals list, I created a lookup column to bring in information from the products list. Now, with this set up, we’ll need to enforce the composite key relationship on the Rentals list. From the list ribbon for Rentals, you can click to customize the form using InfoPath.

Inside InfoPath, we’ll first need to set up a secondary data connection to point back to the entire list so that we can filter it based on the data entered to determine if it’s a unique combination.

To create a secondary connection, from the Data tab, in the Get External Data section click “From SharePoint List”

The site should be automatically populated and you can click next to choose the list to validate against. Choose Rentals. From here we’ll select the columns that we need so that we don’t bring in columns with data that we won’t be using.

Now that we have the secondary data connection set up, we’ll add our validation. For the sake of demonstration, we’ll add this column to the reservation date field. With the Reservation Date control selected, click the Manage Rules button. We’ll create a new Validation rule called “Not Already Reserved”. We can add any simple message to inform the user that this day was already taken.

With this set up, we can configure the condition to validate against. Clicking on the link for Condition will set us up to enter a sea of dialog boxes, and eventually we’ll have a Composite key constraint!

After clicking on the condition link, we’ll want to add the expression below to fail validation if it finds any items in the list that match the key you give it.

count(xdXDocument:GetDOM(“Rentals”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Product[. = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Product and ../d:Reservation_x0020_Date = xdXDocument:get-DOM()/dfs:myFields/dfs:dataFields/my:SharePointListItem_RW/my:Reservation_x0020_Date]) > 0

Obviously I wouldn’t want to type this out off the top of my head though, so we can take a round a bout way to arriving at this expression.

With the condition dialog open, leave the first two dropdowns and click “Use a formula” for the third.

The first two are not important so long as we get to the use a formula selection dialog. This is where we can put in information that will write our expression for us. With this dialog open, select the option to “Select a field or group”, then inside this select to Filter Data and click to add a filter.

This is the important part. To enforce our constraint, both Reservation Date and Product need to be unique. With that said, we can choose reservation date for the first condition, and then choose to select a field or group.

Now that we have this open we can take pause and stare in awe at the number of dialog boxes we currently have open. It’s amazing.

For the right hand side of the filter condition, we’ll need to click to show advanced view on the “Select a field or group dialog” and change the data source to “Rentals”. Here we’ll select reservation date as well. This should complete the first condition and we can now add the second in the same way, but adding it for product. When completed the filter should look as follows:

We can now click ok out of the dialogs back to the “Insert Formula” dialog. Here we can click the edit XPath option to show us the XPath we’ll use to filter.

We can copy this XPath out of there and wrap it in a count expression to do the evaluation we are looking for.

The result should be Count(**XPath Expression**) > 0

With this text we can change the original condition to an expression and copy in our XPath count condition and click ok.

This should be it and we can publish the form back out to SharePoint. You should now have a composite key set up on this list, without writing a single line of code!

About bryan tharpe
I'm a consultant at Slalom Consulting with experience in Microsoft technologies including SharePoint 2010, C#, ASP.Net, and minor dabblings in Silverlight.

9 Responses to Composite Key style constraints in SharePoint 2010 using InfoPath

  1. zzpiff says:

    It’s a sweet solution to an annoying problem and it would be awesome if I could only get it to work:(
    I’ve followed your “tutorial” and my version looks the same(the rule formula) but the result of the count appears to always be 0… Any clues as to what went wrong would be highly appreciated:)

    • bryan tharpe says:

      Can you post your expression, other than that have you checked to be sure the secondary data connection is bringing back data with a less specific (or no) filter applied?

      • zzpiff says:

        Hi, thank you for responding:)

        My expression is:
        count(../my:ID[../my:Reservation_x0020_Date = xdXDocument:GetDOM(“Rentals”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Reservation_x0020_Date and ../my:Product = xdXDocument:GetDOM(“Rentals”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Product]) > 0

        If I do a count(ID) on my secondary data connection I get the number of records so I assume I get all the data. But I’m a novice to infopath and xPath expressions so I’m really fumbling in the dark here…
        Is there a way to query the data connection other than setting values to fields?

  2. bryan tharpe says:

    Hi, Sure always glad to help! I spent some time looking back over this since it had been a couple months since I wrote this post. I’m going to update this post to use a slightly different expression, but i wanted to get this to you so you so you would hopefully have a solution.

    First, ensure that the IsValid field in your main dataset has a default value of False.

    Instead of the count expression, set up the following Validation type rule. For the rental date field, set the left side of the expression to IsValid. The operator should be “is less than or equal to.” Now, for the right side of the condition, click Edit XPath and put in the following expression:

    ../my:IsValid[../my:RentalDate = xdXDocument:GetDOM(“Rentals”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:RentalDate and ../my:ProductLookup = xdXDocument:GetDOM(“Rentals”)/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:ProductLookup]

    When you uncheck edit xpath it should just say IsVald. The end expression should look like “IsValid is less than or equal to IsValid”

    Hopefully this gets you set up! I’ll get the blog post updated with this expression here pretty soon.

    Bryan

  3. zzpiff says:

    Hi Bryan,

    I think maybe I should wait until you get the blog post updated because I’m not getting it.
    The field IsValid… Is that a yes/no column I should add to the list? Anyway, I tried it that way, added a column which defaults to false when cleared(?) and built the expression according to your instructions but no… No constraint.

    If this will work and I assume it will when I finally get it right. Will I be able to make it work on 3 columns?

    /Johan

  4. Rohit says:

    Bryan – I tried creating a composite unique constraint on 3 columns ( Vendor, Month-Year, and and it’s not working as I expected it to be. Here’s what’s happening –

    Following records got created successfully.

    Vendor Month-Year Item_Key Item_Value
    A Apr-12 X Value_X_A
    A Apr-12 Y Value_Y_A
    A Apr-12 Z Value_Z_A
    B Apr-12 X Value_X_B

    However, when I try creating following records, I get the unique key constraint error !?!?

    Vendor Month-Year Item_Key Item_Value
    B Apr-12 Y Value_Y_B
    B Apr-12 Z Value_Z_B

    My conclusion – It’s not looking for uniqueness at the record level. It’s looking if *any* existing records in the list have the same data values. If it finds a match, the rule doesn’t allow the new record to be created.

    Any suggested workarounds?

    Thanks,
    Rohit

  5. Rajkumar V says:

    It is not working in the edit mode, because it checks for the duplicate again

  6. Sara says:

    Hi Bryan,
    Would you know the best way to check against a field with comma delimited values to use as a filter?
    for example table has a.b.c.d.e and i can only use b and e and they reside in a text field with b,e.
    Thanks!
    Sara

  7. Abhijith R Shastry says:

    Hi Brian,

    After following the steps of your post, I’m getting the same error as Rohit, above. I’ve the following fields in the Rentals list:

    A 9/4/2013
    B 9/5/2013

    Now, when I try to add an item:
    A 9/5/2013, I’m getting the validation message.

    This should not be the case if we want to apply Composite-Unique-Key constraints on a SP-List.

    Any suggestions/workarounds?

    Thanks
    Abhijith

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: