A way to write a view to see where another person is one of four positions?

Question

I have created a view that filters a list based on an OR condition between several columns using the [Me] keyword. I want to write the same kind of view, but that allows a person to be selected (i.e. allows you to view the items where another person has any of the roles).

I can do this in Access pointed against the list with the following query

SELECT [My List].[My Name], [My List].[Role 1], [My List].[Role 2], [My List].[Role 3], [My List].[Role 4]
FROM [My List] 
INNER JOIN [User Information List] 
ON ([User Information List].ID=[My List].[Role 1]) 
Or ([User Information List].ID=[My List].[Role 2]) 
Or ([User Information List].ID=[My List].[Role 3]) 
Or ([User Information List].ID=[My List].[Role 4])
WHERE UCase([User Information List].[User name])=UCase([USER_ID]);

but I can't figure out how to pass in the User ID as a parameter in SharePoint, perhaps it can't be done. It also occurred to me that grouping by this value would be sufficient, but I can't find a way to group by an or condition either...

Answer 1

I suppose you're looking for filter parameters.

You can pass a value to a list view using parameters. In SharePoint Designer they have the ribbon button for managing the parameters:

So, you should navigate to a list edit page in SharePoint Designer, select your view, and then, select the XsltListViewWebPart element which represents your view in the page preview window.

Then, if you click the button, you can add a new parameter and select it's type.

For this example, I'm using Query String parameter, but for convenience, if you have SharePoint Server, you'd better add one of existing Filter Web Parts to the page, and use it in conjunction with a parameter of type 'None'.

If you don't like standard web parts functionality (I'm afraid as soon as User Information List is hidden, you can't reference it in OOTB SharePoint List Filter) or if you have Foundation (as do I), you will need to write your own webpart (implementing ITransformableFilterValues interface). You can find an example of doing this on MSDN:

• http://msdn.microsoft.com/en-us/library/bb457205(office.12).aspx

So there, you should probably implement a very simple filter web part, with a dropdown list, filled from User Information List.

Anyway, after you have your parameter created, your second step will be to create the filter itself. Just click the [Filter] button on the SPD ribbon, and add filter rules for all 4 role columns, connecting them with 'Or' statements. You will be allowed to use the parameter in the filtering rules:

After applying the filtering, you can eventually go to your list view page, pass your parameter value, and get the result:

Here I'm passing the parameter using Query String, but as I previously stated, this can be done using user-friendly filter webpart.