2

This is a real estate development that has a Development object and a Property__c object. Properties can be related to a Development or not. Also, properties are tied to opportunities. Now I need to query properties that are available. That is, properties that are not related to an opportunity or only related to 'Closed Lost' opportunities.

I'm trying to get a list with at least the opportunity stage on it but I'm getting the following error:

ERROR at Row:1:Column:106 Didn't understand relationship 'Opportunities' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.

This is the query: SELECT Development1__c, Name, Asking_Price__c, Meters__c, Property_Type__c, (SELECT Name, StageName FROM Opportunities) FROM ARCO_Property__c WHERE Property_Type__c != 'Renovated Commercial (Retail)'

I have also tried:

 ...(SELECT Name, StageName FROM Opportunity)...

or 

...Opportunity.StageName FROM ARCO_Property__c...

or 

...Opportunities.StageName FROM ARCO_Property__c...

or

...Opportunity__r.StageName FROM ARCO_Property__c...

without any successful result.

Both selects work fine when alone. I get error when trying to 'JOIN' them.

Expanded info

Using Workbench I've noticed that apparently there isn't a relationship in ARCO_Property__c for the field StageName of the Opportunity object:

enter image description here

But the Opportunity object does have a Property__c field...

enter image description here

Finally, answering sfdcfox question, there is a lookup relationship from Opportunity to Property.

enter image description here

Again, what needs to be achieved is to show a list with the apartments for a specific development and availability status for each unit.

Improve this question
5
  • Which two queries work alone? Your question isn't quite clear. Where is the lookup field? Is there a lookup field on the opportunity to the property, or a lookup field on the property to the opportunity? It's simply not clear what the relationship between the two is.
    – sfdcfox ♦
    Commented Mar 16, 2019 at 2:42
  • Both queries work alone. I can either select from ARCO_Property_c object or from the Opportunity object.
    – zJorge
    Commented Mar 16, 2019 at 2:55
  • Okay, but what is the relationship between the two objects?
    – sfdcfox ♦
    Commented Mar 16, 2019 at 2:58
  • Thanks @sfdcfox I will expand the question to include the relationships using workbench as Santanu Boral has shown below.
    – zJorge
    Commented Mar 16, 2019 at 15:28
  • @zJorge check my updated answer based on your updated question.
    – Santanu Boral
    Commented Mar 16, 2019 at 19:26

1 Answer 1

Reset to default
3

As per your updated question, Opportunity has a lookup field to the ARCO_Property__c object and your requirement is to retrieved results based on

Properties that are not related to an opportunity or only related to 'Closed Lost' opportunities.

So, both of the conditions cannot be satisfied through the single query. You need to separately query and prepare a list as follows:

//retrieve the property details which are not related to Opportunity
List<ARCO_Property__c> lstProp = new List<ARCO_Property__c>();
lstProp.addAll([SELECT Id, Name FROM ARCO_Property__c
                WHERE Id NOT IN (SELECT ARCO_Property__c 
                            FROM Opportunity)]);

//retrieve the property details which are related to Closed Lost Opportunity
lstProp.addAll([SELECT Id, Name
                FROM ARCO_Property__c
                WHERE Id IN (SELECT ARCO_Property__c FROM Opportunity 
                            WHERE StageName = 'Closed Lost')]);
Improve this answer
1
  • Thank you. The actual query was something like SELECT Id, Name, Asking_Price__c, Meters__c FROM ARCO_Property__c WHERE Development1__c='a013400000YJpv1AAD' AND Id NOT IN (SELECT Property__c FROM Opportunity WHERE Development__c ='a013400000YJpv1AAD') but your answer definitely pointed in the right direction.
    – zJorge
    Commented Mar 16, 2019 at 23:21

You must log in to answer this question.

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