Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I have a document library, Resumes, that has a large number of resumes in it. There is a regular column on that library, called EmployeeID, that is already populated. I have an external content type ( ECT ) that is mapped to a table that lists EmployeeID and several other columns of information. I added this ECT to the library as an External Column.

I am looking for a good way to automatically go through all items in the document library, and copy the value from the "regular" EmployeeID column into the External EmployeeID column and have it update the other columns from the ECT. Any techniques are useful (PowerShell, Object Model, Workflow?) When we have tried this in the past with workflows the setting of the External Column did not report failure, but didn't actually trigger a lookup of the other data and the value didn't seem to actually get saved.

[Update] I have been working on this and am getting closer. I am using PowerShell to access the list and set the external column ID field to the value I want it to be.

The problem is that it doesn't seem to be triggering a lookup of the external data when that field is set.

my "list" (a document library) has a few normal fields and a set of external columns mapped to an ECT "EmplData".

When you use the GUI to add or edit the properties of an item, it works. When I use PowerShell to do the following:

$spList = $spWeb.Lists["Resumes"]
$spListItem = $spList.GetItemById(1)
$spListItem["EmplData_ID"] = "00300"   
$spListItem.Update()

Sometime the external ID field gets updated when I the refresh the web page, other times it says "(blank)". In all cases it does not load in the other fields.

Is there some Update method that updates the rest of the elements? I gather from research that these are called "secondary fields", is the method to update the secondary fields based on the external item ID available in PowerShell cmdlets someplace?

[Update] I am finding that if I use:

$spListItem["EmplData"] = "000300"

instead of $spListItem["EmplData_ID"] = "00300" it gets around the problem of the column showing "(blank)".

I still need to figure how to make it update the other external columns (also known as "secondary columns" I think)

share|improve this question

4 Answers 4

up vote 10 down vote accepted
+50

Thus far, using the object model has been the only way I have been able to successfully do this. I encountered the same issues as you when using PowerShell.

Using Jaspers' post SP2010: Setting BCS column and related fields as my guide, I used 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();
    }
}
share|improve this answer
    
Answer provided by Danny Jessee worked out for me. I have created a Timer job to automate BCS external data synchronisation. One big problem I faced is after the Timer job run completed, the Out of Box external data synchronisation was not working. I just fixed it by commenting the below line from Danny Jessee code. item[dataField.RelatedField] = item["EmployeeID"]; Thanks Danny for the code. –  gowtham Oct 29 '13 at 14:39

Take a look at SharePoint Bdc request library. This group of classes lets to make simple requests for values from external data source, using a BDC Entity Instance Identifier(s) or a value of a certain BDC Entity field. Get required entity instance as a DataTable oject and then set a SPListItem's fields.

share|improve this answer
2  
We require relevant answers and disclosure of any affiliation with products or websites. Please see May I promote products or websites I am affiliated with here? for guidelines. You should at least add a disclaimer since you are a coordinator on this codeplex project. Thanks. –  SPDoctor Apr 19 '12 at 12:20

The Referenced Field value is encoded. You have to use DecodeEntityInstanceId to get it:

http://msdn.microsoft.com/en-us/library/microsoft.sharepoint.businessdata.infrastructure.entityinstanceidencoder_methods

share|improve this answer
1  
Thanks for the input. However, I am not sure how to apply your comment about the referenced field being encoded to my question above. Will the DecodeEntityInstanceId allow me to set those external fields via PowerShell? Can you provide a code modification example to my PowerShell script above to illustrate where I should add the DecodeEntityInstanceId? –  lwbecker2 May 19 '12 at 12:33

I used some of this code to create a web part. My code is available here: http://rrfreeman.blogspot.com/2013/06/bcs-bdc-external-data-lookup-field.html

share|improve this answer
    
Thanks for your answer, but could you paste some of your solution code here, making it much more useful for future visitors? –  Benny Skogberg MCSA Jun 28 '13 at 6:26
    
While this may theoretically answer the question, we prefer inclusion of the essential parts of the answer here, and to provide the link for reference. See answer for general guidelines. –  SPDoctor Jun 28 '13 at 9:40
    
Yes. Sorry, this is validation of Danny's answer above with a link to a packaged solution. –  Robert Freeman Jul 10 '13 at 16:07
    
•Notes ◦I found that in addition to setting the display name you also need to set the RelatedField to the BDCIdentity to get the correct value to display in the edit form and to be updateable via the refresh icon. // Set the BCS field itself (Display Value) listItem[dataField.Id] = dtBDCData.Rows[0][dataField.BdcFieldName].ToString(); // Set the related field to the BDC Identity listItem[dataField.RelatedField] = dtBDCData.Rows[0]["BdcIdentity"].ToString(); –  Robert Freeman Jul 10 '13 at 16:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.