Configuring External Objects

Snowflake Connection

Ensure that package installation and initial Snowflake configuration has been performed.

Enable External Objects

On the "Create External Objects" tab, click the "Enable External Objects". This will create a Salesforce Connect External Data Source and check the Snowflake account for available tables and views.

Table/View Requirements

To access a view from Salesforce, it must have a varchar column named ExternalId (case insensitive, e.g. can also be EXTERNALID). This reflects a Salesforce requirement, and each value in this column must be unique. The column must have a maximum length of 255.

To access a table, it must either have an "ExternalId" column (as described above) or alternatively have a primary key column defined.

Salesforce External Objects Creation

Expand the "Creating External Objects" section and follow the instructions to perform the initial sync process.

Create a Tab to view your data

The simplest way to immediately view data from your new External Objects is to add a Tab for the object:

Add Tab

Select your External Object from the drop-down, choose a tab style, and press Next: Configure Tab

Navigate through the rest of the wizard, leaving the defaults or changing as required.

Now you can select your data from the App Launcher: Select Tab

Don't forget to change the view from Recently Viewed to All, and add more fields using the configuration button: Configure Tab View

You can link your External Objects to other existing Objects in a few different ways which are described in detail here.

Indirect (most common)

To use Indirect, you must first have a Salesforce Object (standard or Custom) with a custom field that contains identifiers matching those in your Snowflake table. For example, if you had a Snowflake table named FINANCIAL_TRANSACTIONS:

TRANSACTION_ID (text, PK) MERCHANT_ID (number(10,0)) TRANSACTION_DATETIME (datetime) TRANSACTION_AMOUNT (number(10,2))
6b8a942c 123 2017-04-09 17:05:03.000 45.67
3c9a9e4d 123 2017-04-09 17:05:08.000 100.00
cec0b3c7 123 2017-04-09 17:05:12.000 21.00
42e8d696 123 2017-04-09 17:05:30.000 50.45

You could link to a Merchant ID field on the Account Object, provided it had a compatible data type, and had the "unique" and "external" flags set: Configure Tab View

Note: The custom field should also be marked as case sensitive, to ensure its values match with those in Snowflake.

To do this, under Setup, click on the name of the External Object, and Edit the field you wish to link with: Configure Tab View Change the field type: Configure Tab View to Indirect Lookup Relationship: Configure Tab View select the object: Configure Tab View and the field: Configure Tab View then follow the rest of the wizard, accepting defaults or changing as desired.

Now that the objects are linked in this way, the external objects should automatically appear on the 'Related' tab of the parent object ('Account' in this example), provided there are matching records.

Lookup

If your Snowflake data contains 18-character Salesforce identifiers (these look like '0010o00002BmgIdAAJ'), you can use a "Lookup" relationship using a similar process to Indirect.

External Lookup

This is similar to Indirect Lookup, except that the External object is the parent in the relationship (i.e. for each record in the Snowflake table, you have zero to many Salesforce objects).

Use in a Report

You can use External Objects in reports. If the external object is not visible under "Other Reports", raise a support ticket with Salesforce, advising that the external object is not visible for report selection.

If you have created lookup relationships and wish to include both parent and child in the report, you must create a new Report Type under Setup which describes the parent-child relationship.

Row Level Filtering

Using the "Row Level Filtering" setting in the Snowflake connection parameters, it is possible to filter out rows based on matching fields from the Salesforce user object, which apply to all queries regardless of any filtering performed within Salesforce.

This setting is not currently available in the Omnata setup app, instead it can be added by:

  1. Under Salesforce Setup, navigate to Custom Metadata Types
  2. Next to "Snowflake Connection", click "Manage Records"
  3. Edit the "Snowflake" connection
  4. Add Row Level Filtering configuration:

Row Level Filtering

The field can be configured with a number of filters, each seperated by a new line.

Each line is of the form: (snowflake table name).(column name)=$CurrentUser.(field name)

For example, the line:

FINANCIAL_TRANSACTIONS.CUSTOMER_EMAIL=$CurrentUser.EmailAddress

means that any row from FINANCIAL_TRANSACTIONS where the CUSTOMER_EMAIL column does not match the Salesforce user's email address will not be included in any query results.