I have a custom object that looks like this
MyObject__c -- Company__c (ref to Account) -- ParentCompany__c (ref to Account) -- CompanyType__c (picklist: Parent/Child) -- (a lot more data and demographics fields)
What I need to do is write a SOQL query that takes an Account Id which can be either a parent or child account Id, but it needs to return the
CompanyType__c = 'Parent' MyObject__c record.
This is a new Salesforce implementation, so I have a certain amount of control over the objects. I can edit or change the relationships up in a way to support this action if needed.
I also have control over the Apex. This MyOject__c table gets populated by a nightly scheduled batchable. I could do something with that.
If the input account id represents a parent company, then this works
WHERE CompanyType__c = 'Parent' AND Company__c = :acctId. However, if the Account Id is that of a child account, I still need to get the MyObject__c record for it's parent. And I haven't been able to come up with a subquery that works.
Here's a bit more about the config.
Account also has a custom field:
-- Id -- CorporateParent__c (ref to Account)
There is a 1-to-many relationship from Account to MyObject__c. Also:
- If an
Accountis the corporate parent, then
- For a given MyObject__c, if CompanyType__c = 'Child', then
MyObject__c.Company__r.CorporateParent__c == MyObject__c.ParentCompany__c.
Accountin that if it is a 'Parent' record then both
ParentCompany__chave the same account Id.
If this were SQL query, either of these 2 queries would work:
SELECT Id, Name, etc FROM MyObject__c WHERE CompanyType__c = 'Parent' AND ( Company__c = :acctId OR Company__c = ( SELECT TOP(1) Parent__c FROM MyObject__c WHERE Company__c = :acctId ) )
SELECT Id, Name, etc FROM MyObject__c WHERE CompanyType__c = 'Parent' AND ( Company__c = :acctId OR Company__c = ( SELECT CorporateParent__c FROM Account WHERE Id = :acctId ) )
But I cannot seem to get the relationships correct, or the queries. I've seen the error "sObject type 'some-field' is not supported. If you are attempting to use a custom object, be sure to append the '__c' after the entity name." so many times. I've tried using
IN instead of
=. When I try the subquery on Account, it says the data has to be related. When I try the subquery on MyObject__c, it says it has to be on a relationship. I don't know what to try next.