Wednesday, April 29, 2015

Display comments and likes in CQWP

This is an update from my former blog post about showing likes and comments in a news web part. And I also get questions about how I have done this, and people saying that they never managed to do the same. So even if I updated the former blog post with this solution, it may not have been clear enough how I did it.

First of all, I changed from using the CSWP (Content Search Web Part) to use the CQWP (Content Query Web Part) as I never got the Comments to work without always running a Full crawl (and we don't want that!).

Then I create the news on a separate Blog site. The news will then be rolled up on the intranet front page in the following format:


As shown in the picture above, the news item is rolled up displaying Published Date, Title, Likes and Comments. Sometimes I also add the Category, but that part is easy and if you can add one you can add another of these fields, so I will mostly focus on how I did the Like and Comments part.

To modify the CQWP you must first add your own xsl files.
NOTE: do NOT modify the existing files - they might get overwritten in an update OR you might write something totally wrong and nothing will work on your site. You always create a new folder called "Custom" (or whatever you want), then you COPY existing files and paste them into your folder and there you can modify them without taking a risk.

Easiest is to open your site in SharePoint Designer, browse to the folder Style Library > XSL Style sheets > Create your own folder "Custom". Then copy the file called "ItemStyle.xsl" and paste into this new folder.


Now go to the site where you first add the CQWP and then export that same web part. You export a web part by clicking the arrow on the web part and then select "Export":


Open the file, it might be called "News.webpart" or whatever you named the web part, and find the row with the property name "ItemXslLink" and change the path to the new that was created above:

Tag: property name="ItemXslLink" type="string"
New value: /Style Library/XSL Style Sheets/Custom/ItemStyle.xsl

It should look like this in the webpart file now:


Then add the fields that should be displayed in the web part, they need to be added in a special way and you must find out their GUID. Find the property tag called "DataMappingViewFields" and the fields must be added in this syntax: {GUID}, Type and then separated with ;


To find the GUID of the fields that should be displayed, go to the list where the columns are and hover each column or click on it and look in the address field. The URL will contain the GUID. Be sure to remove the ASCII for % and - since they are in the URL.
Example:
I want to get the field where Comments are, (which is a Lookup Field) so I go to my news blog and open the Posts list. Go to the List settings and scroll down to find the Column field. Click on it and copy the URL and then paste that into Notepad. You will get something like this:


Then in Notepad, copy only the GUID from the URL:

%7B6026020B%2D0EB4%2D42D0%2DAA51%2D2BBA3A3CD9DF%7D

And then replace all ascii characters with the normal character like

%7B which is {
%7D which is }
%2D which is -
and you will get

{6026020B-0EB4-42D0-AA51-2BBA3A3CD9DF}

Do this for all fields that you want to display in the web part, like Comments, Likes, Category etc

Before you upload the web part file, go back to SharePoint Designer and open the custom xsl file. Add a new xsl:template in the file like this (it is an image):



The entire code in this is here, but the < tags are exchanged to [] so that the code can be displayed here on the page (just replace the [] with the regular < tags):

[?xml version="1.0" encoding="utf-8" ?]

[xsl:stylesheet
  version="1.0"
  exclude-result-prefixes="x d xsl msxsl cmswrt"
  xmlns:x="http://www.w3.org/2001/XMLSchema"
  xmlns:d="http://schemas.microsoft.com/sharepoint/dsp"
  xmlns:cmswrt="http://schemas.microsoft.com/WebParts/v3/Publishing/runtime"
    xmlns:ddwrt="http://schemas.microsoft.com/WebParts/v2/DataView/runtime"
  xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt"]
[xsl:param name="ItemsHaveStreams"]
    [xsl:value-of select="'False'" /]
  [/xsl:param]
[xsl:variable name="OnClickTargetAttribute" select="string('javascript:this.target="_blank"')" /]
[xsl:variable name="ImageWidth" /]
[xsl:variable name="ImageHeight" /]

[xsl:template name="BigImage" match="Row[@Style='BigImage']" mode="itemstyle"]
[xsl:variable name="SafeImageUrl"]
            [xsl:call-template name="OuterTemplate.GetSafeStaticUrl"]
                [xsl:with-param name="UrlColumnName" select="'ImageUrl'"/]
            [/xsl:call-template]
        [/xsl:variable]
[xsl:variable name="SafeLinkUrl"]
            [xsl:call-template name="OuterTemplate.GetSafeLink"]
                [xsl:with-param name="UrlColumnName" select="'LinkUrl'"/]
            [/xsl:call-template]
        [/xsl:variable]
[xsl:variable name="DisplayTitle"]
            [xsl:call-template name="OuterTemplate.GetTitle"]
                [xsl:with-param name="Title" select="@Title"/]
                [xsl:with-param name="UrlColumnName" select="'LinkUrl'"/]
            [/xsl:call-template]
        [/xsl:variable]      
     
[div id="Item_LargePicture" style="padding-bottom:10px"]

        [div class="cbs-largePictureContainer" id="_#= containerId =#_" data-displaytemplate="ItemLargePicture"]
            [div class="cbs-largePictureImageContainer" id="_#= pictureContainerId =#_"]
                [xsl:if test="string-length($SafeImageUrl) = 0"]
[a href="{$SafeLinkUrl}"]
[img src="/_layouts/15/images/custom/emptynews.png" class="cbs-pictureImgLink" /]
[/a]
[/xsl:if]
[xsl:if test="string-length($SafeImageUrl) != 0"]
[a href="{$SafeLinkUrl}" class="cbs-pictureImgLink" ]
[xsl:if test="$ItemsHaveStreams = 'True'"]
[xsl:attribute name="onclick"]
                          [xsl:value-of select="@OnClickForWebRendering"/]
                        [/xsl:attribute]
[/xsl:if]
[xsl:if test="$ItemsHaveStreams != 'True' and @OpenInNewWindow = 'True'"]
[xsl:attribute name="onclick"]
                        [xsl:value-of disable-output-escaping="yes" select="$OnClickTargetAttribute"/]
                        [/xsl:attribute]
[/xsl:if]
[img src="{$SafeImageUrl}?RenditionID=5" title="{@ImageUrlAltText}" class="cbs-pictureImgLink" id=""/]
[/a]
[/xsl:if]

            [/div]
                     
            [div class="cbs-largePictureDataOverlay"  id="_#= dataContainerOverlayId =#_"][/div]
            [div class="cbs-largePictureDataContainer" id="_#= dataContainerId =#_"]
             
                [a class="cbs-largePictureLine1Link"]

[h2 class="cbs-largePictureLine1"]

[a href="{$SafeLinkUrl}" title="{@LinkToolTip}" class="cbs-largePictureLine1Link"]
[xsl:if test="$ItemsHaveStreams = 'True'"]
[xsl:attribute name="onclick"]
                  [xsl:value-of select="@OnClickForWebRendering"/]
                    [/xsl:attribute]
[/xsl:if]
[xsl:if test="$ItemsHaveStreams != 'True' and @OpenInNewWindow = 'True'"]
[xsl:attribute name="onclick"]
                      [xsl:value-of disable-output-escaping="yes" select="$OnClickTargetAttribute"/]
                    [/xsl:attribute]
[/xsl:if]
[xsl:value-of select="$DisplayTitle"/]
[/a][/h2]

[/a]
           
                [span title="_#= $htmlEncode(line4) =#_" id="_#= line4Id =#_" ]
                [xsl:value-of select="ddwrt:FormatDateTime(string(@PublishedDate) ,1053 ,'dd MMMM')"/][br /]
                [xsl:value-of select="@PostCategory"/][/span]
                [span title="_#= $htmlEncode(line5) =#_" id="_#= line5Id =#_"]
                [span style="padding-left:180px;"][xsl:if test="string-length(@LikesCount) = 0"] 0[/xsl:if]
[xsl:value-of select="@LikesCount" /][span style="padding-left:3px;vertical-align:text-bottom"][img src="/_layouts/15/images/custom/like.png" border="0" /][/span][/span][span style="padding-left:10px;"][xsl:value-of select="@NumComments" /][span style="padding-left:3px;vertical-align:text-bottom"][img src="/_layouts/15/images/custom/comment.png" border="0" /][/span]
[/span]
[/span]            

            [/div]
        [/div]
    [/div]      

[/xsl:template]
[/xsl:stylesheet]


Okey so now we have a style for the news items. But there still are some things more to do.

Enable Image Rendition and Blob Cache to make the images render in the correct size, like thumbnails.

First upload this new web part into the sites WebParts gallery (its better to do that than to just upload it straight into the page, and you can also give it a proper name and put it in its own folder so its visible in the gallery when you add a new web part)

Then go to the start page and add this new web part. You have to modify it to point out the news blog location, and to select the correct display for it. And to make other settings that you need, like how many items that should be shown, what sorting order you want, if you want to add any filters etc.

In the web part settings, also go down to the section where the fields are mapped. Then add the names of the columns that you want to display, like PublishedDate, Category, Likes etc and you will find that there are new placeholders for these as we added those in the webpart file:


Yes it is in Swedish, but if you are familiar with CQWP (which I really hope you are, since this is quite complex to setup) then you should recognize the fields I am talking about. So, type in the name of the fields you want to display and DONE. But what? No, you cannot find a field for Comments? How surprising ;) This is where we need to go to Central Administration on the server and a new Mapping property in the Search app.

Go to Central Admin, open the Search service app, click on the link "Search schema" in the left menu and there add a "New managed property":



Call it what you want, like "NumberOfComments". Then set it to be Searchable, Retrievable, and then scroll down to the mapping. Add a new mapping and find the field called "ows_NumComments" and then Save this. You MUST run a full crawl for this to apply.



Okey, that was it. Simple right?? :) :)

27 comments:

Anonymous said...

Hi ,

I tried the same steps as you listed but for some reasons i couldn't. I am trying to do it for a blog that should appear to the main site.

thanks

Divya

Lise Rasmussen said...

Hi Divya!

Please explain more to me, what is not working?
/Lise

papalazarou1975 said...

Can you help me please I've followed your steps and I am getting an error when loading the webpart to the page.

THE FILE YOU IMPORTED IS NOT VALID, VERIFY THAT THE FILE IS WEB PART DESCRIPTION FILE (*,WEBPART OR *.DWP) AND IT CONTAINS WELL FORMED XML

Please help!! Thanks,

Lise Rasmussen said...

Hi Papalazarou975. Please make sure that the xml code is correctly written in your .webpart file. You have to replace the [] characters with <> so you get the correct syntax. And if you copy text from my site, first dump it into Notepad and then copy/paste it into your .webpart file so that you dont get any special characters or spaces in your file. Let me know! Best regards Lise

Anonymous said...

Hi Lise,

I tried to use the same for the blog to show the lates blog post and number of comments and number of likes. For some reason i couldn't. can we do the same with blog?

thanks

Lise Rasmussen said...

Hi Anonymous


This solution is for blogs, I have news in blog format. Just make sure that you have added the Comments as a Managed Property in Search schema, otherwise the results will not show up in your web part. And make sure you get the GUID for all the fields you want to display.
Good luck!!

Divya said...

Hi Lise,

when i tried to upload the webpart i am getting the following error.

"The webpart references an untrusted XSL file. Only XSL files contained in this site's style library may be referenced".

thanks

Lise Rasmussen said...

Hi Divya
Please mail me the file and I will help you correct it.
my mail lise at liserasmussen.se
Best regards Lise

Lise Rasmussen said...

Hi Divya,
I have got your file now. This is my answer. But you have not told me what is not working, so I will send you some control questions first:

1) In the ItemStyle file, have you seen the paths that points to some images that you should have in your local hive on the server? You need to have an image for Like and Comment and empty news:

/_layouts/15/images/custom/emptynews.png
/_layouts/15/images/custom/like.png
/_layouts/15/images/custom/comments.png

The first image is the image that will be displayed when the News item does not have a thumbnail picture.
The second image is the Like picture that is displayed in the News web part
The third image is the Comment picture that is displayed in the News web part

2) In the Webpart file you need to make sure that you have added all the fields for Comments, Likes, Published, Category etc with their GUID's

Let me know if this helps!
Good luck!
BR Lise

jordy stoffels said...

Hi,

I tried to get it working on my site but i get the next error:

"The webpart references an untrusted XSL file. Only XSL files contained in this site's style library may be referenced"

I did exactly what you describe. I'll hope you can help me

Lise Rasmussen said...

Hi Jordy! This is probably an easy problem to solve, I think you have to check the paths that are added in the itemstyle file and check the path in the webpart file so that it points to the location where you have added the itemstyle file.

In the webpart file you have a path that points to the itemstyle.xsl file, make sure it is correct and points to your environment:
You find the path within this tag: "property name="Xsl" type="string""

In the itemstyle file you have several paths, do a search within the file and search for "custom". That will take you to the places where I have changed the paths to different files like images, etc.

Good luck and hope this helps!
/Lise

jordy stoffels said...

I just copied the text from the site and put it in a new xsl file. I didn't make any adjustments.

Or do i need to copy the itemstyle.xsl and put in template you describe ?

Lise Rasmussen said...

Aha okey, well that might explain the errors. Because you need to verify :

- that you have created the same paths as I have added in the web part (you create a new folder in the xsl style sheets library on your server)

- that you have added any files in the new paths that i have pointed to in the Itemstyle file, the paths that contains "Custom" (there is folders for images like "Like", "Comments", "Empty news" etc)

If you dont do this, the web part will fail.
/Lise

jordy stoffels said...

And i can't find the field GUID only the GUID of the list. If i clikc the field it opens and i dont see any GUID?'

Can you help me with this? I'm using Sharepoint online

Lise Rasmussen said...
This comment has been removed by the author.
Lise Rasmussen said...

If you are on SharePoint online, then you can add the following after your site to get all fields and stuff in GUID (you need to run this in Chrome, did not work in IE for me)

https://yoursite/subsite/_api/Web/AvailableFields

jordy stoffels said...

I'm always working Chrome :). I made some adjustments but i always get the same error.

"The webpart references an untrusted XSL file. Only XSL files contained in this site's style library may be referenced"

The path i have given in webpart file is:

/Style Library/XSL Style Sheets/Custom/CommentItemStyle.xsl

and in the xsl file by image scr i have:

/_layouts/15/images/custom/Like.png
/_layouts/15/images/custom/Comment.png
/_layouts/15/images/custom/Emptynews.png

I cant figure it out :S:(

Lise Rasmussen said...

Give me your mail address and i will send you a copy of my files :)

jordy stoffels said...

You can mail me on j.stoffels@unive.nl

I see a path in the xsl file with the following link:

/_layouts/15/images/custom

But my site has this address

https://univeservices.sharepoint.com/sites/mysite

So i think it is a subsite and i can't come to /_layouts/15/images/custom

Lise Rasmussen said...

no that is correct, you cant reach the hive when you are on sharepoint online. just add the images in your Site Assets on root level and point the path to that location instead. that will work. try before i mail the files.

jordy stoffels said...

i tried but still the same error :-(

Lise Rasmussen said...

I have sent you my files now, hope that helps you. Remember to change any layouts/15 paths to your local site path

jordy stoffels said...

Hi Lise,

I looked at your files and compare it with mine. By DataMappingViewFields are differents. You got:

Image;
Text;
LookupMulti;
Likes;
Lookup;

And i got:
Image;
Likes;
DateTime;

Im looking for the GUID of the field text. But i don't know of this is the problem

Lise Rasmussen said...

Hi Jordy! I have now published the video with instructions on how to setup this web part. https://youtu.be/sQaDuKQpELI

jordy stoffels said...

Hi Lise,

The video is deleted https://youtu.be/sQaDuKQpELI

Can you upload again ?

Lise Rasmussen said...

Here it is! https://youtu.be/16zOtUxxqng

Leslie Lim said...

This is really interesting and knowledgeable. Thanks for sharing. I really appreciate it a lot. Please do more blogs in the future. Thank you and God bless to the blogger!

Rica
www.imarksweb.org