Programmatically populate external data type column in SharePoint

If your list has an External Data Type Column and you want to populate is using code. You can do it as below. The external content we create has methods used by SharePoint BCS.

This code is done using PowerShell, but can be very easily re-written using Server Object Model.

#Note: In this scenario, I have written a function, wherein I am passing Opportunity Number and based on the same, I am pulling data from CRM.

In below code, these points are worth noting :-

1. Creating a Scope Object => $scope = new-object Microsoft.SharePoint.SPServiceContextScope $spContext;Because, default scope is not available in SharePoint Powershell.

   2. Setting "_ID" field, this field is used for binding. And is generated       automatically and an encrypted value is filled in it.

$encodedEntity = [Microsoft.SharePoint.BusinessData.Infrastructure.EntityInstanceIdEncoder]::EncodeEntityInstanceId($objArray); $listItem["OpportunityGroup_ID"] = $encodedEntity; 

function SetOpportunityData($listItem, [string]$oppNumber, [string]$siteUrl, [string]$ectNameSpace, [string]$ectName)
{

   
   $farm = [Microsoft.SharePoint.Administration.SPFarm]::Local;  
   $service = $farm.Services | where -FilterScript {$_.GetType() -eq [Microsoft.SharePoint.BusinessData.SharedService.BdcService]}  
   $spContext = Get-SPServiceContext -Site $siteUrl;  
   $catalog = $service.GetDatabaseBackedMetadataCatalog($spContext);  
   #Scope starts - Very Important  
   $scope = new-object Microsoft.SharePoint.SPServiceContextScope $spContext;    
   #$ectName = "OpportunityGroup";  
   $entity = $catalog.GetEntity($ectNameSpace, $ectName);  
   $LobSysteminstance = $entity.GetLobSystem().GetLobSystemInstances()[0].Value;  
   $filters = $entity.GetDefaultFinderFilters();  
   if(-not([string]::IsNullOrEmpty($oppNumber)))  
   {  
    $wcFilter = [Microsoft.SharePoint.BusinessData.Runtime.WildcardFilter]$filters[0];  
    $wcFilter.Value = $oppNumber;  
   }  
   $enumerator = $entity.FindFiltered($filters, $LobSysteminstance);  
   $dataTable = $entity.Catalog.Helper.CreateDataTable($enumerator);  
   #write-host $dataTable.Rows.Count;  
   #Scope Ends  
   $scope.Dispose();  
   if($dataTable.Rows.Count -lt 1) { write-host "(" $oppNumber ") => Not foound in extrnal system!!"; }  
   else   
   {  
     $handleRow = $dataTable.Rows[0];  
     [Microsoft.SharePoint.SPBusinessDataField]$externalDataField = [Microsoft.SharePoint.SPBusinessDataField]($listItem.Fields["Opportunity Data"]);  
     #Set the "external" [Opportunity Data] field value  
     $listItem[$externalDataField.InternalName] = $oppNumber;  
     $listItem["Opportunity Data: Opportunity Number"] = $oppNumber;   
     [System.Object]$objArray = @($oppNumber);  
     $encodedEntity = [Microsoft.SharePoint.BusinessData.Infrastructure.EntityInstanceIdEncoder]::EncodeEntityInstanceId($objArray);  
     $listItem["OpportunityGroup_ID"] = $encodedEntity;  
     $listItem["Opportunity Data: SEO Number"] = $handleRow["xyz_seonumber"];   
   }  
}

Powershell to upload files to SharePoint document library

function UploadFileInLibrary           
{           
    [CmdletBinding()]           
    Param([Parameter(Mandatory=$true,ValueFromPipeline=$true)][string]$webUrl,[Parameter(Mandatory=$true)][string]$ImportLibTitle,[Parameter(Mandatory=$true)][string]$FilePath)               
  
    Start-SPAssignment -Global
    
    #Get handle of PLM CSS web object
    $spSite = Get-SPSite -Identity $webUrl           
    $spWeb = $spSite.OpenWeb()

   try
   {         
     $spWeb.AllowUnsafeUpdates = $true;           

     #Get List handle
     $List = $spWeb.Lists.TryGetList($ImportLibTitle) 
    
     #Get File handle       
     $FileName = $FilePath.Substring($FilePath.LastIndexOf("\")+1)           
     $File= Get-ChildItem $FilePath     
    
     #Read File Stream                           
     $fileStream = ([System.IO.FileInfo] (Get-Item $File.FullName)).OpenRead()
              
     #Add file           
     write-host -NoNewLine -f yellow "Copying file " $File.Name " to " $List.RootFolder.ServerRelativeUrl "..."           
     [Microsoft.SharePoint.SPFile]$spFile = $List.RootFolder.Files.Add($List.RootFolder.Url + "/" + $File.Name, [System.IO.Stream]$fileStream, $true)           
     write-host -f Green "...Success!"
              
     #Close file stream           
     $fileStream.Close()
    
     #Update Item         
     write-host -NoNewLine -f yellow "Added file " $spFile.Name "... to the library "  $ImportLibTitle         
     $spFile.Item.Update() 
    
     #Success         
     write-host -f Green "...Success!"               
   }
   catch
   {
     write-host -f Red "...Failure!"
     $ErrorMessage = $_.Exception.Message
     Write-Host $ErrorMessage -BackgroundColor Red
   } 
   finally
   {
     $spWeb.AllowUnsafeUpdates = $false;
     $spWeb.Dispose()
     $spSite.Dispose()
     Stop-SPAssignment -Global
   }         
}

#Begin Code
cls

asnp "*sh*" #This will add all SnapIns that contain "sh". In most cases, this is just the Microsoft.SharePoint.PowerShell one.

#Read params passed to the powershell
$WebUrl = $args[0] #example: http://*****/sites/YourSite/
$FilePath = $args[1] #example: C:\Users\User\Desktop\FillerFiles\Example.csv
$ImportLibTitle = $args[2] #example: "Title"

#call Import function
UploadFileInLibrary $WebUrl $ImportLibTitle $FilePath

PowerShell to update content type in SharePoint

function LoadSharePointSnapin
{
  $snapin = "Microsoft.SharePoint.PowerShell"

  if ((get-pssnapin $snapin -ea "silentlycontinue") -eq $null)
  {
    Write-Host -ForeGround Green "Loading SharePoint Snap-In $snapin."
    Add-PSSnapin $snapin
  }
}

try
{
  write-host -ForeGround Green "** Start Updating Content Type **"
  LoadSharePointSnapin
  Start-SPAssignment -Global

  $spSiteUrl = read-host "Enter Your Site Collection Url  "
  $spSite =  Get-SPSite -Identity $spSiteUrl
  $rootWeb = $spSite.RootWeb

  $MyListContentTypes = $rootWeb.ContentTypes                                                         
  if ($MyListContentTypes -ne $null)
    {
         $ct = $rootWeb.ContentTypes["Content Type Name"]                                     
         if($ct)                                                           
         {
             $ct.FieldLinks[$ct.Fields["Field1"].Id].Required = $False
      $ct.FieldLinks[$ct.Fields["Field2"].Id].Required = $False
      $ct.FieldLinks[$ct.Fields["Field3"].Id].Required = $False
             $ct.Update($true)
         }     
    }     
  write-host -ForeGround Green "** End **"
  Stop-SPAssignment -Global
}
catch
{
  Write-Host ""

  If ($_.FullyQualifiedErrorId -ne $null)
  {
    Write-Error $_.Exception.Message
  }
}

EXECUTE permission was denied on the object 'proc_putObjectTVP'

Below information helped me a lot.  Worth sharing.

one or more of the following might be the cause:

• The service account to which SharePoint is set does not have sufficient permissions to the database to which it is trying to connect.
• The service account is not set up properly in SharePoint.
• When using least privilege setup of the Farm.

Please perform the steps below and test the issue again:

1. Expand Databases then expand the SharePoint_Config Database.
2. Expand Security -> Roles -> Database Roles
3. Find WSS_Content_Application_Pools role, right click it, and select Properties
4. Click on Securables and click Search
5. Next click Specific objects and click OK
6. Click Object Types and select Stored Procedures. Click OK
7. Add the Stored Procedure 'proc_putObjectTVP' and click OK (if it does not automatically grant it exec permission; you need to click the checkbox on "execute" and save it)

Rearrange fields in New/Edit form of lists

The fields in the New/Edit form of a list are attached to the ContentType.
If you want to rearrange the same. You can do the same in code (Server Object Model/Powershell).

The code is simple but, small mistake you can make :)
Note : Please note the field names should be "Internal Names"


$ct = $list.ContentTypes["Content Type Name"];
if($ct -ne $null)
{
    try
        {
             $fldOrder = "Title", "Field1", "Field2", "Field3";  # Should be Internal Names
     $ct.FieldLinks.Reorder($fldOrder);
     $ct.Update();
}
     catch { write-host "Error: " $_.Exception.Message }
}

Update Solutions in the SharePoint Farm

Often we need to update solutions on the farm.
The below code will automate your work.

[Before You Begin] => Please download file from the below link

Click Link to download

Steps to follow :

1. Keep all the Wsps in the Solutions Folder - see the folder structure in the download.
2. Update the old and new .wsp file name in the Config file - see the folder structure
3. open the SharePoint powershell as an admin
4. cd to the folder
5. run the script .\UpdateSolutions.ps1


- Hope SharePoint farm deployment is quicker next time.

Power shell to provision Search Service Application

#Set Database and Service Names here - set them as desired

$SearchServiceAppPool = "Search_Service_Application_Pool"

$SearchApplicationName = "Pro SharePoint Search Service Application"

$SearchAdminDatabase = "Search_Service_Admin_Database"

$CrawlDatabase = "Crawl_Database"

$SearchPropertyDatabase = "Search_Property_Database"

#Set the search service instance name in a variable and start the service.

$SearchServiceInstance = Get-SPEnterpriseSearchServiceInstance -local

Start-SPEnterpriseSearchServiceInstance -Identity $SearchServiceInstance

#Set a user for the service and create a new application pool for search

$Search_Service_Account = "Administrator"

$ApplicationPool = new-SPServiceApplicationPool -name $SearchServiceAppPool

–account $Search_Service_Account

#Create the search service application

New-SPEnterpriseSearchServiceApplication -Name $SearchApplicationName

–applicationpool $ApplicationPool -databasename $SearchAdminDatabase

#Create the Search Service Application Proxy

$SearchServiceApplication = Get-SPEnterpriseSearchServiceApplication

New-SPEnterpriseSearchServiceApplicationProxy -name Search_Service_App_Proxy

–Uri $SearchServiceApplication.Uri.AbsoluteURI

#Set the Search Service Administration Component

Set-SPenterpriseSearchAdministrationComponent –SearchApplication

$SearchServiceApplication -searchserviceinstance $SearchServiceInstance

#Create a Crawl topology

$CrawlTopology = $searchserviceApplication | New-SPEnterpriseSearchCrawlTopology

#Create a new crawl database and crawl component and then Set the new Crawl Topology Active

$CrawlDatabase = $searchserviceApplication | New-SPEnterpriseSearchCrawlDatabase

–DatabaseName $CrawlDatabase

New-SPEnterpriseSearchCrawlComponent -CrawlTopology $CrawlTopology –CrawlDatabase

$CrawlDatabase -SearchServiceInstance $SearchServiceInstance

$CrawlTopology | Set-SPEnterpriseSearchCrawlTopology –Active

#Create a new query topology

$QueryTopology = $searchServiceApplication | New-SPEnterpriseSearchQueryTopology -Partitions

1

$Partition = Get-SPEnterpriseSearchIndexPartition -querytopology $QueryTopology

New-SPEnterpriseSearchQueryComponent -indexpartition $Partition –QueryTopology

$QueryTopology -searchserviceinstance $SearchServiceInstance

#Create a database for properties and assign it to the query partition

$PropertyDatabase = New-SPEnterpriseSearchPropertyDatabase -searchapplication

$SearchServiceApplication -databasename $SearchPropertyDatabase

$Partition | Set-SPEnterpriseSearchIndexPartition -PropertyDatabase $PropertyDatabase

#Activate the Query Topology

$QueryTopology | Set-SPEnterpriseSearchQueryTopology -Active

SPWorkflowAssociation object lost link to Task List or History List

Recently I ran into a issue, where the workflow association object lost the link to Task List. Due to this the Workflow stopped working and status was "error" in the workflow status.

On further investigation I found that the task list attached to the workflow association had been deleted and re-created. And the workflow association was still referencing the old task list.
In our case the "Workflow Association Form" was hidden for some reason. Hence, we had to debug and fix this using "Power-Shell".

SPList.WorkflowAssociations gives handle of all the associations to a particular list.
The individual workflow association object has TaskListId and HistoryListId, these properties show the GUID of the respective lists.

-------------------------------------------------------------------------
Below script will help update these entries and fix the issue.
-------------------------------------------------------------------------

for($i = 0; $i  -lt  $list.WorkflowAssociations.Count; $i++)
{
   $wfAssocObj = $list.WorkflowAssociations[$i];
   if($wfAssocObj.TaskListId -ne $taskList.Id)
   {
         Write-Host "Association: " $wfAssocObj.Name " is referencing wrong TaskList" -      ForegroundColor Red;
 $wfAssocObj.SetTaskList($taskList);
 $list.UpdateWorkflowAssociation($wfAssocObj);
 Write-Host "Correct TaskList mapped => OK" -ForegroundColor Green;
    }
}

-------------------------------------------------------------------------
*Note: You can modify according to your issue, hope this will help understand.

Update Workflow Task List item from c# and Power-Shell

What if you need to update the task list item from code and not a step in the workflow ?
Will you simple write item.Update() ? - No
This will lock the task item and start giving error to users assigned to this task.

So, how to do it ? - Well SharePoint has given a special static class and it's methods to do this.
SPWorkflowTask.AlterTask  - click the link to read in details. 

A. Below C# code will use this function.

 //fields in the task list item to be updated
Hashtable data = new Hashtable();
data["Field1"] = "Value1";
data["Field2"] = "Value2";
data["Field3"] = "Value3";
 //add more above - if needed!!
SPWorkflowTask.AlterTask(itemTask, data, true);

#Note:   

1. Here, Field1, Field2 and Field3 are internal name of fields in the TaskList Item!! 

2. Value1, Value2 and Value3 are assumed to be string, please keep correct data type for your fields.

3. itemTask -> is the related task list item. You have to write logic to get this. In my case i get it with the help of URL.

 B. Same Code in Power-Shell

The  above task can also be done in Power-Shell. Only two points have to taken care. 

1. How to use Hash Table in  Power-Shell - see sample code below

2. How to  write SPWorkflowTask.AlterTask in Power-Shell - sample code below

    #Note: This is very simple. For calling any static method in Power-Shell. 

                We write like this -> [ClassName]::FunctionName();

# Declare the hash table

$hashTableOfData = @{};

# Prepare data to be updated

$hashTableOfData.Add("Field1", "Value1"); 

$hashTableOfData.Add("Field2", "Value2");
$hashTableOfData.Add("Field3", "Value3");
                          

# Update - using the static method

[Microsoft.SharePoint.Workflow.SPWorkflowTask]::AlterTask($itemTask, $hashTableOfData, $true);