Dynamic Filtering Based on List/Library Column

There might be a situation where you, as a site designer, have to create several views of a list or document library to show it’s contents filtered based on a column (eg: category) in it. Instead of creating so many views manually, you can use SharePoint Designer to create a Data View Web Part (DVWP) which dynamically filters the content based on the value of a column. The detailed procedure to created it is as follows.

Prerequisites:

  1. SharePoint site in which you are a design/owner.
  2. SharePoint Designer 2010 (installed in your workstation).
  3. Working knowledge in SharePoint Designer 2010.

Procedure:

The procedure includes two parts. First part is to create a page with a data view web part using SharePoint Designer to accept the column value as query string and filters the list based on that value. Second part is to create custom navigation links (in left navigation pane of the SharePoint site) to the same page containing the data view web part just by changing the query string value.

Part 1: Creating a page with Data View Web Part.

  1. Create a new page in the SharePoint site and save it (eg: DynamicView.aspx).
  2. Open SharePoint Designer (SPD) in your local workstation and open your site.
  3. Select ‘Site Pages’ (or the page library where you created the new page) on the left navigation pane and select the page you just created.
    001_PageLibrary
  4. Click on ‘Edit File > Edit File in Normal Mode’ in the ribon
    010_EditFileMenu
  5. After opening the page in design view (there is a tab on the bottom of the page to switch through Design, Code and Split views), place cursor within the main content placeholder and place a Data View Web Part (DVWP). In order to do this, expand Data View button in the ribbon under Insert tab and select the list/library you created in step 1.
    020_InsertDVWP
  6. Now, make sure that the newly placed data view web part is selected and click on Parameters button in the ribbon under Options tab.
  7. In the popup window, click on ‘New Parameter’ button to create a new parameter.
  8. Give a parameter name (eg: parDocCategory), select Query String as Source and give a name for Query String Variable. Click OK.
    030_NewParameter
  9. Again, make sure that the data view web part is selected and click on Filter button in the ribbon.
  10. Choose the list column you want to filter on (eg: DocCategory) as ‘Filter Name’ , Equals as Comparison and the parameter you created (eg: parDocCategory) as Value and click OK.
    040_NewFilter
  11. You are done creating a data view web part which is capable of filtering the list/library content based on the value passed to the page as query string. Part 1 is complete.

Part 2: Creating Links to Dynamic View Page.

In the example document library that this article refers to, I’ve created a Choice column called ‘Doc Category’ to filter the documents on. The values are Policies, Requests and Procedures. So I’ll have to create three navigation links on my SharePoint site to navigate to the same page I created in Part 1 but with different query string values (query String is a part of URL in the form "?QueryStringName=Value" used to pass data/values into a web page). For example, the URL for the page I created in Part 1 to list all documents in category ‘Policies’ should be https://<example_site_url>/SitePages/DynamicView.aspx?DocCategory=Policies.

In order to create the navigation links,

  1. In the SharePoint site, navigate to Site Settings > Navigation.
  2. Under Current Navigation, click on Add Link…
  3. Provide a Title (eg: Policy Documents) and URL as /SitePages/DynamicView.aspx?DocCategory=Policies">/SitePages/DynamicView.aspx?DocCategory=Policies">/SitePages/DynamicView.aspx?DocCategory=Policies">https://<your_site_url>/SitePages/DynamicView.aspx?DocCategory=Policies
    Note: The query string name ‘DocCategory’ has to be the same as one you provided for Query String Varable in step 8 of Part 1.
  4. Create links for other categories by just changing the query string value (eg: ?DocCategory=Requests, ?DocCategory=Procedures etc.).
Advertisements

The EXECUTE permission was denied on the object ‘proc_GetProductVersions’- Insufficient SQL database permissions for user

With SharePoint 2010 RTM production farm, the following error was being logged in application event logs with severity level Critical when users try to open some of the site administration pages. An example is Site Actions > More Options page.

Insufficient SQL database permissions for user ‘Name: <user id> SID: <GUID> ImpersonationLevel: Impersonation’ in database ‘SharePoint_Config’ on SQL Server instance ‘<DB Server>’. Additional error information from SQL Server is included below.

The EXECUTE permission was denied on the object ‘proc_GetProductVersions’, database ‘SharePoint_Config’, schema ‘dbo’.

 

In order to resolve the issue, I provided Execute permission to the database role “WSS_Content_Application_Pools” into the stored procedure “proc_GetProductVersions”. I performed the following steps to do this.

  1. In the database server, expand SharePoint Config database and naviage to Programmability/Stored Procedures/dbo.proc_GetProductVersions using SQL Server Management Studio.
  2. Right click on the above stored procedure and select Properties.
  3. On the popup screen, select Permissions on the left and click Search button.
  4. On the new popup screen, click Search, select [WSS_Content_Application_Pools] database role and click OK.
  5. Click OK again.
  6. On the first popup screen, select the role, check Execute permission and click OK.

There is no need to restart any services for this change to take effect.

failure trying to synch site GUID for ContentDB GUID WebApp GUID.

After performing a full farm restore from one farm to another, I noticed that the following event is being logged into application event logs in every one hour.

Event ID: 5553

failure trying to synch site <GUID> for ContentDB <GUID> WebApp <GUID>.  Exception message was Cannot insert duplicate key row in object ‘dbo.UserMemberships’ with unique index ‘CX_UserMemberships_RecordId_MemberGroupId_SID’.
The statement has been terminated..

After researching for some time I got really confused because all the online discussions talk about content database being detached and attached to a different farm without running “preparetomove” command. But what I did was a farm restore not a content database attach. The solution discussed everywhere was to run the following commands.

stsadm –o preparetomove –contentdb <GUID> –site <URL>

stsadm –o sync –deleteolddatabases 5

But reading further I realized that with SharePoint 2010, there is no longer a “preparetomove” switch with stsadm. SharePoint keeps the same GUID for any content databases attached. Then I found this post and as per the instructions in it, I ran the following command.

stsadm –o sync –deleteolddatabases 0

I waited for few hours for the User Profile to SharePoint Full Synchronization job to complete and verified that the error mentioned above has gone.

SharePoint Designer Workflow Actions using elevated permissions

Some of us might have encountered a situation where we need to copy or move a document (usually InfoPath form) once it is uploaded/submitted by the user to a different library where the user doesn’t have permission. With SharePoint 2007, we used an open source extension to achieve this functionality. With the extension, we were able to copy/move the document from one library to another using the System account privileges. Using the system account privilege in a workflow action of course has its own security issues.

Now, SharePoint 2010 workflow (SharePoint Designer 2010) is out of this hassle. SharePoint Designer 2010 workflow has a new feature called “Impersonation Step”. This is nothing but a workflow step by any actions included in this step will be executed using the Workflow author’s privilege instead of the user who is submitting the document. The Impersonation Step is present in the Insert section of SharePoint Designer ribbon.

Tip:

SharePoint Designer always use the logged in user’s (workflow author) credentials for running the impersonation step. If you are unable to log in to the local computer using the account you want to use for impersonation step, you can run SharePoint designer as a different user using the following method.

  1. Navigate to the folder C:\Program Files (x86)\Microsoft Office\Office14\
  2. Right click the file SPDESIGN.EXE and select ‘Run as different user’ context menu item (you can also create a shortcut to this file on Desktop and right click the shortcut).
  3. Enter the network credentials (or different user account) you prefer to run SPD as.