0

I've learned a lot about Salesforce querying but also have a lot to learn. I hope this question is clear.

I have a lookup relationship where Restrictions__c is the parent and Actions__c is the child (API name Actions_Restrictions__c), another where Actions__c is the parent and Restrictions__c is the child (API name Action_Id__c), and another where HR__c is the parent and Actions__c is the child (API name HR__c).

I need to return field Restriction_Description__c on Restrictions__c where the CreatedDate (or could use the ID auto number field) field on Actions__c is the most recent one, and where the HR ID field is '[###].

Using Salesforce Developer Console for starters, this query returns data but doesn't limit the data to just the most recent HR.ID:

SELECT Name, Restriction_Description__c, 
     (SELECT CreatedDate FROM Actions__r 
        WHERE HR__r.Name = '[###]' 
        ORDER BY CreatedDate DESC LIMIT 1) 
      FROM Restrictions__c 
      WHERE Action_ID__r.HR__c = '[###]'

Can you please suggest what changes are needed to return Restriction_Description__c for only the most recent CreatedDate?

Also, strange to me that instead of returning a column called CreatedDate on Actions__r, it returns a column called Actions__r that has no data. Why is that?

The query is going to be used in a Conga query.

Thank you.

3
  • This queries a Restriction whose parent Action has HR__c = '[###]' but is fetching the Restriction's children Actions that also have HR__c = '[###]'. Those children Actions (by definition, 0+) may not be the same Actions as the Restriction's parent Action. Use edit and illustrate with actual data and I suspect you'll see the issue
    – cropredy
    Commented Mar 11, 2021 at 19:27
  • Why do you have Restriction and Action as both parent and child of each other? That doesn't make any sense. Commented Mar 18, 2021 at 3:27
  • Thank you very much for the comments, @DavidCheng and everyone. I continue working on this task using the Stack Exchange input and others. When the problem is resolved, I'll respond with the solution.
    – Lynda
    Commented Mar 21, 2021 at 19:42

1 Answer 1

0

In your query, you have an outer query on Restrictions__c that will return a row for all Restrictions that have a parent Action.HR__c = '[###]'

You also have an inner query, meaning that for each Restrictions row returned, you also query a list of children Actions. This list of children Actions will only contain one action with the most recent createdDate and also whose HR__r.Name = '[###]'

To answer your second question first, The column: Actions__r actually represents a List of child Actions with the fields you select in the SELECT clause. If there is no data, it means that for that Restrictions, wither no children Actions__r Exist, or no children Actions__r exist that meet your criteria of HR__r.Name

An example of this is querying Accounts with its Child Contacts:

SELECT Id, (SELECT Id, Name FROM Contacts WHERE FirstName = 'Joe') FROM Account
  • This query will return rows of ALL the Accounts in your org
  • for each Account row, you will have a List (or rather a column) called Contacts
  • inside the Contacts column, you will have a list of Contacts that have a first name of Joe. If there is no Contact with a FirstName of Joe for that given account, then the Contacts column will be blank. If an Account has child Contact(s) with a FirstName of Joe, the Id and Names of these contacts will be in the List.

In regards to your relationships between Restrictions__c and Actions__c, I understand you have a lookup field,

  1. Restrictions__c.Action_ID__c
  2. Actions__c.Actions_Restrictions__c

Are you able to use the Parent Action for Restrictions instead?

If that is the case, you can break down your question in the following way to design the query

I need to return field Restriction_Description__c on Restrictions__c

SELECT Restriction_Description__c FROM Restrictions__c

where the [parent actions] HR ID field is '[###]'.

WHERE Action_ID__r.hr__c = '[###]'

where the CreatedDate (or could use the ID auto number field) field on [its parent] Actions__c is the most recent one,

ORDER BY Action_ID__r.CreatedDate DESC LIMIT 1

Does that help solve your problem?

1
  • Thank you for the time you took answering my question, Z33dawg and @cropredy. There are multiple rows returned for Restriction_Description__c where Action_ID__r.hr__c = '[###]' that can have different Created Dates and Action_ID__r.Names. I need to get the Restriction_Description__c field only for the latest Created Date or Action_ID__r.Name. BTW I tried creating a formula field for MAX(CreatedDate) and MAX(Action_ID__r.Names) but found I need a rollup summary field for getting MAX value for this purpose, and I need to use a query for the work if at all possible.
    – Lynda
    Commented Mar 12, 2021 at 18:11

You must log in to answer this question.

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