function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
lijin Devlijin Dev 

Please help me add one more entity using SOQL

Hello Team,
I want to add one more field to the below SOQL.  This field  "Name" is coming from a different Entity. Not sure how can I go about this

Field to add: Name   , Table: TBC_Verticals__c

SELECT Name,
    (SELECT UnitPrice FROM OpportunitylineItems) ,
    (SELECT Revenue__c FROM Opportunity_Line_Item_facts__r) ,
    FROM opportunity

  
Best Answer chosen by lijin Dev
GauravGargGauravGarg
Because there is no direct relationship between Opportunity and TBC_Verticals. 

They are connected just by the ID's that is a text field (i hope). 

All Answers

GauravGargGauravGarg
Is there any relationships between TBC_Verticals__C and Opportunity? Then, use child relationship name (on child object field) to derive this SOQL like this:
  
SELECT Name,
    (SELECT UnitPrice FROM OpportunitylineItems) ,
    (SELECT Revenue__c FROM Opportunity_Line_Item_facts__r) ,
(SELECT Name FROM TBC_Verticals__c)
    FROM opportunity

Else, you need to find a way to determine connection between the two tables.
  • Then query first table with required filters
  • Store the result in list, and pass the list to another SOQL query to get the required records. 


Hope this is helpfull!!

Thanks,

Gaurav
Skype: Gaurav62990
lijin Devlijin Dev
Thanks, Gaurav. I had tried the same query and seems not working.  Does that mean there is no relation between the Tables?  The second option seems to be challenging as I use the SOQL on a third party reporting tool which extracts data from Salesforce (and report )
GauravGargGauravGarg
you need to check the relationships between object on Object Structure. Please navigate and check if there is any relationship. 
lijin Devlijin Dev
Hey Gaurav , I did check with my Salesforce team and here is the answer to the relation. Can we do something after getting this details?
TBC_verticals__c has a field "ID" which will be connected to  Vertical_ID__C   in Opportunity. 
GauravGargGauravGarg
Yes, that would work for us.

so, here the relationship would go like this:

1. Query TBC_Verticals objects  
List<Id> tbc_Ids = [SELECT Id 
FROM TBC_Vertificals
WHERE "add conditions"];
2. Now query Opportunity filtering based on TBC Id's
  
SELECT Id 
FROM Opportunity 
WHERE Vertifical_IDs__c = :tbc_Ids 
AND "Add Other Conditions"


Hope this helps

Thanks,
Gaurav
Skype: Gaurav62990
lijin Devlijin Dev
Appreciate the help.  Unfortunately, my privilege is just to write one Select statement. Anyways, I got  this quick question ( as I'm new to SOQL)

As it is confirmed that there is a relation between TBC_Vertical and opportunity. why the below query ( the same you suggested didn't work).  In the below query if  I just remove TBC_vertical subquery (select) and run, it works. Not sure what could be the reason/trick here 

SELECT Name,
 (SELECT UnitPrice FROM OpportunitylineItems) ,
(SELECT Revenue__c FROM Opportunity_Line_Item_facts__r) ,
(SELECT Name FROM TBC_Verticals__c)
FROM opportunity
GauravGargGauravGarg
Because there is no direct relationship between Opportunity and TBC_Verticals. 

They are connected just by the ID's that is a text field (i hope). 
This was selected as the best answer