-1

We have a custom object with interface logs and I would like to get a list of only the last record for a certain entitiy (e.g. contacperson), only is this last record is nog succesful. If the last record for that specific entity is succesful, I don't need that record in my list. However, I have a query what shows the last unsuccesful record for a contactperson, regardless is there is an newer interface log which is succesful. How can I get a list of the last interface logs, which are only not succesful. This is the code I had: SELECT msf__Person__c, MAX(CreatedDate) FROM msf__Interface_Log__c WHERE msf__Successful__c = false GROUP BY msf__Person__c

Anyone idea if this is possible within SOQL?

1 Answer 1

0

Nice question actually. Not easy to do but I believe it is possible using 'HAVING' on SOQL. https://developer.salesforce.com/docs/atlas.en-us.240.0.soql_sosl.meta/soql_sosl/sforce_api_calls_soql_select_having.htm

I don't have an org with the right setup and data to verify this, but basically you:

  • don't want to filter on msf_Successful__c = false, because you also need 'true' in order to filter out the ones you don't need
  • need to order by CreatedDate (descending) so the latest one for each group of person is on top
  • then filter those based on whether the top one (=latest one) is not successful
  • (including the record Id in the query result is optional)

This would result in a query like this, showing you only the id/person where the latest created one is not successful:

SELECT MAX(id), msf__Person__c, MAX(CreatedDate) , MAX(msf__Successful__c) 
FROM msf__Interface_Log__c 
GROUP BY msf__Person__c 
HAVING MAX(msf__Successful__c) = false 
ORDER BY MAX(CreatedDate) DESC 
2
  • Thank you very much. At HAVING MAX(msf__Successful__c) = false I got the error that MAX() was not compatible with a true/false field, so I addes a formula field that was filled with the creation date, if succesful was true. So the query now is: SELECT MAX(id), msf__Person__c, MAX(CreatedDate) , MAX(SC_Date_succesful__c) FROM msf__Interface_Log__c GROUP BY msf__Person__c HAVING MAX(SC_Date_succesful__c) = NULL ORDER BY MAX(CreatedDate) DESC And this worked! Oct 28, 2022 at 14:11
  • Ah, nice workaround indeed :-) Oct 30, 2022 at 14:35

You must log in to answer this question.

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