You need to sign in to do that
Don't have an account?
Justin Mitchell
SOQL subquery syntax help needed
I need to query a list of accounts and their related contracts, but I need the accounts listed in order of the contract end dates.
So if Account_A has two contracts with end dates of 1/1/2020 and 1/1/2021
And Account_B has two contracts with end dates of 1/1/2019 and 1/1/2025
Then Account B needs to be on the top of the list because it has a contract which is ending sooner.
Is this possible to do in a single query?
Here's how the syntax would look in my mind if it helps explain what I'm trying to do. This query doesn't work though:
So if Account_A has two contracts with end dates of 1/1/2020 and 1/1/2021
And Account_B has two contracts with end dates of 1/1/2019 and 1/1/2025
Then Account B needs to be on the top of the list because it has a contract which is ending sooner.
Is this possible to do in a single query?
Here's how the syntax would look in my mind if it helps explain what I'm trying to do. This query doesn't work though:
SELECT Id, Name, (SELECT Id, ContractNumber, EndDate FROM Contracts ORDER BY EndDate DESC) FROM Accounts ORDER BY Contracts[0].EndDate DESC NULLS LASTIs this even possible?
- Rollup Helper (https://appexchange.salesforce.com/appxListingDetail?listingId=a0N30000009i3UpEAI)
You can then use the same in your query.All Answers
You may not be able to achieve this within a single query since, the outer Order By clause within your Soql will refer to the Account records and not the associated child Contract fields.
A solution to your issue would be to create a new Rollup Summary field on Account, which holds the MAX() date value of the associated child Contract End Date values. You can then use that field within your Select and Order By Clause instead of Contracts[0].EndDate to sort the results.
Assuming you call the new field as MaxContractEndDate on Account, your query would now become
I'm also realizing now there will need to be some logic for which date is used. I want the OLDEST date that is not already in the past. In other words, the date that is coming up the soonest, after today.
I think I'm going to have to write a trigger if I want to track this, and have it sync up every time a contract is created or the end date field is updated. Which opens up a whole bunch of potential issues. UGH. :(
Either that or I need to find a better way to sort my visualforce list. Hmmm
- Rollup Helper (https://appexchange.salesforce.com/appxListingDetail?listingId=a0N30000009i3UpEAI)
You can then use the same in your query.