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?
Pulling data from a Lookup field into a MyInsights Studio visualization requires creating multiple Data Elements and tying them together with a One for One 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.
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.
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
One for One Data Element
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.
CRM Help Documentation: Adding Data for MyInsights Pages
Send us your feedback: We are always looking for feedback to help improve our Knowledge Base! Please let us know if this article is helpful or provide feedback on how we can improve your experience by clicking here.