Thursday, August 5, 2010

Filtering a Data View Web Part to Current User

When you begin using data view web parts (aka data form web parts) to display information on a sharepoint webpage, you will find that filtering is rather easy. After you insert a data view, you should see a small '<' symbol (MSFT calls it a chevron) off to the right of your new data view webpart. If you click it, you can see that the first thing they let you click is to Filter. When you do, you can choose the first dropdown to be any column; but to filter it to the current user, you need to pick a Person/Group column that you have created. The middle dropdown should be "Equals". The third dropdown, you should be able to scroll to the VERY bottom and find something called "[Current User]". This will filter the webpart to only show you items where your username is in that person/group column. Mine looks like this:

Pretty simple, huh? Well, there's one small kicker to this: this works right IF you are only using a single list. If you follow the instructions of Laura Rogers or other SharePoint gurus and create a linked data source (joining the information from 2 different lists with a common column), then this filter will not work. It will actually change the place in the "code" where the filtering happens and it just won't work. The way to fix this is to open up the page in Split or Code view and you'll have to copy-paste something from here into a specific part (don't worry, I'll show you where). The piece you need to copy to your clipboard is this:

"&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;Eq&gt;&lt;FieldRef Name="YOURCOLUMNNAME"/&gt;&lt;Value Type="Integer"&gt;&lt;UserID/&gt;&lt;/Value&gt;&lt;/Eq&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;"

What this really looks like is this:
"<View>
  <Query>
   <Where>
    <Eq>
     <FieldRef Name="YOURCOLUMNNAME"/>
      <Value Type="Integer">
       <UserID/>
      </Value>
    </Eq>
   </Where>
  </Query>
 </View>"
This is the dreaded CAML - a markup language used to get information from sharepoint lists like SQL will get information from SQL tables. What it means is that it needs to check the Field "YOURCOLUMNNAME" (which you will need to replace with the name of your person/group column that you want to filter by) and use the "UserID" integer (aka your ID number from sharepoint) to only get the ones where the ID number in that column matches yours. Where you will need to place this code, look toward the top of the code for your data view webpart and look for two things: 1) the words "SharePoint:AggregateDataSource" and several "<asp:Parameter"s. 2) just below the Aggregate data source and some of the parameters, you should see "SharePoint:SPDataSource" and toward the right you will find the phrase SelectCommand = "&lt;View&gt;&lt;/View&gt;" or something super close to that. Replace everything in quotes for the SelectCommand with the stuff from above. Be sure to rename the YOURCOLUMNNAME with the actual name of your column. This will filter that data source automagically for you. Hope that helps and doesn't increase brain fog.

4 comments:

  1. when I copy pasted it into the correct spot it didnt work. It's like there is a syntax error right after my column name -- because the quote after it lights up. Is there a syntax error by chance? Penn

    ReplyDelete
    Replies
    1. Make sure that there is one set of quotes surrounding the whole statement and not two. Also make sure there are quotes around your field name. If it freaks out, try using single quotes to go on the outside of the whole thing and leave the double quotes surrounding your field name alone or vice versa

      Delete
    2. You'll need to use:

      "

      instead of quotes " used in example.

      Delete
  2. Hi Brendan, thanks for the post! Tried using this approach to filter by Created By field (Author). It is throwing an error...
    Any help you can provide is greatlyy appreciated!
    Thanks!

    ReplyDelete