0

I have an object called SRC which has account as lookup in it. SRC content will be as shown below:

SRC ID Account__c Status City__c LastModifiedOn
1 1 Yes Delhi 12-Apr
2 1 No Cochin 13-Apr
3 2 No Delhi 14-Apr
4 2 Yes Indore 15-Apr

SRC is created populating the BillingCity from Account at that point of time. So everytime BillingCity is changed in account there will be a new SRC created.

I want latest SRC record of an account. The output is as shown below:

SRC ID Account__c Status City__c LastModifiedOn
2 1 No Cochin 13-Apr
4 2 Yes Indore 15-Apr

I ideally want to write a query like this:

SELECT 
    Status 
FROM SRC__c 
WHERE 
    Account__c IN ('1', '2') AND 
    Account__r.BillingCity = City__c
GROUP BY Account__r.Id 
ORDER by LastModifiedDate DESC LIMIT 1

How can I achieve this? I cannot run the query inside a for loop, because I am writing this code in a trigger.

I have two problems here:

  1. Account__r.BillingCity = City__c is not allowed
  2. using group by gives me "Ordered field must be grouped or aggregated" error
2
  • In what context are you trying to add a query? Is this a trigger on Account that updates SRCs? Or is this a trigger on SRC? May 11, 2021 at 19:32
  • Actually, it is trigger on quote. I want to see if the latest account's SRC status is "Yes". May 12, 2021 at 0:28

1 Answer 1

1

If you query FROM Account, you can order the child records. This grouping strategy will be much easier to work with here.

SELECT (SELECT Status__c FROM SRCs__r ORDER BY LastModifiedDate DESC)
FROM Account WHERE ...

Make sure you get the correct child relationship name. There are numerous posts both here and in the Salesforce documentation on how to do so.

2
  • My SRC object has lookup to Account, but Account does not have relationship to SRC. In this case, how can I write this query? May 12, 2021 at 8:13
  • A child relationship is a relationship. You described your relationships the same in your original post, and this answer covers how to query in that situation...
    – Adrian Larson
    May 12, 2021 at 13:15

You must log in to answer this question.

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