+ Start a Discussion
Siddharth LakhotiaSiddharth Lakhotia 

Need Help in SOQL Query

Hi,

this query is showing an error : 

for(Project__c objProject : [SELECT Id,Name,(SELECT Id,Estimated_Value__c, Total_Actual_Sales__c 
                                            FROM Opportunities) FROM Project__c WHERE Id IN: set_Opportunity])

Opportunity is a child object to Project , and its a lookup relationship.

I am using this query in a trigger..  but getting below error

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. at line 14 column 33
Sujeet PatelSujeet Patel
Hii Siddharth 
I think you should use Opportunity instead of Opportunities
Siddharth LakhotiaSiddharth Lakhotia
Still the same error
S_RathS_Rath
Hi,

Please check that the lookup field is created properly if yes then check for other fields in the query too. Instead of checking it in trigger run the query in the query editor you will be able to find the problem. I think opportunities will work in this query.

Hope, this will work !!
Siddharth LakhotiaSiddharth Lakhotia
I tried this query in query editor.. it didn't work..
Khan AnasKhan Anas (Salesforce Developers) 
Hi Siddharth,

Greetings to you!

You have created a custom relationship between Project__c and Opportunity. You need to use Opportunities__r i.e.; ChildRelationshipName__r. Here, Opportunities__r is a relationship name. 

That means if one object is standard (child) and one is the custom object (parent), then you need to use __r for the standard object in sub-query.

Use below query, it will work:
SELECT Id, Name, (SELECT Id, Estimated_Value__c, Total_Actual_Sales__c FROM Opportunities__r) FROM Project__c WHERE Id IN: set_Opportunity


Similarly, let’s say we have a custom object with name Khan__c and standard object Account with name Account which is a parent of Khan. Suppose we want to know the name of the Khan where Account name is Abc.
 
// When record is fetch from Account
// Khans is child relationship name of Khan__c
// __r relationship suffix of custom object
 
SELECT Name, (SELECT Name FROM Khans__r) FROM Account WHERE Name = 'Abc'
 
// When record is fetch from Khan__c
// Account__r is relationship name of custom field Product__c
 
SELECT Name, Account__r.Name FROM Khan__c WHERE Account__r.Name = 'Abc'


I hope it helps you.

Kindly let me know if it helps you and close your query by marking it as solved so that it can help others in the future.

Thanks and Regards,
Khan Anas
Siddharth LakhotiaSiddharth Lakhotia
Same error again
Khan AnasKhan Anas (Salesforce Developers) 
Please share the screenshot of Child Relationship Name. Like this:

User-added image
Siddharth LakhotiaSiddharth Lakhotia
User-added image

Please find the screenshot. Please open screenshot in new tab for better view
Khan AnasKhan Anas (Salesforce Developers) 
Please share the screenshot of Lookup Relationship field which you have created in Opportunity object, as I have shared above, I need Child Relationship Name.
Siddharth LakhotiaSiddharth Lakhotia
Hi Anas,

Thats sorted now.. ! I checked child relationship name..  and its working.. .However I am facing problems with my trigger... Can you help me with respect to that ?

What I am doing is collecting the sum of estimated value and actual sales on opportunity to Project.. Kind of a custom rollup.

However, I am not recieving the summed value on Project. Can you help me on that ?
S_RathS_Rath
Hi,
For creating a roll-up for lookup relationship trigger is the proper approach. I have done something similar on Contact and Account and sharing the code for that. 

trigger ContactTrigger on Contact (after delete, after insert, after undelete, after update) {

    List<Contact> cons = new List<Contact>();
    if (Trigger.isDelete) 
        cons = Trigger.old;
    else
        cons = Trigger.new;

    // get list of accounts
    Set<ID> acctIds = new Set<ID>();
    for (Contact con : cons) {
            acctIds.add(con.AccountId);
    }
    
    Map<ID, Contact> contactsForAccounts = new Map<ID, Contact>([select Id
                                                            ,AccountId
                                                            from Contact
                                                            where AccountId in :acctIds]);

    Map<ID, Account> acctsToUpdate = new Map<ID, Account>([select Id
                                                                 ,Number_of_Contacts__c
                                                                  from Account
                                                                  where Id in :acctIds]);
                                                                 
    for (Account acct : acctsToUpdate.values()) {
        Set<ID> conIds = new Set<ID>();
        for (Contact con : contactsForAccounts.values()) {
            if (con.AccountId == acct.Id)
                conIds.add(con.Id);
        }
        if (acct.Number_of_Contacts__c != conIds.size())
            acct.Number_of_Contacts__c = conIds.size();
    }

    update acctsToUpdate.values();

}

Hope, this will help in your case too.

Thanks

 
Siddharth LakhotiaSiddharth Lakhotia
Hi Sonal,

What I want to do is have a rollup like functionality on two fields. Since its a lookup relationship... Rollup is not possible.

I want sum of all the estimated value on packages(Child Object) to be on Total Estimated Sales on Project(Parent Object)

and sum of all Actual Sales on packages to be on Total Sales on Project.. I have tried doing something.. but its not helping me