function readOnly(count){ }
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
+ Start a Discussion
Eric BowdenEric Bowden 

Cannot retrieve metadata via Files Connect: SharePoint Online

I have successfully configured Files Connect for SharePoint Online many times, and it works well for browsing and sharing files via Chatter or in the Files tab.  Next, I'd like to configure the External Object created by my Files Connect data source with a Lookup relationship to an opportunity.  I'm intending for users to be able to view files which are related to an Opportunity as a related list, on the details page for an Opportunity.  Thus far, I've not been successful.  

Has anyone accomplished this?  It appears that Files Connect: SharePoint Online is ignoring the metadata fields that I added to my document library in SharePoint.

Initially, I performed the following steps:
- Added a text column to my SharePoint document library called OpportunityId.
- Added a Lookup Relationship to the items_Files_Connect external object in Salesforce, I specified the External Column Name as OpportunityId.
- I added a few documents to my document library in SharePoint and set the OpportunityId value equal to the 18 digit id for a test opportunity.
- I viewed my test Opportunity, but the items_Files_Connect related list is empty.

To debug, I performed the following:
- Added a Text column to my external list called OpportunityId and set the External Column Name as OpportunityId.
- Created a tab for the external object items_Files_Connect and navigated to that tab.
- Modified the view for items_Files_Connect to include the new OpportunityId text column.
- I found that the OpportunityId text column is always empty. 

My debug steps lead me to think that Files Connect is not reading additional fields from SharePoint and thus that is the reason that my initial attempt to create a lookup relationship did not succeed.

I saw in the release notes (http://releasenotes.docs.salesforce.com/en-us/summer15/release-notes/rn_chatter_files_connect_lookup.htm) that Lookup Relationships should be working, but "Files Connect: SharePoint Online" wasn't called out specificially.

Appreciate any tips or confirmation if others have been able to retrieve metadata when using "Files Connect: SharePoint Online" or if you've been able to establish a lookup relationship where the 18 digit object id was stored as a field in SharePoint.
Best Answer chosen by Eric Bowden
Eric BowdenEric Bowden
I got it working, with a little help from runscope.  (aside:  runscope is extremely useful for debugging integration projects such as Salesforce to Office 365).  Reading the API calls that are executed into SharePoint by Files Connect helped lead me to the solution.

The short answer:  Additional field data that you want to retrieve using Files Connect must be added as a managed property in SharePoint because files connect uses search queries to return the listing of documents and metadata from SharePoint.  Creating managed properties is described in the context of Search, SOSL, and SQL queries at this link (https://help.salesforce.com/HTViewHelpDoc?id=admin_files_connect_sp_custom_props.htm&language=en_US).  There are some limitations and requirements to be aware of, mentioned in the article.

To add a field as managed property in SharePoint:
  1. Create a site column in SharePoint (Site Settings -> Site Columns) for the data you want to store.
  2. Add the site column to your document library (Library Settings -> Add from existing site columns).
  3. Navigate to SharePoint Online Administration and create a managed property for the site column.  Be sure to pay attention to the retrievable and queryable options, mentioned in the article above.  Note:  Site columns which contain data are automatically promoted to managed properties as described here (https://technet.microsoft.com/en-us/library/JJ613136.aspx).  There are some limitations.  I created mine manually instead of waiting for it to be automatically created.
  4. Add fields to the external object (described in my original post above), if you haven't done so already.
  5. Wait for the search crawl to complete.  I usually see searches complete within 10-20 minutes, though this, albeit old, post suggests 15 minutes and as long as 6 hours (http://www.sharepointnutsandbolts.com/2013/10/waiting-for-search-crawl-in-office-365.html).  You can create a custom search center in SharePoint, use a search debug tool such as (https://sp2013searchtool.codeplex.com/), or use runscope to confirm the results.
Now that the managed property is in place and has been crawled, you can configure the external object for files connect to retrieve additional metadata values or to use a Lookup relationship.

All Answers

Eric BowdenEric Bowden
I got it working, with a little help from runscope.  (aside:  runscope is extremely useful for debugging integration projects such as Salesforce to Office 365).  Reading the API calls that are executed into SharePoint by Files Connect helped lead me to the solution.

The short answer:  Additional field data that you want to retrieve using Files Connect must be added as a managed property in SharePoint because files connect uses search queries to return the listing of documents and metadata from SharePoint.  Creating managed properties is described in the context of Search, SOSL, and SQL queries at this link (https://help.salesforce.com/HTViewHelpDoc?id=admin_files_connect_sp_custom_props.htm&language=en_US).  There are some limitations and requirements to be aware of, mentioned in the article.

To add a field as managed property in SharePoint:
  1. Create a site column in SharePoint (Site Settings -> Site Columns) for the data you want to store.
  2. Add the site column to your document library (Library Settings -> Add from existing site columns).
  3. Navigate to SharePoint Online Administration and create a managed property for the site column.  Be sure to pay attention to the retrievable and queryable options, mentioned in the article above.  Note:  Site columns which contain data are automatically promoted to managed properties as described here (https://technet.microsoft.com/en-us/library/JJ613136.aspx).  There are some limitations.  I created mine manually instead of waiting for it to be automatically created.
  4. Add fields to the external object (described in my original post above), if you haven't done so already.
  5. Wait for the search crawl to complete.  I usually see searches complete within 10-20 minutes, though this, albeit old, post suggests 15 minutes and as long as 6 hours (http://www.sharepointnutsandbolts.com/2013/10/waiting-for-search-crawl-in-office-365.html).  You can create a custom search center in SharePoint, use a search debug tool such as (https://sp2013searchtool.codeplex.com/), or use runscope to confirm the results.
Now that the managed property is in place and has been crawled, you can configure the external object for files connect to retrieve additional metadata values or to use a Lookup relationship.
This was selected as the best answer
Grzegorz ŁukasikGrzegorz Łukasik
Hello Eric,
We are facing similar issue where some metadata of images and videos are not correctly retrieved when querying external object. This includes numeric and url properties and one single choice property, although few other single choice properties are returned correctly (all properties are managed properties).
SPO Search tool returns correct metadata all the time.
We setup text type managed properties for URL and numeric types but this didn't helped.
Have you met similar issues by chance or know a workaround?
We connect to O365.