During last week I was working on a Proof-of-Concept for a customer that involved InfoPath Forms Server and Business Connectivity Services (BCS) technologies: from a technical standpoint the idea was to integrate a process where users read or input data in the form and data flows to and from a SQL Server database with BCS acting as the middleware between the two.

One of the goal for this solution was to display data in the context of the current logged-in user both in SharePoint and offline client technologies like SharePoint Workspace. I’m going to explain you the step-by-step instructions on how to achieve this and for this purpose I’ll focus my attention on the BCS External List (which is actually a key component in our PoC) used to pull the data from the SQL Server database.

1) Let’s begin with the table structure that we want to use for our list: in order to be able to filter data using this method you need to have a field filled in with the value of the AD user that you want the data filtered for (stored in the format domain\username), in this case the “TaskAssignedTo” field. Remember that in order for the field to be filterable you need to map it to an identifier (primary or foreign key identifier)

2) Now it’s the time to define our External Content Type in SharePoint Designer 2010 (I won’t delve into the steps to create the ECT as this is out of scope for this post because I want to keep the focus on the user context functionality). After creating the ECT along with all the necessary operations (Read Item and Read List at the minimum) you should end with your created ECT in SPD

3) Now pay attention to the Read Item operation: if you enter in edit mode (double-click) and then try to add a filter for the input parameters you can see that there is a filter named “User Context” which actually passes the current logged in user

4) Let’s add the filter to this operation by pressing OK. If we do the same then with the Read List operation….

5) Ouch!!! Too bad..we don’t have a User Context filter in the Filter Type dropdown…so what?! Just exit the screen with Cancel and save the ECT to the BCS Metadata Store (just press the Save button on the top left part).

6) Here comes the fun: although there is no designer support for the User Context filter in the Read List operation we can copy the defined filter from the Read Item operation and change the submitted query. Let’s start by exporting the BDC Model from SPD: External Content Types -> Right-click on your ECT -> Export BDC Model -> Keep the “Default” setting, give a name to the exported model and press OK to save the file to your computer

7) In the meantime we can publish the ECT with an External List in SharePoint through the “Create Lists & Form” Ribbon button from the ECT main screen list in SPD

8) Don’t forget to give appropriate permissions to your BCS entity in the BCS Service Application otherwise you’ll get a nice error “Access denied by Business Connectivity Services” while accessing your external list (remember that at least you need one user/group with the “Set Permissions” permission on the entity to avoid creating a non manageable object, the UI will stop you automatically on that)

9) We can now access our External List in SharePoint, as you can see we are displaying all the records from the table (TaskAssignedTo contains all users and is not filtered, I’ve truncated some fields in the image that are not relevant).

10) Let’s come back to our previously exported BDC model, open it through an XML Editor (or Notepad which is my preferred choice for this kind of operations) and locate the Read Item and Read List operations. From the Read Item operation locate the UserContext filter in the FilterDescriptors section, copy the entire block from “<FilterDescriptor Type=”UserContext” …” to “</FilterDescriptor>” and paste it after the Read List operation tag “<FilterDescriptors>” (this tag is not present if you didn’t add filters to the Read List operation, in this case it’s sufficient to add/copy the tags “<FilterDescriptors></FilterDescriptors>” in between the tags “</Properties>” and “<Parameters>”). The end result should look like the image below

11) We can add now the input parameter associated with our User Context filter to be passed along to the select query. Go back to the Read Item operation and locate the input parameter named after your AD filter field in the database table (@YourFieldName), copy the entire block from “<Parameter Direction=”In” Name=”@YourFieldName“>” to “</Parameter>” and paste it into the Read list operation after the “<Parameters>” tag. The end result should look like the image below (you can confirm that this parameter is actually associated to your filter by seeing the match between the the AssociatedFilter property in the parameter TypeDescriptor and your filter name in the FilterDescriptor tag)

12) Finally we can change the select query to account for the new input parameter: locate the property named “RdbCommandText” in the “<Properties>” section of the Read List operation and append the filter at the end of the query (either with “WHERE FieldName = @FieldName” if no filters were previously defined for the operation or with “AND FieldName = @FieldName if the WHERE clause is already defined).

13) Don’t forget to increment the entity version in the Entity tag otherwise it won’t get updated when you upload the model back to the BCS Metadata Store.

14) Save the file and go to the BCS Service Application in the SP Central Administration, press the “Import” button located in the top-left part

15) Locate your .bdcm file previously modified and load it into the file upload control and confirm with Import

16) If everything went good you should see a confirmation message without errors (just warnings about the external system not updated because an external system is already in place and the limit filter warning if you did not define it in the Read List operation)

17) Confirm that the entity version in the BCS Service Application screen (ECT view) is now matching the one you specified in the model file. Repeat step 8 to give permissions on the entity

18) If you go back to the external list you will see that the displayed data is still unfiltered, to refresh the view you need to repeat step 7 in SPD to force a refresh of the External List (refresh the ECT list until you see the correct entity version before attempting this operation), after that you can proceed again to the list view in SharePoint

19) It’s done!! Now you should see only the records that belong to your current user, this solution is not “security through obscurity” but data are filtered directly from the database and you can safely use this method when synchronizing external lists to offline clients (SharePoint Workspace, Outlook)

Important notices:

1) If you change the table structure (and so the query for the Read List operation) you need to repeat these steps to append again the filter parameter at the end of the select query
2) Every time you export and import again the .bdcm model ensure that you save it with a different name otherwise it will give you an error about a duplicate object in the metadata store

I’ll keep this post updated as soon as I’ll discover new important things to make you aware of.

Enjoy and thank you for reading until this point!

Be Sociable, Share!
  1. Rick says:

    I am stuck on the third step, which is to add the Filter to the Read Item. I get the error: “This operation will be saved without filter property for data source element UserID. The data source element must be defined as a field, identifier, or foreign identifier to add this property.”

  2. Jazz says:

    its not allowing me to get through step 15 its giving me error when i try to upload model
    the error is
    Application definition import failed. The following error occurred: Object reference not set to an instance of an object. Error was encountered at or just before Line: ’43′ and Position: ’10′.

    i have check every thing no tag is missing infact when i just modified the model using sharepoint designer and import it just after exporting it in this case its also giving me the same error.

    i dont know what to do please help i am stuck

    • iboscolo says:

      Hello Jazz,

      Sorry for the long absence….did you manage to get through and make it work? Pls let me know.

      Cheers.

  3. YSingh says:

    This solution is working perfectly! Please follow exact steps or try one more time to get it working.

    Thanks for detailed steps.

  4. Rob Koelmans says:

    We can’t get it to work for a FBA-User. Also a write ‘behind the scenes’ for certain parameters would be great. Users would be able to create their own ‘localized’ data. In that case we’re also able to see what a FBA-username would look like.

    MS documentation says it’s ‘member: username’ but the only member syntax we have (in ASP. Net) doesn’t work.

    • iboscolo says:

      Hello Rob,

      In my development environment I’ve setup the thing only with integrated authentication but yours is a good point to test also. Did you manage to get it work and want to share the resolution with the community? Otherwise I’ll resume my environment and perform further tests on it. Pls let me know.

      Cheers.

  5. Cyberbrutus says:

    Excellent article. Exactly what I planned to do.

    I wanted to filter the last 14 days records only and used ‘WHERE {fn now()} – [post_dt] < 15′ and it worked just fine

  6. Dave says:

    Awesome Post!
    Struggled with some steps the first time, but if you perform the steps EXACTLY as described it works perfectly!

    Thank you very much for sharing!

  7. Sherry says:

    Hi,

    I’m trying to correct the display of datetime fields coming from an external list. I exported the BDC model and changed the to for the affected fields. When I try to import the BDC file I get the message “Application definition import failed. The following error occurred: Object reference not set to an instance of an object. Error was encountered at or just before Line: ’93′ and Position: ’10′” This line consists of this : The site is members2.capp.ca, and I updated the version number. ANy ideas????

    • iboscolo says:

      Hello Sherry,

      Did you manage to solve the problem and want to share the resolution? Pls let me know. I have to resume back my environment so I’m grouping together all the issues to refresh the article in case. Thank you.

      Cheers.

  8. Matt G says:

    Something’s horribly wrong for me at step 3. After defining the filter on the UserName field in my ECT, the Input Parameters Configuration dialog on the Read Item operationsgives me this warning: “This operation will be saved without filter property for data source elemnent UserName. The data source element must be defined as a field, identifier, or foreign identifier to add this property.” Is this because I’m deploying to SharePoint Foundation (on my development laptop) rather than to a SharePoint Server?

  9. iboscolo says:

    Hello Matt,

    Did you manage to solve the problem? Did you try to follow all the steps as described? In fact I did try the solution on SP Server but as BCS is available also in SP Foundation this should work for this scenario..did you define your field as identifier? (step 1: “Remember that in order for the field to be filterable you need to map it to an identifier (primary or foreign key identifier)). Pls let me know.

    Cheers.

  10. Evelyn says:

    Thank you very much for that great article! It works perfect for me!

  11. John Ave says:

    Works great ! Thanks for sharing the solution :)

  12. Chris says:

    Thank you! Thank you!

  13. Utpal says:

    When I try to import the BDC model (.bdcm) I get the message “Application definition import failed. The following error occurred: Object reference not set to an instance of an object. Error was encountered at or just before Line: “. I was trying import bdc model from Dev. server to test server. Very frustrated. I will very much appreciate any suggestion to troubleshoot.

    • iboscolo says:

      Hello Utpal,

      Did you follow all the steps properly? Usually this error comes from an unproperly edited bdcm file.

      Cheers,
      Ivan

  14. Christopher says:

    Great article – Thanks for posting this, and in such great detail! I’m up and running!