Filtering in SharePoint DVWPs with Multiple Lists in the Data Source
Another question from my contact form:
Hi Marc.
My problem in short is to get a filters to work on columns that holds values from linked tables. For instance I have a column with
xsl:value-of select="../../../Products/Rows/Row[@Title=current()/@ProductID]/@ProductGroup"
Am at a loss for a way to do it at this point. A hint would be greatly appreciated.
And my response…
I’d build the filtering mechanisms separately from the DVWP which displays the items. You can use any controls you like, but I usually use DVWPs. In those DVWPs, post back to the same page based on the user selections with the filter values on the Query String, something like this:
http://servername/sitepath/default.aspx?ProductID=X&ProductGroup=Y
Then in your DVWP below, add filtering logic into your row selects, like this:
<xsl:variable name="Rows" select="/dsQueryResponse/primaryList/Rows/Row[
(@ProductID = $ProductID or string-length($ProductID) = 0) and
(@ProductGroup = $ProductGroup or string-length($ProductGroup) = 0)
]"/>
At this point, you’re doing all of the filtering in your XSL yourself. One of the problems with the mumbo jumbo code that SharePoint Designer creates for filtering on column headers is that it is pretty difficult to alter. Keep in mind that SPD is essentially just a code generator and it has to leave "hooks" in for the types of things the you *might* ask it to do later. One caveat: once you start writing your own XSL, don’t ask SPD to do things through the dialogs, as it may well break things on you. (Not all dialogs will cause problems, but until you are very familiar with what each choice generates in the XSL, this is the best rule to follow.)
Thanks for this, Mark! Another great post in helping some of us less enlightened users in figuring out how to work with SP more easily.
I’ve got a scenario to pose to you- What if you wanted to have a conditional row select that filtered based on a query string value passed in, but would otherwise show all values unfiltered if there was no query string value? I tried the following, to no avail:
url: http://servername/sitepath/default.aspx?ProductID=X
In the DVWP:
Any idea how to get around this?
Cheers!
-n
Norm:
If I understand your question, you’d do something like this:
What this does is show the rows where the ProductID matches the one on the Query String OR the ProductID on the Query String is empty.
M.
Ah, I see. I thought that the string-length check in your original example was to default to true (ProductID) in case only the second variable (ProductGroup) was passed in the URL.
Thanks Mark! You’re a lifesaver!
Sure thing, Norm. Glad to help!
M.
Marc,
I posted this question on another site, but not sure if you check there often. I have a interesting question regarding filtering. So I thought I would post it here and get your thoughts. I have 2 list that are linked to data in 2 different tables. The first list has something like the following Name, DOB, Address, etc.. and GroupCode. The second list is user_name, GroupCode. The second list contains a record for every GroupCode you have been authorized to view. What I would like to do is do a xpath query to retrieve all the GroupCodes where user_name = current_user.
If I was doing this in SQL it would look something like Select Name, DOB, Address from tabl1 where GroupCode in (select GroupCode from table2 where user_name = $current_user)
The result set from the SQL then could be passed to a dataviewer object for display which can be filters can be applied on just the data returned.
I’m off base on being able to achieve this with DVWP or do I need to look at writing a C# code to this? Thanks for any advice, help, examples.
Jay
Jay:
You can absolutely do this with a DVWP. It’ll probably require a bit of manual XSL writing, but totally possible.
M.
It seemed like I could do that, but I see so many different ways to do different things, but nothing exacting like I was describing. I don’t mind writing the XSL. I was thinking that maybe the best way to do it was write an XSL that did nothing but return the values I need and import that XSL into the DVWP to use for filtering. I appreciate the response. Your book was a big help to me over seas last year. I was responsible for helping push people to SharePoint to increase information sharing. As they started using the demand for more specialized DVWP, it really helped me satisfy user needs without the need for writing c# or VB code.