+ Start a Discussion
Sailor67Sailor67 

Pls. help - need to modify SOQL query with Asset and Contact

I need to modify a current API-query below to also include: Contact.Email and Asset.Description.

 

SELECT Account.Id, Account.Name, Description, Amount,

  (SELECT PriceBookEntry.Product2.Name, Id, Quantity, FROM OpportunityLineItems)

FROM Opportunity

 

 

How do I do this?

 

Thanks in advance

/Lars

Best Answer chosen by Admin (Salesforce Developers) 
John De SantiagoJohn De Santiago

You can add another sub-query to the query to get the contact through the opportunity contact roles. However, you can have multiple-contact roles so you can either add a limit 1 or filter the contact roles by the role or where the contact role is the primary depending on what you are looking for.

 

Example:

SELECT Amount, Description, Account.Id, Account.Name, 

(SELECT Id, Role, Contact.Email FROM OpportunityContactRoles), 

(SELECT Id, PricebookEntry.Product2.Name, Quantity FROM OpportunityLineItems) 

FROM Opportunity 

 

Assets is a different story. You are going to need to use a second query to get the asset list.

 

For example:

 

SELECT Id, (SELECT Description, Name FROM Assets) FROM Account WHERE ID = [Account Id]

 

You will then have a list of all the Assets associated to the Account. 

All Answers

John De SantiagoJohn De Santiago

You can add another sub-query to the query to get the contact through the opportunity contact roles. However, you can have multiple-contact roles so you can either add a limit 1 or filter the contact roles by the role or where the contact role is the primary depending on what you are looking for.

 

Example:

SELECT Amount, Description, Account.Id, Account.Name, 

(SELECT Id, Role, Contact.Email FROM OpportunityContactRoles), 

(SELECT Id, PricebookEntry.Product2.Name, Quantity FROM OpportunityLineItems) 

FROM Opportunity 

 

Assets is a different story. You are going to need to use a second query to get the asset list.

 

For example:

 

SELECT Id, (SELECT Description, Name FROM Assets) FROM Account WHERE ID = [Account Id]

 

You will then have a list of all the Assets associated to the Account. 

This was selected as the best answer
Sailor67Sailor67

Many thanks John!

/Lars