Get the Unique BCS record link and store in the backed for External List

Shalabh Gupta 21 Reputation points
2020-12-07T20:17:28.35+00:00

Hi All,

We have a requirement to send notifications whenever an item in BCS External List is updated based on some SQL Triggers. In that email notification we need to include a link to the BCS External List record which is updated.

Is there a way to identify the unique record {@BdcIdentity} from our BCS List and have it stored in the SQL Server for sending these notifications with links?

The notifications are sent from a powershell script which is called by SQL routine, hence it would be great to know if we can get this {@BdcIdentity} all the way back to the DB which is mapped to the external list.

Any response is appreciated.

Exchange Server Management
Exchange Server Management
Exchange Server: A family of Microsoft client/server messaging and collaboration software.Management: The act or process of organizing, handling, directing or controlling something.
7,326 questions
SharePoint Server Development
SharePoint Server Development
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Development: The process of researching, productizing, and refining new or existing technologies.
1,567 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Echo Du_MSFT 17,111 Reputation points
    2020-12-08T09:56:53.347+00:00

    Hello @Shalabh Gupta ,

    The BDC Identity column resides in the SharePoint External List, not in SQL DB. Thus, its data is NOT transferred to SQL DB like the data in all the other fields.

    You could try to the below script:

    using Microsoft.SharePoint;  
    using System;  
    using System.Web;  
      
    class whatif  
    {  
        static void Main()  
        {  
            String siteUrl = "http://wn10101/StatusBook";  
      
            using (SPSite site = new SPSite(siteUrl))  
            {  
                site.AllowUnsafeUpdates = true;  
                SPSecurity.RunWithElevatedPrivileges(delegate()  
                {  
                    using (SPServiceContextScope scope = new Microsoft.SharePoint.SPServiceContextScope(SPServiceContext.GetContext(site)))  
                    using (SPWeb web = site.OpenWeb())  
                    {  
                        SPList list = web.Lists["Manage Spread Steps"];  
      
                        // query items  
                        SPQuery query = new SPQuery();  
                        query.Query =  
                            @"<Where>  
                        <IsNull>  
                            <FieldRef Name='BDCIdentityKey' />  
                        </IsNull>  
                        </Where>  
                        <OrderBy>  
                            <FieldRef Name='Group'/>  
                        </OrderBy>";  
                        query.ViewFields =  
                            @"<ViewFields>  
                            <FieldRef Name='SpreadKey'/>  
                            <FieldRef Name='Topic'/>                    
                            <FieldRef Name='Group'/>  
                            <FieldRef Name='BdcIdentity'/>  
                            <FieldRef Name='BDCIdentityKey'/>  
                        </ViewFields>";  
      
                        // RowLimit has no effect  
                        query.RowLimit = 1;  
      
                        SPListItemCollection items = list.GetItems(query);  
      
                        foreach (SPListItem item in items)  
                        {  
                            item["BDCIdentityKey"] = item["BdcIdentity"].ToString();  
      
                            // Set all fields.  
                            item.Update();  
      
                            Console.WriteLine("SpreadKey: '{0}', Group: '{1}', BdcIdentity: {2}, BDCIdentityKey: {3}",   
                                item["SpreadKey"],  
                                item["Group"],  
                                item["BdcIdentity"],  
                                item["BDCIdentityKey"]);  
                        }  
                            Console.Write("Press Enter to continue!");  
                            Console.ReadLine();   
                    }  
                });  
            }  
        }  
    }  
    

    Here is a similar case for your reference:

    Thanks,
    Echo Du

    ==================

    If an Answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments No comments

  2. Shalabh Gupta 21 Reputation points
    2020-12-08T14:45:25.63+00:00

    Hi @Echo Du_MSFT

    Thanks for the reply and sharing the c# code to get the unique-key from the backend.

    Since we do not have a custom external list (just created from sharepoint designer), writing and hosting these scripts might not be possible at this moment. Is there a way to read some specific SP-Config database table where these keys are stored? If yes, we can then join those config tables with our BCS DB to achieve what we need.