SharePoint REST API to get Discussion List Questions and Replies separately


To get the list of questions from a SP 2013+ Distribution list using OOTB REST API:

_api/lists/getByTitle(‘listname‘)/items?$filter=ContentType eq ‘Discussion’

or

/_api/lists/getByTitle(‘listname‘)/items?$filter=FSObjType eq 1

To get the list of replies from a SP 2013+ Distribution list, using OOTB REST API:

_api/lists/getByTitle(‘listname‘)/items?$filter=ContentType eq ‘Message’

or

/_api/lists/getByTitle(‘listname‘)/items?$filter=FSObjType eq 0

To get the list of Discussions with the replies count from a SP 2013+ Distribution list, using OOTB REST API:

/_api/lists/getByTitle(‘listname‘)/items?$select=ID,Title,Body,Folder/ItemCount &$expand=Folder/ItemCount &$filter=ContentType eq ‘Discussion’

Having multiple JSLink based webparts on the same page, overrides all the other templates in SP 2013


Scenario:

Create a dashboard page in SP 2013, displaying just 3 lists in 3 different views using Js Link capability. The 3 lists are:

  1. Accordion: A list containing title and description which will be displayed on the dashboard with an accordion functionality with title being the accordion item heading and description being the accordion item body.
    Image
  2. Menu: A list containing entries that can be used to construct a mega menu or a navigation menu.
    Image
  3. Alert: A list containing entries whose Body contents are lengthy and hence have to be trimmed down and ellipsis (…) added to the lengthy ones
    Image

The dashboard page was created and all the 3 list view webparts were added to it. All of them were given 3 different JsLink with different namespaces and method names. Each js had their template methods that rendered the list in the desired views and functionalities. ex. the accordion template method just returned “<h2>{Title}</h2><p>{Description}</p>” and an OnPostRender jQuery script to toggle them on <h2> click.

Issue:

Loaded the Alert list first on the fresh page–> No issue; trimming the body content worked perfectly

Image

Loaded the Accordion list next –> No issue; slide up and down of the accordion worked perfectly
Image

Loaded the Menu list next –> Boom! The accordion lost its Js Link functionality and started behaving like the menu displaying items using the template defined for the Menu list.

Image

Attached below are the 3 JsLink files used:

AlertsAccordion | Menu

Feel free to test it out…

If the JS Links had different namespaces & methods, why would it overwrite all the other templates on the same page? Could it be by design? By changing the BaseViewID, i could get the Accordion working but the menu then started taking the accordions template behavior…:(

Finally, I ended up having a single Js Link file (added as JS Link to all the webparts on the page) having the same template method, but internally it loaded different templates for each webpart by doing an <if> condition check for the List Name.
ex. if (ctx.ListName==’Accordion’) return “<h2>{Title}</h2>…”; if (ctx.ListName==’Menu’) return ‘<ul><li>{Tite}</li></ul>’; etc…

But somehow you will feel something is not right when you code it. Having a bulky js method with If-else or switch-cases…:(

Solution:

Paul Hunt (@Cimares) has the solution for this scenario wonderfully detailed out here.This solution works and you can do an IF condition check to load the templates corresponding to each webpart accordingly.

I still wish if i could use independent JS Link files for all the webparts on the dashboard…:(…But, all in all JSLink is wonderful and I am happy to see MS taking the client side route and would love to support them in this direction…:)

Feel free to post your comments or possible alternatives or solutions.

JSLink SharePoint 2013 troubleshooting


JsLink is a good feature that was introduced in SP 2013 to allow users to bring in more flexibility in Client Side Rendering (CSR). With this you can control how your list data is getting displayed in View, Display, Edit and New forms.

For ex., you can achieve this using JSLink:

Image

 

You can check the implementation here: JSLink Custom JS Rendering Tutorial

ISSUE:

The JS Link was still not working and the list view remained the same.

SOLUTION #1:

Check the URL you defined in your JSLink webpart property (under Miscellaneous). You need to define the URL as: ~site/Site Assets/xyz.js” instead of defining the absolute or relative URL of your js file. It should always begin with a given set of URL tokens and then your file path. JsLink URL can begin with either of the following URL tokens: ~site, ~sitecollection, ~layouts, ~sitecollectionlayouts or ~sitelayouts only. Absolute paths will break the JsLink functionality.

SOLUTION #2:

MDS (Minimal Download Strategy) strategy having issues. Try using RegisterModuleInit(“{file URL}”, MyJsMethod); to register the JSLink. Check this link.

Also, as a best practice, its recommended to specify the SharePoint CSR filename in the JS Link URL.

ex. The JSLink URL for Tile View in Promoted Links would look like:

sp.ui.tileview.js | ~site/SiteAssets/xyz.js

SharePoint 2013 BCS with WCF data source: Unknown Error occurred. Unable to load one or more of the requested types.


This is a common error that you would have faced, while adding a WCF service endpoint to BCS through SharePoint Designer 2013, connected to Office 365. The complete error would look like this:

Unknown Error occurred. Unable to load one or more of the requested types. Retrieve the LoaderExceptions property for more information.

This is an error observed while connecting to a WCF data source through SharePoint Design 2013. Ensure that your WCF service endpoint is working perfectly.

Fix:

STEP 1: Open the “SPDESIGN.exe.config” file from SharePoint Designer folder ()

STEP 2: Update the file with the following configuration:

<?xml version=”1.0″ ?>
<configuration>
<startup>
<requiredRuntime version=”v2.0.50727″ />
<supportedRuntime version=”v2.0.50727″ />
</startup>
<runtime>
<assemblyBinding xmlns=”urn:schemas-microsoft-com:asm.v1″>
<dependentAssembly>
<assemblyIdentity name=”Microsoft.BusinessData” publicKeyToken=”71e9bce111e9429c” culture=”neutral” />
<bindingRedirect oldVersion=”16.0.0.0-65535.65535.65535.65535″ newVersion=”15.0.0.0″/>
</dependentAssembly>
</assemblyBinding>
</runtime>
</configuration>

STEP 3: Save the file and run SharePoint Designer once again.

STEP 4: Try adding your WCF data source once again. Voila! you should see it work now…:)

The fix is just telling SPD to run BCS on version 15.0.0.0, if its gets a reference to version 16.0.0.0. I guess our Office 365 online considers SharePoint 2013 version as 16, instead of 15. Forward thinking…quite understandable…:)

Hope this helps!

 

 

Display Project Server 2013 Excel Reports in your SharePoint Site


This post is in continuation to my previous post on Building Excel reports from Project Server 2013 OData Services.

Now that you have your Excel Report and started being innovative in building jazzy reports and dashboard, you might want to display it on your homepage so that you could get the snapshot of everything when you login online.

The only requirement for this is to ensure that you have “Excel Web App” installed and the required features enabled.

Step 1: Upload your excel reports to a Document library and open it. This will open up the Excel report in a WOPIFrame page, with the URL as:
https://servername/sites/pwa_name/ProjectBICenter/_layouts/15/WopiFrame.aspx?sourcedoc=/sites/pwa_name/ProjectBICenter/Sample%20Reports/Project%20Overview%20Dashboard.xlsx&action=default

Office Subscription Screenshot Excel Web App

WopiFrame

 

 

Step 2: Copy the above URL to your clipboard.

Step 3: Edit your Project Site, add a Page Viewer webpart and set the type as “WebPage” and “URL” as the one copied from step 1.

Outcome: The outcome will be something like this

ExcelReports

 

Hope this helps!!

 

Building Excel reports from Project Server 2013 OData Services


This is quick walkthrough to showcase how you could build powerful reports from Project Server data online, in a jiffy.

I am using the Project Online Subscription from Office 365. However, this should work with your on-premise Project Server 2013 setup as well. So no worries…Interestingly this technique can be extended to any other data source…let’s say getting all your bug reports from Team Foundation Server to Excel, getting the Northwind Products table from an OData source online etc.

Without much adieu let me start… just 3 Steps

Step 1: Copy the OData Connection file URL from Project Online, under Data Connections library in the ProjectBICenter. You can find it in the following URL:

/sites/pwa/ProjectBICenter/Data%20Connections/Forms/AllItems.aspx?RootFolder=%2Fsites%2Fpwa%2FProjectBICenter%2FData%20Connections%2FEnglish%20%28United%20States%29&FolderCTID=0x012000E9798414DD31E84AB759BED87428492E&View=%7B17A6948A-A122-4B2E-9398-FC156E6BE875%7D&InitialTabId=Ribbon%2ERead&VisibilityContext=WSSTabPersistence

Image

Not able to get here?

Navigate to your “pwa” home page (http://servername/sites/pwa) and click on “Reports” in the left navigation. It will take you to the ProjectBICenter site with the URL as (/ProjectBICenter/Pages/Default.aspx). Then click on “Reports” link on the left navigation in the ProjectBICenter site. You should reach the above place.

Step 2: Open new Excel Workbook and select “Existing Connections” from “Data” ribbonClick “Browse for More” button at the bottom left corner paste the above copied URL in the “Filenamesection and press Open.

Image

Step 3: Select “OK” to view the data as a table

Image

Final Outcome: Entire Project Online data is available to you in excel for data crunching. Also, data is constantly in sync with online.

Image

To synchronize data with Project Online: Select “Refresh All” under the “Data” ribbon

Image

Well you got through the hard part now…rest all i probably would leave to a good excel data cruncher to build charts, pivots, power views, KPIs and other dashboards out of this rich data.

If you see any missing columns, that’s probably coz they are not pulled as a part of your OData URL. If that’s the case, go back to Step 1 and then check the OData URL…add the column names you want (use your SharePoint skills to get the exact column name). For further tweaking the OData URL and its query options, you can visit OData.org and learn more…

In the upcoming posts, i shall show you how to display this on your Project Site in a webpart.

Feel free to post your comments, if you need any assistance.

Hope this was useful!!