0

I have a junction Object called Service_Transaction_Type__c that creates a many-to-many relationship between two other custom objects (Service_Offering__c and Transaction_Type__c). I am also working with another custom object that has a rather long name (referred to as CustomObject__c here). CustomObject__c is a junction Object between Service_Offering__c and Account.

Typically, the only users who would be logged in as ":UserInfo" are customer portal users, so they will always have an associated contact.

Below is how I have started this query:

       User u = [SELECT Id, ContactId, FirstName FROM User WHERE Id = :UserInfo.getUserId()];

       Contact c = [SELECT Id, LastName, AccountId FROM Contact WHERE Id =:u.ContactId];

       Account a = [SELECT Id, Name FROM Account WHERE Id = :c.AccountId];

       List<CustomObject__c> sn = [SELECT Id, Name, Service_Offering__r.Name FROM CustomObject__c WHERE Account__c = :a.Id]; 

What I need to do next is come up with a list of all of the Transaction_Type__c names for all of the Service_Offering__c's that were returned when I queried the List(CustomObject__c> sn.

I have tried a number of ways of going about doing this. Some involving for loops or trying to create a list of Service_Offering__c's to query next. Below is an example of how I have attempted this:

List<Service_Offering__c> serviceList = new List<Service_Offering__c>();

    for (CustomObject__c snanaRecord : sn){
        serviceList.add(snanaRecord.Service_Offering__r);    

   }

List<Transaction_Type__c> trxNameList = [SELECT Name FROM Transaction_Type__c WHERE Service_Transaction_Type__r.Service_Offering__c IN :serviceList];

The error that keeps getting thrown is: "Didn't understand relationship 'Service_Transaction_Type__r' in field path. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name." I am using the "__r" in the field path. I would appreciate any input as to where I may be going wrong here.

Thanks,
CP

1 Answer 1

1

'Transaction_Type__c WHERE Service_Transaction_Type__r.Service_Offering__c'

Here Service_Transaction_Type is the child of Transaction type . you can't query it using this way(means you can't directly access from here).

use inner query to get all Service_Transaction_Type record and from there apply the Service list Filter in where condition.

or another approach is query the Service_Transaction_Type object and get parent record of transaction and then use Service Type in where condition.

So you can go with inner query or the second approach. If you want to use inner query then to get the relationship name go to child object select lookup field and there you will get the relationship name add '__r' in the name end.

4
  • I have tried this: List<Transaction_Type__c> trxNameList = [SELECT Id, Name, (SELECT Service_Offering__c FROM Service_Transaction_Type__r) FROM Transaction_Type__c WHERE Service_Transaction_Type__r.Service_Offering__c IN :serviceList]; Still running into the same error.
    – Chris P
    Feb 5, 2016 at 16:26
  • Did you check the relationship name nd you need to remove 'Service_transaction_type' from where condition. Feb 5, 2016 at 16:30
  • List<Transaction_Type__c> trxNameList = [SELECT Id, Name, (SELECT Service_Offering__c FROM Service_Transaction_Type__r) FROM Transaction_Type__c WHERE Service_Offering__c IN :serviceList]; I have removed the service_transaction_type__c from the WEHRE piece. In the nested FROM piece, I have tried __c and __r and get the same error. I am going to try that second approach now.
    – Chris P
    Feb 5, 2016 at 16:34
  • 1
    approach number 2 was successful. Many Thanks.
    – Chris P
    Feb 5, 2016 at 16:44

You must log in to answer this question.

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