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"];   
   }  
}