Salesforce Docs Portal

Include SharePoint Custom Properties in Search, SOQL, and SOSL Queries

Include SharePoint Custom Properties in Search, SOQL, and SOSL Queries

After you create an external object for a SharePoint data source, some special steps are needed to search or query any custom properties it contains.

Required Editions

Available in: both Salesforce Classic and Lightning Experience
Files Connect for cloud-based external data sources is available in: Professional, Enterprise, Performance, Unlimited, and Developer Editions
User Permissions Needed
To create an external object and provide access to its fields: Customize Application

Configuring Custom Properties in SharePoint

If you have the existing mapped custom columns and Office documents' (Microsoft Word, Excel, etc.) metadata in SharePoint, external objects in Salesforce let you select and filter on the following custom properties:

  • Custom columns defined in a Custom Content Type
  • Metadata from Microsoft Word, Excel, and other Office documents

To search on these custom properties in Salesforce, a Sharepoint administrator must create a corresponding Managed Property.

  • To display these properties in external object fields, or use them in SOQL or SOSL SELECT queries, set the corresponding Managed Property to Retrievable.
  • To filter on these properties in external objects, or use them as query criteria in a SOQL or SOSL WHERE clause, set the corresponding Managed Property to Queryable.

Querying Resulting Fields in Salesforce for SharePoint Online

In the following example, CustomProperty stands for the Custom column name defined in the Custom Content Type, or the Office document metadata name. ManagedCustomProperty stands for the corresponding Managed Property name.

In most cases, you can use ManagedCustomProperty for both the SELECT and WHERE clause. Here’s a SOQL example: 

SELECT ManagedCustomProperty FROM items_spOnline_x WHERE ManagedCustomProperty=...

However, for file types that SharePoint doesn’t index for search, such as .jpg, .png, and .pdf files, you must use CustomProperty in the SELECT clause and ManagedCustomProperty in the WHERE clause. As a workaround, you can define an alias on the Managed Property in SharePoint and format queries like this:

SELECT Alias FROM items_spOnline WHERE Alias=...

Normally, custom properties aren’t displayed on external object detail pages—defining an alias also addresses this issue.

Boolean Custom Properties

When using boolean (Yes/No) custom properties with a corresponding Managed Property set to the Text type, the corresponding external object field must be set to the Text type as well. In filters, the values “0” and ”1” equal false and true, respectively. For example:

WHERE customBooleanWithTextManagedProperty=”1”

“0” and ”1” are also displayed as results, however, so boolean custom properties should use a Managed Property type of YesNo. Set the corresponding external object field to Checkbox to query it with standard true or false values. For example:

WHERE customBooleanWithYesNoManagedProperty=true

Metadata in Microsoft Office Documents

The Sharepoint Search service returns all metadata from Office documents as strings, so corresponding external object fields must be set to the Text type.

Dates are returned in the format, MM/DD/YYYY hh:mm:ss AM/PM, (for example, “3/31/2015 9:59:00 PM”). To query on fields using the Date type, the external object must include a corresponding Date field for filtering, and a Text field for selecting.

Limitations for Specific SharePoint Property Types in Salesforce for SharePoint Online

  • Number, Currency, and Choice multiple properties can’t be selected for file types SharePoint doesn’t index for search. By default, this includes image, video, and .pdf files, although .pdf files are natively indexed starting with SharePoint 2013.
  • Multiple Line of Text, URL, and Date properties aren’t queryable.
  • Choice multiple and URL properties can’t be selected.

See Also