MyInsights Studio: How to Pull Related Object Data From a Lookup Object?

Overview:

Querying data in MyInsights Studio is similar to building queries in Salesforce Workbench, although MyInsights Studio operates with a GUI-driven interface rather than the SOQL query language of Workbench. This can sometimes mean having to pull data in MyInsights Studio differently from how users may be used to doing with SOQL queries.

For example: In Salesforce Workbench, users can query attributes from Lookup fields by calling the 'relationship' name.

A query to pull Product IDs from the Product Metrics object, and to also pull the Product Name from the Products object for those Product IDs, would use a relationship lookup as follows:

SELECT Products_vod__c, Products_vod__r.Name FROM Product_Metrics_vod__c

The Products_vod__c field on the Product Metrics object is a Lookup field pointing to the IDs of Products in the Product Catalog object (Product_vod). It is possible to call and display the Name of those Products in a SOQL query using the __r relationship link.

However, this lookup/relationship link ability is not automatically available in MyInsights Studio.

How, then, can users pull attributes across objects by Lookup relationship?

Solution:

Pulling data from a Lookup field into a MyInsights Studio visualization requires creating multiple Data Elements and tying them together with a Calculate Fields For List Data Element.

The example in this article builds on the SOQL query example up above. We will pull fields from the Product_Metrics_vod object, including the Product_vod__c field, which is a lookup to the Product_vod object. We will then pull the Name of a Product by reading the Product ID passed from the Products_vod__c field on the Product_Metrics_vod record.

This example was done using the Products Metric and Product Catalog objects, and the Account_Profile_Override_vod visualization type, but the concept of calling Lookup attributes across objects should remain the same regardless of visualization type.

Example - Product Metric Data for Account using Context Query Data Element

The visualization in this example is to display Product Metric data for an Account, and display the Product name from the Product Catalog for each Product Metric row on the Account. So to begin with, we must create a Data Element to know what Account we are looking at - a Context Query data element to pull the Account ID we are looking at in the visualization.

Please note, the Context Query data element, or Get Field Value Based on CRM Context Data Element, can only be used to query certain objects, and the objects allowed for the query are different for different MyInsights Page types. For more information, see the Online Help page on Adding Data for MyInsights Pages.
Depending on the type of MyInsights Page you are building, you may need to query the CRM context, or you may be able to proceed directly to the CRM Query Data Elements as below. Once you have two CRM Queries pulling relevant data, you can use a Calculate Fields For List data element to match the data up, effectively acting like the Related Object Lookup.

CRM Query Data Element #1

Next, our first CRM Query Data Element. Here, we are pulling the data we want to display from the Product Metrics object. There are a number of fields, including the Account the Product Metric entry is for, and the Products lookup field Products_vod__c. This field is filled in with the ID of a Product from the Products Catalog object, not the name of that Product.

A WHERE clause on this Data Element restricts the data pulled to only those rows where the Account is equal to the Account ID from our Context Query data element pulling the currently viewed Account.

CRM Query Data Element #2

Next, we need to create a CRM Query Data Element to pull data from the other Object - the object the Lookup field on the first object points to, and which we want to pull values from. In this example, that is the Product Catalog object. Here, we pull the Record ID (the same ID that is referenced in the Lookup field on the Products Metric object) and the Product Name.

The WHERE clause here pulls rows where the Record ID of the Product is found in Field values from a list of records. The list we point to is the previous Object, and the field we point to is the Lookup field from that object. In this example, that is the Products field from the Product Metric object.

Calculate Fields for List Data Element

Now we have a Context Query element to pull the current Account, a CRM Query to pull data from the primary object we want in the visualization, and a second CRM Query to pull the data we want to pull from a related object via Lookup/relationship.

The Calculate Fields for List Data Element is used to calculate data between elements, but can also be used to match up data between two elements. In this Data Element, pull in all the data from the other two Data Elements that you will want to display together or calculate with.

Salesforce IDs are unique. One ID refers to one specific record. An Account ID refers to one and only one Account, and a Product ID refers to one and only one Product. Some fields on an Object can contain more than one value, but some - like Product Name in this example - only occur once on an individual record. Each Product record in the Product Catalog has only one ID, and one Name. We can use that uniqueness to match the Objects up with a Calculate Fields for List Data Element.

In the example we are working on, we need to pull in data from the Product Metrics object records that we have filtered by Current Account, and then pull in data from the Lookup to the Product Catalog object. Here, we are setting the Data Source as the first of our two CRM Query data elements. The first few fields displayed are fields from that first Object.

After that, use the Pick a value arbitrarily option, pulling from a Related Field. The Related Field object choice must be your second CRM Query data element. Set up a Get/where/matches one of formula to pull the Lookup/relationship value from the second CRM Query data element (here, the Product Name) where the Lookup value (here, the Product ID from the Product Catalog) matches one of a List of individual values.

The List of individual values to pull 'arbitrarily' from should be the Lookup field on the first CRM Query data element. No additional Where clause is needed.

While this formula says it's pulling a value arbitrarily from a list, it's really pulling from a list of only one item. In this data relationship, there is only one Name for each Product ID. Just as there is only one Account Name for an Account ID, or one User name for a User ID. Not all data in CRM is one-to-one of course, but the types of values users might expect to pull by Lookup/relationship query are more likely to be one-to-one.

Page Display

When making the Page display for your visualization, you will want to pull data from this Calculate Fields for List Data Element. All the data you need from the first CRM Query Data Element should be pulled into that Calculate Fields for List element, as well as the Lookup/relationship formula.

You can then choose the Calculate Fields for List data element to display from, showing the combined data from both Objects.

Product Support Portal

Search the Knowledge Base

Welcome to the Veeva Product Support Portal!