BCS External List Limitations

There are a number of features that are missing when you create an External List based on an External Content Type. This can cause a lot of issues with end users because even though it looks exactly like a normal list it might not behave exactly like a normal list (or at least have some features missing)

1) Workflows cannot be configured on External Lists

Unfortunately as you can see you cannot actually run a workflow on an external list. You neither have the option to define any workflows nor can you actually start one. Now this is even if the workflow that you are thinking of defining will not alter the External List item in anyway, doesn't matter, External List = No Workflows!

2) Cannot create Information Management Policies

This kind of makes sense. I mean you can really declare something a record or move something to another location if you aren't storing it within SharePoint but it is worth knowing. No IMP's means no barcodes, auditing, retention or labels.




3) No Versioning or Version History

You cannot version or see version history for an External List. Now this does make sense since in many external systems there is no concept of versions

4) No Inline Editing or Datasheet View

Inline editing allows you to edit list items from within the list view itself, kind of a datasheet view for an individual item. Datasheet view is a way where you can easily change metadata for multiple items. Neither of these are available.

5) No Ratings

This does suck but you cannot rate any items in an external list.

6) No ability to Export to Excel, Create Visio Diagram, Open with Access or Open with Project

Some of these are not available to all list types (Create Visio Diagram or Open with Project for example) but all standard lists are able to be exported to Excel or opened with Access. This is a real shame I think because exporting to Excel is an oft use feature that will be missed by users.

7) No REST Access through ListData.svc to External Lists

Yep you can’t access an External List though REST...

8) No RSS Feeds

No RSS Feeds are available either

9) No Item Level Permissions

You also cannot configure item level permissions for External Lists.

10) No Item or Field Level Validation

So as you can see there are quite a few limitations that one should be aware of. The thing that is concerning is that from a user’s perspective you can’t actually tell that a list is external and without all these things being available this can lead to some confusion. But it is out of the box functionality after all and with a little SharePoint Designer and XSL magic you might not be in the situation to expose external lists anyway.

11) No Lookups

Unfortunately the only thing that you can do a look up on in an external list is on the ID column, anything else and you are out of luck.

12) No Attachments

No attachments either!

13) Nintex Form

Nintex Forms does not support the ID column when designing forms for a SharePoint external list. It is recommended that the external content type manages the ID creation.

Publishing to Nintex Live is not supported when designing a Nintex form for a SharePoint external list.

Projects' Risks and Issues custom columns query for reporting in 2010 / 2013

Working with Project Server 2010 / 2013, reporting database makes me realize how it can be painful to get information and properties that was customized on Risks or Issues sharepoint lists (or any sharepoint list).

Risks, Issues and Deliverables are lists in the project site, yet they are project's properties and those properties have their own tables in the project server 2010 / 2013. These are the following name of each:

• Risks: MSP_WssRisk
• Issues: MSP_WssIssue
• Deliverables: MSP_WssDeliverable

And since they are sharepoint lists then they can be customized to add columns or update column's values, and there the trick is ... the new custom columns are added to the sharepoint content database as database columns that will be named by the data type of the property and a postfix number, in the [AllUserData] table like: datetime2, sql_variant1, nvarchar9 and etc etc...

After lots of googling the way to do it I have found a blog (unfortunately I can't find it again) that suggested a query to do so, it will be like this:


SELECT B.ProjectUID, B.IssueUniqueID,....., A.datetime2 as [Date Raised]
FROM PWAContentDatabase.dbo.AllLists lists
INNER JOIN PWAContentDatabase.dbo.AllUserData as A ON lists.tp_ID = A.tp_ListId
INNER JOIN dbo.MSO_WssIssue as B ON a.tp_DocId = B.IssueUniqueID
INNER JOIN dbo.MSP_Epm_Project_UserView AS C ON B.ProjectUID = C.ProjectUID


I have tried it and it worked as charm except for one detail; consider having this daily used scenario:

• Open the project site
• Open the issues list (for example)
• Add a new Issue
• Close the Add Issue dialog
• Open the Issue for edit and save again

in that query there will be redundant records for the same item (Issue in our example), and the reason of that is sharepoint lists keep track of each update for versioning issues (Version History, to view all changes took place on this list item). So to avoid such redundant records there is a column in the [AllUserData] table called [tp_IsCurrentVersion] that indicates which record is the working (current) one.

so our final SQL query will be like this:


SELECT B.ProjectUID, B.IssueUniqueID,....., A.datetime2 as [Date Raised]
FROM PWAContentDatabase.dbo.AllLists lists
INNER JOIN PWAContentDatabase.dbo.AllUserData as A ON lists.tp_ID = A.tp_ListId
INNER JOIN dbo.MSO_WssIssue as B ON a.tp_DocId = B.IssueUniqueID
INNER JOIN dbo.MSP_Epm_Project_UserView AS C ON B.ProjectUID = C.ProjectUID
WHERE (A.tp_IsCurrentVersion = 1)


It worked for me I hope it will work for you as well :)

How to create a Web Application with Classic Authentication in SharePoint 2013

As we all SharePoint developer knows, Microsoft didn't provide a UI interface to create a Classic Authentication Web Application in SharePoint 2013.

But you can create a Classic Authentication Web Application by using powershell command, mostly this kind of practices are used during the migration of SharePoint from old version to SharePoint 2013.


==================
Powershell Command
==================

New-SPWebApplication -Name "SharePoint - 80" -ApplicationPool "SharePoint - 80" -ApplicationPoolAccount (Get-SPManagedAccount "Contoso\SPAdmin") -DatabaseName "WSS_Content" -Port 80 -URL http://myportal/

How to download a wsp file from Central Admin

You can easily download either one wsp file from central admin or you can download all wsp files by using powershell command in Sharepoint 2010 and 2013 environment.

============================
Download one wsp file
============================

$myFarm = Get-SPFarm
$myWSP = $myFarm.Solutions.Item("helloworld.wsp").SolutionFile
$myWSP.SaveAs("C:\helloworld.wsp")

============================
Download all wsp files
============================

$pathName = "c:\wspFiles\"

foreach ($solution in Get-SPSolution)
{
    $solid = $Solution.SolutionID
    $title = $Solution.Name
    $filename = $Solution.SolutionFile.Name
    $solution.SolutionFile.SaveAs("$pathName\$filename")
}

Useful Powershell Commands of SharePoint 2013

These are the following powershell commands which is commonly used during development, Farm Creation and for Administration:

===========================
Backup & Restore
===========================

Backup-SPSite -Identity http://ServerName:port -Path "c:\backup\file.bak"

Restore-SPSite -Identity http://Servername:port -Path "c:\backup\file.bak" -force

===========================
Add and Install WSP
===========================

Add-SPSolution c:\helloworld.wsp

Install-SPSolution –Identity helloworld.wsp -GACDeployment -force -WebApplication http://portal

===========================
Remove WSP
===========================

Remove-SPSolution -Identity helloworld.wsp

===========================
Update WSP
===========================

Update-SPSolution -Identity helloworld.wsp -LiteralPath C:\helloworld.wsp -GACDeployment

How to enable Ajax Functionality on Custom Master Page in SharePoint 2013

When you create a custom master page through design manager in SharePoint 2013, then unfortunately design manager does not add Ajax snippets in this page, and due to this you cannot apply Ajax functionality on your custom web part.

This is not a big deal now, you can just add below mentioned snippets in your custom master HTML page under a body tag and the same tag will be remove from Head.

<!--SPM:<asp:ScriptManager id="ScriptManager" runat="server" EnablePageMethods="false" EnablePartialRendering="true" EnableScriptGlobalization="false" EnableScriptLocalization="true"/>-->
<!--MS:<SharePoint:AjaxDelta id="DeltaSPWebPartManager" runat="server">-->
<!--MS:<WebPartPages:SPWebPartManager runat="server">-->
<!--ME:</WebPartPages:SPWebPartManager>-->
<!--ME:</SharePoint:AjaxDelta>-->

How you can change the name of SharePoint from Top Blue Bar

You can use powershell command to change or replace the name of SharePoint which is by default appear on top blue bar.

You can replace a "SharePoint" literal with these are the following options:

• With Label
• With Link
• With Image & Link

===================================
With Label
===================================

Add-PSSnapin Microsoft.SharePoint.PowerShell

# get your site
$myApp = Get-SPWebApplication "http://mysite"

# add text
$myApp.SuiteBarBrandingElementHtml = '<div class="ms-core-brandingText">My Portal</div>'

# update
$myApp.Update()


===================================
With Link
===================================

Add-PSSnapin Microsoft.SharePoint.PowerShell

# get your site
$myApp = Get-SPWebApplication "http://mysite"

# add a linked text
$myApp.SuiteBarBrandingElementHtml = '<div class="ms-core-brandingText"><a style="color:#fff;" href="http://myportal">My Portal</a></div>'

# update
$myApp.Update()


===================================
With Image & Link
===================================

Add-PSSnapin Microsoft.SharePoint.PowerShell

# get your site
$myApp = Get-SPWebApplication "http://mysite"

# add an logo image
# $myApp.SuiteBarBrandingElementHtml = '<div class="ms-core-brandingText"><a href="http://myportal"><img src="/SiteCollectionImages/Logo.png"/></a></div>'

# update
$myApp.Update()