0

Sorry for the title, I could not come up with a better one.

Basically, I have the following query

SELECT Id, (SELECT Name FROM Prod__c WHERE Id = Case.Id) FROM Case limit 20

So what I want to achieve is to use the result Id from the query on the sub-query. It is some internal SF Id, and the name I need is in Prod__c. When trying it like this I get the following error:

Bind variables only allowed in Apex code

I'm not sure if what I'm trying to do is even possible. But any help is appreciated.

Improve this question

1 Answer 1

Reset to default
3

Salesforce does automatically the filter on the sub-queries provided that you use a relationship field to fetch them (Lookup / Master Detail).

Assume you have an sObject called Prod__c and also a relationship field from Prod__c to Case with Api name Case__c (and which "Child Relationship Name" is Prods (You can check chat on the detail of the relationship field)). If that is the case, you can :

SELECT Id, (SELECT Name from Prods__r) FROM Case LIMIT 20

In this case Prods__r is the Child Relationship Name + __r. Note that standard relationships do not need the __r

Edit after the chat

The sObject Case has a relationship to Prod__c through Case_Product__c. He wants to get all cases that have a specific Prod__c (by Id), so the pseudo code would be

Id prodId = 'whateverId';
List<Case> casesForProduct = [SELECT Subject, Case_Product__r.Name FROM Case WHERE Case_Product__c = :prodId];

// additionally consider ordering and limiting the results
Improve this answer
9
  • Thanks, but the problem is with the usage of a variable for the where clause. I can get data from that object if I use a fix value. But here I do a query for Id on Case, and would require that result in the subquery to Prod__c as a where limit
    – derchris
    Nov 11, 2020 at 15:53
  • salesforce automatically filters the Prod__c records that are related to each of the Case records retrieved, you don't need an extra where clause. If you want to compare things between those, you could have a formula field on Prod__c that compares data from Case and Prod__c and filter it in the sub-query
    – Sergio Alcocer
    Nov 11, 2020 at 15:58
  • Unfortunately this does not work for me. I get Didn't understand relationship when trying with both __c or __r. I'm also just a user of the API, so not able to make any changes.
    – derchris
    Nov 11, 2020 at 16:16
  • If you have access to the schema, go to the sObject Prod__c, go to the field list, then go to the detail of the lookup / Master-Detail that points to Case, and you'll find that you have to put before the __r inside the section called Child Relationship Name (you don't need to change anything, just being able to read it. Otherwise... you could describe the sobject and figure it out there)
    – Sergio Alcocer
    Nov 11, 2020 at 16:19
  • Sadly that is beyond my SF knowledge, I'm just a user of the frontend. I can access the Id if I append it to our SF URL, but I don't see anyway how to access more details of this object from there.
    – derchris
    Nov 11, 2020 at 16:28

You must log in to answer this question.

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