SharePoint 2013 External list synchronized with a native list

As you are probably aware, in SharePoint Server 2013 we can utilise external data thanks to Business Connectivity Services (BCS).
We can create External Content Types in SharePoint Designer, define what we want to do with our data (all CRUD options available) and display that data in an External List.
One would say it is brilliant. We can use SharePoint to CRUD data in another LOB systems e.g. another SQL database. It is good but not perfect. If you ever worked with SharePoint External List, you had noticed that we cannot utilise SharePoint core functionality available in native SharePoint lists. These are the features which are currently not supported:

- Per-location view settings
- Metadata navigation settings
- Custom columns
- IMP settings
- Alerts
- Export to Excel (this may work arguably)
- Versioning

Yes, instead of an External List you can just create External Columns an use them in your native SP lists to have above features available. This however was not an option for me as lookup columns will not take into account any new items that have been created in an external system.

I have decided to replicate my External List to a native SharePoint list and create a PowerShell script to automate the process. Now, many scripts I have looked at do the similar thing but still it was not quite what I was looking for. The scripts I found on the internet were exporting External List into a CSV file and importing the values into a native SP List. One script was checking if an item exists in a SP List and if not, it created a new one. Some of the scripts were also updating ALL items instead of the ones that have changed.

My requirements were:

1. Export an External List into a CSV file (always up to date info)
2. Import a CSV file into a SP Native List
    a) Check if an item exists, if not - create it
    b) Check if an update is needed, if yes- update JUST the items that changed (needed for
        efficient versioning)
3. Run the script on a scheduled basis for automation
4. Utilise SharePoint functionality (alerts, metadata etc. in a Native List)

Here is my script to accomplish points 1 and 2 above
Thank you Dan Christian for ideas:

Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 

#Get the context
$ctx = Get-SPServiceContext https://intranet.company.com

#Get the scope
$scope = new-object Microsoft.SharePoint.SPServiceContextScope $ctx
  
#Get the target site collection 
$webTarget = Get-SPWeb -identity "https://intranet.company.com/site"
  
#Get the Target List 
$list = $webTarget.Lists["Name of External List"] 
  
#Array to Hold Result - PSObjects 
$ListItemCollection = @() 
  
#Get All List items (ColumnID is greater than or equal to 1)
 $list.Items |  Where-Object { $_["ColumnID"] -ge 1} | foreach { 
 $ExportItem = New-Object PSObject
 $ExportItem | Add-Member -MemberType NoteProperty -name "Column1" -value $_["Column1"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Column2" -value $_["Column2"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "Column3" -value $_["Column3"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "ModifiedDate" -value $_["ModifiedDate"]
 $ExportItem | Add-Member -MemberType NoteProperty -name "ColumnID" -value $_["ColumnID"]
#Add more columns if required
 
 #Add the object with property to an Array 
 $ListItemCollection += $ExportItem
 } 
 #Export the result Array to CSV file 
 $ListItemCollection | Export-CSV "\\fileserver\directory\export.csv" -NoTypeInformation                        
#Dispose the web Object 
$webTarget.Dispose()

#Pause for 10 seconds for the CSV file to populate all the way.
Start-Sleep 10
function Update-SPList()
{
#Import to destination list
#This section of the PowerShell will loop through the csv file we created, compare the inventory list and add new item if it didn't exist.
$csvVariable= Import-CSV -path "\\fileserver\directory\export.csv"

# Destination site collection
$WebURL = "https://intranet.company.com/site" 

# Destination list name
$listName = "SP Native List" 

#Get the SPWeb object and save it to a variable
$webDestination = Get-SPWeb -identity $WebURL 

#Get the SPList object to retrieve the list
$list = $webDestination.Lists[$listName] 

#Get all items in this list and save them to a variable 
$items = $list.items 

#loop through csv file
foreach($row in $csvVariable)
 {
  #set variable for adding new items
  $added = 0
  #set variables for specifying a query needed for updating items
  $spQuery = New-Object Microsoft.SharePoint.SPQuery
  $camlQuery = '<Where><Eq><FieldRef Name="ColumnID" /><Value Type="Text">'+ $row.ColumnID +'</Value></Eq></Where>'
  $spQuery.Query = $camlQuery
  $listItems = $list.GetItems($spQuery)
  $updateitem = $list.Items | Where { $_["ColumnID"] -eq $row.ColumnID }
 
  #loop through SharePoint list
  foreach($item in $items)
  {
   #check if item exists
   if($item["ColumnID"] -eq $row."ColumnID") 
            { 
                Write-Host "Item already on the list"
    $added++
            } 
  }
        #add new item if item does not exist
        if($added -eq 0)
            {
    Write-Host "Adding a new item"
    $newItem = $list.items.Add()
    $newItem["Column1"] = $row."Column1"
    $newItem["Column2"] = $row."Column2"
    $newItem["Column3"] = $row."Column3" 
    $newItem["ModifiedDate"] = $row."ModifiedDate"
    $newItem["ColumnID"] = $row."ColumnID"
    $newItem.Update()
            }
  #check if ModifiedDate in CSV not equals ModifiedDate column in SharePoint list- item needs to be updated
  elseif ($updateitem["ModifiedDate"] -ne $row.ModifiedDate)
   {
    Write-Host "Updating" $updateitem["Column1"]
    $updateitem["Column1"] = $row."Column1"
    $updateitem["Column2"] = $row."Column2"
    $updateitem["Column3"] = $row."Column3"
    $updateitem["ModifiedDate"] = $row."ModifiedDate"
    $updateitem["ColumnID"] = $row."ColumnID"
    $updateitem.Update()
   }
  #otherwise no update needed
  elseif ($updateitem["ModifiedDate"] -eq $row.ModifiedDate)
   {
    Write-Host "No need to update"
   }
 }
#dispose of SPWeb variable
if ($webDestination)
 {
  $webDestination.Dispose()
 }
}
Update-SPList

You can accomplish Point 3 from my requirements with a Windows Task Scheduler and run this script how often you want to keep your External and Native SharePoint lists in sync.

Once in your Native SharePoint list, you may add metadata and other columns, version control, alerts etc. you know what you can do.

You will soon start to notice advantages of this approach as thanks to it, you can work on external data in a much more flexible way.
Enjoy!

Content Query Web Part stopped working

One Monday we have experienced a strange issue with our Content Query Web Part. It was configured to list the files checked out to a logged in user across the whole site collection.
[See this guide on how to accomplish this]
This solution worked for ages for us, but one day- it stopped! What I mean by "it stopped" is- the admin user could still see their results (files checked out to admin) in the web part.
An ordinary user (reader/editor/contributor) had nothing listed in the web part (no results, no error messages, nothing!, even when they had files checked out to them).

I have seen the following threads on the support forum while doing the research:

Content Query WP not showing columns for some users
Some sharepoint actions stopped working
CQWP stops showing results, until it is edited
CQWP - Not returning results after extensive research

but none of them were really applicable.

I have stumbled upon this blog post and learned about LTV's (List Value Threshold).
It is configured under CA/Application Management/Manage Web application.
You click your relevant web application, go to General Settings and click Resource Throttling.

As you can see, the default limit of items that the single database operation can involve at one time is 5000. Since we are querying against the whole site collection, on that Monday we must have exceeded that limit so the web part no longer worked for ordinary users.

As you can see from the screenshot above, the limit for admin users is higher. That explains why the web part worked for admin users.

A few options:

1. Increase the limit (not recommended due to SQL locks and loss of performance) so your web part can query and pick up items.

2. Archive old files so we will remain below the default limit of items.

3. Change the solution, utilise Search web part or something else.

SharePoint Approval Workflow vs Content Approval- how to get it right- Part 1 of 3

This is the First part in the series in which I investigate the behaviour of a SharePoint Server 2013 Content Approval and OOTB Approval Workflow (using SharePoint Workflow 2010). In this part I perform a set of tests under given configurations to capture the results on a document library. Some of them will be quite...-interesting, to say the least.

Part 2 will take you through the requirements for my library and a possible solution to overcome the limitations we have identified in Part 1. It will also break down a standard, OOTB SharePoint Approval Process in SP Designer 2013 with some nice graphics for the ease of reading.

Finally, the Third part will showcase how I modified a standard, OOTB SharePoint Approval Process and wrapped it in my own, custom workflow to accomplish the requirements from Part 2. You will see screenshots of the final testing of my solution with associations to the logic I have implemented. If you think that everything went according to plan, you are totally wrong! There were little issues that cropped up unexpectedly but gladly I was able to resolve them.

With the completion of Part 3, you should be able to create a fully working solution to manage approval of content in your library, whatever the configuration you use. On me! 

Ok, let's start!

It looks as though, according to MS, Approval Workflow and Content Approval meant to work together, but in reality they run- more or less- as two independent processes.

In some scenarios, our approval workflow stated: ‘Approved’ whilst content approval column still said ‘Pending’. That also created a set of content visibility issues and some confusing- not to say ‘wrong’ information about the files.

Let’s get down to some testing to see what we actually get when working with SharePoint Content Approval and SharePoint OOTB Approval Workflow with a set of given scenarios.

Please note that I have probably not covered every possible configuration here, but surely you get a good view of your possibilities.

Before you will see the tables with the results for certain configurations, please note these 3 conclusions to which I have come quite early during my work:

1. 

‘CHECK OUT’ REQUIRED ON A LIBRARY PREVENTS AN APPROVAL WORKFLOW FROM UPDATING ITS STATUS COLUMN. THAT RESULTS IN APPROVAL WORKFLOW STATUS NOT BEING ABLE TO CORRESPOND WITH THE CONTENT APPROVAL STATUS.

2.

MODIFYING APPROVAL WORKFLOW IN SP DESIGNER TO ‘CHECK OUT’ THE FILE WILL ALWAYS CREATE A NEW VERSION EDITED BY ‘System Account’ (NOT ACCEPTABLE?).

3.

IF APPROVAL WORKFLOW IS ASSOCIATED WITH A CONTENT TYPE, THERE IS NO OPTION TO ‘START THIS WORKFLOW TO APPROVE A MAJOR VERSION OF A DOCUMENT’. THIS IS ONLY AVAILABLE WITH LISTS/LIBRARIES' WORKFLOWS.

Do I have to mention that MS will never tell you that? You would have to find this out by experiencing problems, then finding blogs like this one and learning about such configuration glitches yourself.

_____________________________________________________________________________________________

TEST 1

On file change

Document Library  Versioning?                    Major & Minor  See Drafts?                    Approver & Author   Check Out?                      Required  Content Approval?      Required  Approval Workflow?  On Content Type (New,                                                 Change, Update Status)  File status:                 1.0 Approved
		Requester	Approver	Reader
Saving file 1.0  as Draft (1.1)- waiting	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	In Progress
	Approval Status	Draft	Draft	Approved
Approved	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	Approved
	Approval Status	Draft	Draft	Approved
Rejected	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	Rejected
	Approval Status	Draft	Draft	Approved

Document Library  Versioning?                   Major & Minor  See Drafts?                    Approver & Author   Check Out?                     Required  Content Approval?      Required  Approval Workflow?  On Content Type (New,                                                 Change, Update Status)  File status:                 1.1 Draft (1.0 for Reader)
		Requester	Approver	Reader
Saving file 1.1 as Major (2.0)- waiting	Version	1.2	1.2	1.0
	Internal Workflow	Approved	Approved	Approved
	Approval Status	Pending	Pending	Approved
Approved	Version	1.2	1.2	1.0
	Internal Workflow	Approved	Approved	Approved
	Approval Status	Pending	Pending	Approved
Rejected	Version	1.2	1.2	1.0
	Internal Workflow	Approved	Approved	Approved
	Approval Status	Pending	Pending	Approved

Document Library  Versioning?                   Major & Minor  See Drafts?                    Approver & Author   Check Out?                     Required  Content Approval?      Required  Approval Workflow?  On Content Type (New,                                                 Change, Update Status)  File status:                 1.0 Approved
		Requester	Approver	Reader
Saving file 1.0  as Major (2.0)- waiting	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	In Progress
	Approval Status	Pending	Pending	Approved
Approved	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	Approved
	Approval Status	Pending	Pending	Approved
Rejected	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	Rejected
	Approval Status	Pending	Pending	Approved

In the above examples, whenever the ‘Approval Status’ column ends up with ‘Pending’, a user with approving permissions has to manually click ‘Approve’ button for that file in the browser using a ribbon or eclipse menu. 

That initiates ANOTHER instance of the Internal Workflow (second set of e-mails sent and tasks created) which will only update its own column, not the ‘Approval Status’ one.

Although the file might say ‘Rejected’, its version  gets elevated to major hence it becomes visible to all Readers!

_____________________________________________________________________________________________

TEST 2

On file change

Document Library  Versioning?                    Major & Minor  See Drafts?                    Editors   Check Out?                      Required  Content Approval?      NOT Required  Approval Workflow?  On Content Type (New,                                                 Change, Update Status)  File status:                 1.0 Approved
		Requester	Approver	Reader
Saving file 1.0  as Draft (1.1) - waiting	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	In Progress
Approved	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	Approved
Rejected	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	Rejected

Publishing to Draft updates the ‘Internal Workflow’ column status only for the Readers. When the file is e.g. Rejected by the Approver, the readers see the last, approved version they are entitled to see, but with an internal approval column saying ‘Rejected’. (It is totally confusing and not correct!). We need to have at least one column indicating a file's approval status hence we cannot hide that column. 

Document Library  Versioning?                    Major & Minor  See Drafts?                    Editors   Check Out?                      Required  Content Approval?      NOT Required  Approval Workflow?  On Content Type (New,                                                 Change, Update Status)  File status:                 1.1 Approved (1.0 for                                                       Readers)
		Requester	Approver	Reader
Saving file 1.1  as Major (2.0)- waiting	Version	2.0	2.0	2.0
	Internal Workflow	Approved	Approved	Approved
Approved	Version	2.0	2.0	2.0
	Internal Workflow	Approved	Approved	Approved
Rejected	Version	2.0	2.0	2.0
	Internal Workflow	Approved	Approved	Approved

Document Library  Versioning?                     Major & Minor  See Drafts?                     Editors   Check Out?                       Required  Content Approval?       NOT Required  Approval Workflow?   On Content Type (New,                                                  Change, Update Status)  File status:                  1.0 Approved
		Requester	Approver	Reader
Saving file 1.0  as Major (2.0)- waiting	Version	2.0	2.0	2.0
	Internal Workflow	Approved	Approved	Approved
Approved	Version	2.0	2.0	2.0
	Internal Workflow	Approved	Approved	Approved
Rejected	Version	2.0	2.0	2.0
	Internal Workflow	Approved	Approved	Approved

With publishing to major version, whatever happens to the file (in progress/approved/rejected) - the version gets published anyway and it is visible to all Readers.

_________________________________________________________________________________

TEST 3

On file change

Document Library  Versioning?                   Major & Minor  See Drafts?                    Editors   Check Out?                     NOT Required  Content Approval?      NOT Required  Approval Workflow?  On Content Type (New,                                                 Change, Update Status)  File status:                 1.0 Approved
		Requester	Approver	Reader
Saving file 1.0 - waiting	Version	1.1	1.1	1.0
	Internal Workflow	In Progress	In Progress	Approved
Approved	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	Approved
Rejected	Version	1.1	1.1	1.0
	Internal Workflow	Rejected	Rejected	Approved

Document Library  Versioning?                    Major & Minor  See Drafts?                    Editors   Check Out?                      NOT Required  Content Approval?       NOT Required  Approval Workflow?   On Content Type (New,                                                  Change, Update Status)  File status:                  1.1 Approved (1.0 for                                                      Readers)
		Requester	Approver	Reader
Saving file 1.1 as Major (2.0) - waiting	Version	2.0	2.0	2.0
	Internal Workflow	In Progress	In Progress	In Progress
Approved	Version	2.0	2.0	2.0
	Internal Workflow	Approved	Approved	Approved
Rejected	Version	2.0	2.0	2.0
	Internal Workflow	Rejected	Rejected	Rejected

When user opens a document, they need to click ‘Enable Editing’ as opposed to ‘Check Out’. (Check Out feature switched off so button not there).

While the file is opened, every click on ‘Save’ sends an approval e-mail to the Approver. 

Every most recent e-mail becomes an active one whilst the former ones no longer have active tasks.

In this case we need to tell users not to save the file as they go along, but save it at the end of their work!

Also, when 'Check Out' is switched off, users cannot specify their version while saving (See TEST 5).

_________________________________________________________________________________

TEST 4

On file change

Document Library  Versioning?                    Major only  See Drafts?                    Editors   Check Out?                      NOT Required  Content Approval?       NOT Required  Approval Workflow?   On Content Type (New,                                                  Change, Update Status)  File status:                  1.0 Approved
		Requester	Approver	Reader
Saving file 1.0 as Major (2.0) - waiting	Version	2.0	2.0	2.0
	Internal Workflow	In Progress	In Progress	In Progress
Approved	Version	2.0	2.0	2.0
	Internal Workflow	Approved	Approved	Approved
Rejected	Version	2.0	2.0	2.0
	Internal Workflow	Rejected	Rejected	Rejected

Even 'Rejected' versions are visible to Readers. The Approval Workflow does not control visibility. Content Approval does!

_________________________________________________________________________________

TEST 5

On file change

Document Library  Versioning?                    Major & Minor  See Drafts?                    Approver & Author   Check Out?                      NOT Required  Content Approval?       Required  Approval Workflow?   On Content Type (New,                                                  Change, Update Status)  File status:                  1.0 Approved
		Requester	Approver	Reader
Saving file 1.0 - waiting	Version	1.1	1.1	1.0
	Internal Workflow	In Progress	In Progress	Approved
	Approval Status	Draft	Draft	Approved
Approved	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	Approved
	Approval Status	Draft	Draft	Approved
Rejected	Version	1.1	1.1	1.0
	Internal Workflow	Rejected	Rejected	Approved
	Approval Status	Draft	Draft	Approved

As indicated in TEST 3 above, without ‘Check Out’ feature, users will not see the option to specify a version when they save files in Office 2010/13. When they save a file, it is saved as a draft automatically. Users will need to use a browser to 'Publish a Major Version' of the file when it is in ‘Draft’. See below.

Document Library  Versioning?                    Major & Minor  See Drafts?                    Approver & Author   Check Out?                      NOT Required  Content Approval?       Required  Approval Workflow?   On Content Type (New,                                                  Change, Update Status)  File status:                  1.1 Draft (1.0 for                                                                Readers)
		Requester	Approver	Reader
Publishing file 1.1 as 2.0 (Major)  - waiting	Version	1.1	1.1	1.0
	Internal Workflow	In Progress	In Progress	Approved
	Approval Status	Pending	Pending	Approved
Approved	Version	1.1	1.1	1.0
	Internal Workflow	Approved	Approved	Approved
	Approval Status	Pending	Pending	Approved
Rejected	Version	1.1	1.1	1.0
	Internal Workflow	Rejected	Rejected	Approved
	Approval Status	Pending	Pending	Approved

In this case, when you attempt to ‘Publish a Major Version’ of the file (only in browser), Content Approval changes to 'Pending' and Internal Workflow becomes ‘In Progress’. You can either Approve or Reject the document via Internal Workflow, but this will not change Content Approval status in 'Approval Status' column.

You have to- again using a browser- ‘Approve’ that file to initiate ANOTHER instance of an Internal workflow (second set of e-mails sent and tasks created), which will then finally update the version to 2.0 with all statuses: Approved.

This is when I realised that if you happen to 'un-publish' a major version of a file (e.g. 2.0), it goes through the Internal Approval process (as expected) and once approved (un-publishing major version), the file will revert back to draft- 1.1 and will be visible only to Approvers and Authors. What is interesting is that this file then completely disappears from the views for Readers. They cannot even see the last approved and full version they could see before which was 1.0.

This behaviour is by design and it will be present no matter the configuration we use, even when our custom solutions in Part 3 are implemented.

Again, the file can end up with Internal Workflow saying ‘Approved’ and Content Approval stating ‘Rejected’ or vice versa. Even if the file is Draft, all users that cannot see drafts will not see this file or its previous versions at all (not present in the views).

_________________________________________________________________________________

TEST 6

On file change

Document Library  Versioning?                    Major & Minor  See Drafts?                    Approver & Author   Check Out?                      Required  Content Approval?       Required  Approval Workflow?   Not Required  File status:                  1.0 Approved
		Requester	Approver	Reader
Saving file 1.0  as Draft (1.1)- waiting	Version	1.1	1.1	1.0
	Approval Status	Draft	Draft	Approved

Content Approval does not require approving Draft versions hence no Approval/Rejection links are available in the browser. 

There are no e-mails/tasks to approve content since there is no Internal Workflow associated with a library/content type.

Document Library  Versioning?                    Major & Minor  See Drafts?                    Approver & Author   Check Out?                      Required  Content Approval?       Required  Approval Workflow?   Not Required  File statuses:              1.1 Draft (1.0 for                                                               Readers)
		Requester	Approver	Reader
Saving file 1.1  as Major (2.0) - waiting	Version	1.2	1.2	1.0
	Approval Status	Pending	Pending	Approved
Approved	Version	2.0	2.0	2.0
	Approval Status	Approved	Approved	Approved
Rejected	Version	1.2	1.2	1.0
	Approval Status	Rejected	Rejected	Approved

With a file's rejection, the file reverts back to a Draft version. It appears to Authors and Approvers as Rejected draft while Readers can see its last approved full version.

_____________________________________________________________________________________________

As you can see, the last configuration is closest to being acceptable because:

FOR:

- it works with a library when 'Check Out' is enabled

- Content Approval automatically handles necessity for approving content (only major versions)

- it controls visibility of Draft and Major versions efficiently

- at least one column to indicate a file's approval status present and meaningful

There are however some limitations that need to be addressed:

AGAINST:

- there is no automatic alerting system to notify relevant Approvers about their task and notify Requesters about the progress and outcome of the approval

- anybody from the Approvers group can in theory access approval/rejection links in the browser while the file is 'Pending'

In Part 2 of the series I will identify the list of specific requirements and a solution that I have decided to implement. 

Part 2 consists of a breakdown of an OOTB Approval Process in SharePoint which I will modify to my needs. I get into the nitty-gritty of its steps and overall logic as well as point where I inject my logic to it.

Thanks!