Signature Sterling

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:

1. Create a multiple-value look-up column within the knowledge base list to server record values.
2. 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).
3. 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

4. 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.

5. 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.
6. 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).

=("https://pvhome.pv.com/Departments/IT/PV%20Share%20Documents/KnowledgeBase.aspx?ServerID="&ServerListNameColumn)

I've been dabbling with LINQ recently, specifically within the ADO.NET Entity framework. And I'm not ashamed to admit, it's been kicking my butt up and down the proverbial technology block. Here's a sampling of what I've picked up in the hopes of saving some poor other programming soul some time and headache:

1. When trying to achieve a left outer join, disregard the numerous postings instructing you to utilize the 'DefaultIfEmpty()' method, since that will not work for LINQ to Entity queries (in .NET 3.5 SP1). Upon compilation, you'll see something to the effect of:
   
   System.NotSupportedException: LINQ to Entities does not recognize the method 'System.Collections.Generic.IEnumerable`1[AllStar.DataV2.DataModel.Object] DefaultIfEmpty[Object](System.Collections.Generic.IEnumerable`1[AllStar.DataV2.DataModel.Object])' method, and this method cannot be translated into a store expression..
   
   All LINQ to Entity group joins (the ones with the 'in group' clause at the end) perform a left outer join by default: http://msdn.microsoft.com/en-us/library/bb399367.aspx. (In C#, the syntax for group join is simply 'join').
   
   Here's an example query that should generate a left outer join if you're using LINQ to Entity:

   
   var query = from s in db.Studies
   join sa in db.StudyAccess on s.Id equals sa.StudyId into temp
   where (s.Studyname.ToLower() == studyname.ToLower())
   select new { s.Id, a.Access };
   
   

   *NOTE: Make sure you include the 'into [group]' portion or it will NOT complete a left outer join*
   
2. When trying to perform MULTIPLE left outer joins using LINQ to entity, you'll have to nest the query-groups in order to maintain context (thank-you David Buchanan). Here's what I mean:

   var query = from po in allStarNetEntities.PurchaseOrder
        join pl2 in
            (from pl in allStarNetEntities.POProductLink
             join pd in allStarNetEntities.POProducts on pl.POProducts equals pd
                 into POGroup
             select pl)
            on po equals pl2.PurchaseOrder into POGroup
            where po.Requestor == criteria.SearchText
        select po;