Filter related items by almost any field of SharePoint form

In this article I'd like to introduce you to a new feature of SharePoint Forms Designer 2.9.1 allowing to filter the Related items control by almost any form field including lookup, single line of text, number, choice, date, user, and even calculated column. First, I want to demonstrate the most common case, filtering by a lookup column.

Filtering by Lookup column

I've created a list of projects and a related list of issues. The Issues list contains a lookup column to the Projects list. Now I will show how to create a form for the Projects list with the list of related issues in it. We need to put the Related items control onto the project form and configure its data source following way:

Project column of the source list is a lookup to the Projects list. Here is the result:

As you might noticed I've configured filtering by a display column of the lookup column. But if you say have multiple projects with the same title you will see issues of all such projects in the same form. To avoid this you should add ID of the parent list as an additional column in the lookup settings:

Now you can filter the related issues by ID of the parent item in the Data Source Editor:

Filtering by Date column

Ok, now let's configure filtering by a date column. I've created Daily Reports list to store forms with the list of solved issues filtered by a date specified in the report. As previously, we need to place the Related items control onto the form in Forms Designer and configure its data source following way:

DateCompleted is a field of the Issues list containing resolution date of an issue. Here is the result:

Filtering by User column

And finally, I'd like to demonstrate how to filter the Related items control by a people picker field. For this case I've created User Reports list containing a people picker field. Next, we need to design a form with the Related items control linked to the Issues list and filtered by the people picker column:

Almost done. But in contrast to the previous cases we have to do additional stuff here because SharePoint returns people picker value as a link. So we need to extract plain username from the link and pass it outside the form to filter the related items properly. Put HTML-control onto your form, switch CDATA property to False and insert the following code into Content property:

<xsl:variable name="UserName" select="substring-after(substring-before(substring-after(@User,'userdisp.aspx?ID='),'&lt;'),'&gt;')"/>
<xsl:comment>
<xsl:value-of select="ddwrt:GenFireConnection(concat('*', '@User=', ddwrt:ConnEncode(string($UserName))), '')" />
</xsl:comment>

Please, pay attention that to make this sample working you have to replace the highlighted attributes with the internal name of your people picker field (Form field). Here is the report:

Summary

In this article I demonstrated how to filter related items by almost any field of the parent form. Please, note that if you need to filter related items by multiple columns, you can concatenate them into a single calculated field and configure filtering by this column. Feel free to ask your questions in the comments.

CodeProject