2

Can someone tell me how to get the underlying SOQL (or atleast the joins) of a standard report type ?

I have to make changes to the existing "Campaigns - Opportunities report" (standard) and I am not able to understand how the different objects like Campaigns and Opportunities are actually joined (or the SOQL) ?

Can someone help ?

2 Answers 2

2

There is no tool that converts your report in to a convenient SOQL statement, at least not one I'm aware of. As an additional bonus, there are some objects which are not API accessible, meaning you cannot query them or interact with them in any way. These objects are far and few between, but they do exist.

For those you can convert in to SOQL, you simply need to figure out if the relationship is many-to-many or one-to-many. In other words, if you run a report on them, can both objects in the report appear multiple times? For example, if you run a report on Accounts with Contacts, you'll notice that accounts may appear multiple times, but each contact will appear only once. This is a one-to-many relationship. Conversely, if you run a Campaign Member report, you'd notice that Campaigns and Leads/Contacts can appear multiple times in the report; this is a many-to-many relationship.

For the first case, Accounts with Contacts, you simply need to know the connecting field, which happens to be AccountId. Standard relationships have their field end with "Id", and the relationship itself does not. So, you know you can write:

SELECT Name, AccountId, Account.Name FROM Contact

And you'll get the account's name through the Account relationship. If you're examining two objects, and you know it's a one-to-many relationship, you can build this in the Schema Builder, and it will tell you exactly how things are related. For example, here's my developer organization's Accounts and Contacts:

Accounts and Contacts in Schema Builder

As you can see, by selecting Account and Contact in the Schema Builder, it shows some fields and also shows relationship lines: the terminal end with a single line is the "one" in a one-to-many relationship, and the terminal end with a circle and three lines is the "many" in a one-to-many relationship. The many side of a relationship actually connects to the field that represents this relationship. This diagram shows us three relationships: Account to Account, Contact to Account, and Contact to Contact.

So, if you do the same thing for Campaign and Opportunity, you'll get a similar diagram:

Campaigns and Opportunities in Schema Builder

As you can see, we have Campaigns to Campaigns, and Opportunities to Campaigns. The diagram tells us that the CampaignId field on the opportunity is what gives us the Return Over Investment (ROI) reports. You can find the Schema Builder in Setup in the navigation menu.

Edit: To get the field's API name, you can also right click on it and choose "View Field in New Window" to see the API name.

1
  • Completely forgot Schema Builder, great visualization tool
    – Jarvis
    Feb 8, 2016 at 8:07
1

I don't know about underlying SOQL but different relationships are created between tables ( read objects) when you create following components related to a object 1) Lookup field 2) Master Detail field 3) Junction Object

So if I need to understand the relationship between Opportunity or Campaign then I would go to object detail page and find out the master detail or lookup fields to different objects Fields on Opportunity

If you further drilldown on the field you will see the relationship name between two objects. Generally for the standard objects the Foreign Key field is appended with Id field, so the foreign key field on Opportunity is CampaignId Relationship Details

So SOQL to retrieve the Campaign names would be

select campaign.name from opportunity where id = '00628000004gUqA'

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .