There's been much published about how to achieve a 1-many relationship using SharePoint lists such as
this one (one parent to multiple children). This method works great for exhibiting master/detail-type relationships. However, I was tasked with generating a knowledge base list to which our internal IT professionals could publish content and reference for information related to various server records within our SharePoint server list for troubleshooting or informational purposes. Since any single server record could have multiple related knowledge base articles, and any knowledge base article could potentially apply to multiple server records, I needed to emulate an (automated) many-to-many relationship between the server and knowledge base list. Given the limited resources available describing how to achieve this behavior, I decided to post my solution.
My task called for a ‘Knowledge Base’ column within the server list which would contain a hyperlink to a page displaying all of the knowledge base articles pertaining to that particular server record. Here’s how I achieved such behavior:
-
Create a multiple-value look-up column within the knowledge base list to server record values.
-
Create a KnowledgeBase.aspx page (within SharePoint Designer) and drag/drop a knowledge base list onto the page. (See the article above for more detailed instruction).
-
Add a data view parameter to the list called ‘ServerQS’ with value ‘ServerID’.
-
Select the list within the design view
-
Click the grey box with the ‘<’ symbol that appears to the right of the list (to access Common Data View Tasks)
-
Click ‘Parameters’
-
Click ‘New Parameter’
-
Enter ‘ServerQS’ for the parameter name
-
Select ‘Query String’ as the parameter source and enter ‘ServerID’ as the query string variable.
-
Click OK
-
Filter the Data View by using the ServerID query string value:
-
Navigate to the Common Data View Tasks pane again.
-
Select ‘Filter’
-
Check the ‘Add XSLT Filtering’ box and click the associated ‘Edit’ button.
-
Choose ‘All’ from the ‘Select a function category:’ dropdown.
-
Select the ‘contains’ function, you should see the textbox above populate with your selection.
-
Expand the dsQueryResponse > Rows > Row tree in the left-hand navigation and select the server look-up column created earlier (I called mine ‘Network Appliance Hostname’).
-
Paste ‘$ServerQS’ as the second parameter in the contains() function within the textbox.
-
Click OK.
-
Save/publish your page.
-
Test your reference page:
-
Create a new knowledge base article and select arbitrary server look-up values.
-
Visit your new page URL providing one of the look-up values as a query string parameter: KnowledgeBase.aspx?ServerID=TestServer.
-
Confirm your newly created knowledge base articles are displaying.
*Note: The ‘Common Data View Tasks’ pane also provides a plethora of formatting settings for the result set such as sorting, grouping. In addition, the ‘Change Layout’ option, ‘General ‘ tab contains a toolbar option allowing users to dynamically implement their own sorting, grouping, and filtering preferences within the page.
-
Add the knowledge base hyperlink (look-up) column to the server list:
-
Create a calculated column within the server list.
-
Provide the following formula, where ‘ServerListNameColumn’ equals the name of the column used to uniquely identify your server records (and the value provided as the ServerID query string parameter).