set BCS field using powershell

Question

I have an external data field. I have to copy the value from list to another list. How i can set the value in the other list?

$web = Get-SPWeb http://intranet/CAC/RestrictedAreaManco/
$listProspect = $web.Lists["Prospects"]


$Fund = Get-SPWeb http://manco-intranet/sites/clients
$listFunds = $Fund.Lists["New Fund"]

foreach($item in $listProspect.Items | Where {$_["Approved"] -eq "string;#Approved"}) 
#Approved is a calculated Column so it's input have the value string;#..
{
  $newItem["Investment Compliance Provider"] = $item["Investment Compliance"]
     $item["Investment Compliance"]

  $newItem.Update()

}

Answer 1

It seems there's some issue with powershell. You could refer below SSOM code to copy external data.

The following code to iterate through all the items in my Resumes document library, read the value from the "regular" EmployeeID field for each item, set the "external" EmplData_ID value, then set all the "secondary field" values based on this value.

    using (SPSite site = new SPSite(SPContext.Current.Site.ID))
{
    using (SPWeb web = site.OpenWeb(SPContext.Current.Web.ID))
    {
        web.AllowUnsafeUpdates = true;
        SPList resumesList = web.Lists["Resumes"];

        foreach (SPListItem item in resumesList.Items)
        {
            SPBusinessDataField dataField = item.Fields["EmplData_ID"] as SPBusinessDataField;
            // Set the "external" Employee ID field value
            item[dataField.RelatedField] = item["EmployeeID"];
            // Now set all the secondary field values
            SetSecondaryFields(item, dataField, GetEntityInstance("http://sp2010srv", "Employee", int.Parse(item["EmployeeID"].ToString())));
            item.UpdateOverwriteVersion();
        }
    }
}

Here is the code for the two helper methods GetEntityInstance and SetSecondaryFields:

    private static IEntityInstance GetEntityInstance(string nameSpace, string entityName, int entityId)
{
    BdcService service = SPFarm.Local.Services.GetValue<BdcService>();
    IMetadataCatalog catalog = service.GetDatabaseBackedMetadataCatalog(SPServiceContext.Current);
    IEntity entity = catalog.GetEntity(nameSpace, entityName);
    ILobSystemInstance LobSysteminstance = entity.GetLobSystem().GetLobSystemInstances()[0].Value;
    IEntityInstance entInstance = null;

    // Get methods collection 
    foreach (KeyValuePair<string, IMethod> method in entity.GetMethods())
    {
        // Get current method's instance 
        IMethodInstance methodInstance = method.Value.GetMethodInstances()[method.Key];
        // Execute specific finder method. 
        if (methodInstance.MethodInstanceType == MethodInstanceType.SpecificFinder)
        {
            // Create new identity 
            Identity id = new Identity(entityId);
            // Execute specific finder method and get the entity instance 
            entInstance = entity.FindSpecific(id, entity.GetLobSystem().GetLobSystemInstances()[0].Value);
        }
    } 

    return entInstance;
}

private static void SetSecondaryFields(SPListItem listItem, SPBusinessDataField dataField, IEntityInstance entityInstance)
{
    // Convert the entity to a formatted datatable
    DataTable dtBDCData = entityInstance.EntityAsFormattedDataTable;

    // Set the BCS field itself (Display Value)
    listItem[dataField.Id] = dtBDCData.Rows[0][dataField.BdcFieldName].ToString();

    // Get the specific finder method to get the columns that returns
    IMethodInstance method = entityInstance.Entity.GetMethodInstances(MethodInstanceType.SpecificFinder)[0].Value;
    ITypeDescriptorCollection oDescriptors = method.GetReturnTypeDescriptor().GetChildTypeDescriptors()[0].GetChildTypeDescriptors();

    // Set the column names to the correct values
    foreach (ITypeDescriptor oType in oDescriptors)
    {
        if (oType.ContainsLocalizedDisplayName())
        {
            if (dtBDCData.Columns.Contains(oType.Name))
            {
                dtBDCData.Columns[oType.Name].ColumnName = oType.GetLocalizedDisplayName();
            }
        }
    }

    // get the secondary field display names; these should be set
    string[] sSecondaryFieldsDisplayNames = dataField.GetSecondaryFieldsNames();

    // loop through the fields and set each column to its value
    foreach (string columnNameint in sSecondaryFieldsDisplayNames)
    {
        Guid gFieldID = listItem.Fields[String.Format("{0}: {1}", dataField.Title, columnNameint)].Id;
        listItem[gFieldID] = dtBDCData.Rows[0][columnNameint].ToString();
    }
}

Refer: Automate connectivity of external data in BCS