[SalesForce] How to fetch contentNote records as per the contact record

I am trying to fetch the note title, note body as per the related contact ID in one SOQL query as two SOQL queries won't serve the purpose.

In contentNote, We have the note title and note body but don't have the contact information. For contact information, there is contentDocumentLink but contentNote don't share any direct relation with contentDocumentLink.

When I run the below SOQL query in the Developer Console

SELECT Id, Title, TextPreview FROM ContentVersion 
WHERE ContentDocumentID IN (SELECT ContentDocumentId FROM ContentDocumentLink WHERE LinkedEntityId = '0030H000059CBKAQA4')

It throws below error:

ERROR at Row:2:Column:59 Entity 'ContentDocumentLink' is not supported
for semi join inner selects

below query also don't serve the purpose as contentDocument object has note title but don't have the note body.

SELECT ContentDocument.title FROM ContentDocumentLink WHERE LinkedEntityId
= '0030H000059CBKAQA4'

It is really frustrating to get all the details in one query, could someone please suggest if note title, body and contact id can be fetched in a single
query

Our purpose is to get all the data and integrate with another system.

Best Answer

There exists a relation between ContentNote and ContentDocumentLink. In order to get the ContentNote or ContentVersion, there are different ways of achieving it.

Using 2 SOQL's

  • You will have to first query on ContentDocumentLink using the LinkedEntityId i.e. in your case it will be ContactIDs and collect all the ContentDocumentId's.
  • Then after getting the ContentDocumentID's, you can query on ContentNote using the ContentDocumentId's retrieved from previous query.

This is how your sample code should look like

Set<Id> contactIds= new Set<Id>(); 
contactIds.add('0030H000059CBKAQA4'); //include all the contact ids for which you need to retrieve content note
Set<Id> contentDocumentIds = new Set<Id>();
//get all contentdocumentids so that you can query on contentnote
for(ContentDocumentLink conDoc : [SELECT ContentDocumentId FROM ContentDocumentLink WHERE LinkedEntityId IN :contactIds]){
    contentDocumentIds.add(conDoc.ContentDocumentId);
}
//get all the content notes using contentDocumentIds
List<ContentNote> contentNotes = [SELECT Id, Title, Content from ContentNote where Id In: contentDocumentIds];

Using relationship queries

  • You can get the ContentNote details using relationship fields by querying on ContentDocumentLink object and filtering it with LinkedEntityId

Your sample code for that would be

Set<Id> contactIds= new Set<Id>(); 
contactIds.add('0030H000059CBKAQA4'); //include all the contact ids for which you need to retrieve content note
List<ContentDocumentLink> conLinks = [select Id, LinkedEntityId,ContentDocumentId, ContentDocument.LatestPublishedVersion.Title,ContentDocument.LatestPublishedVersion.VersionData from ContentDocumentLink where LinkedEntityId IN :contactIds];
system.debug(conLinks); //this will give only fields from ContentDocumentLink
system.debug(conLinks[0].LinkedEntityId); //this will give LinkedEntityId, in your case it will be Contact's ID
system.debug(conLinks[0].ContentDocument.LatestPublishedVersion.Title); //to access ContentNote's title
system.debug(conLinks[0].ContentDocument.LatestPublishedVersion.VersionData); //to access ContentNote's VersionData

In the above query, you can get the ContentNote's fields i.e. Title and VersionData along with its LinkedEntityId which would be your ContactId

You can refer the API document of Content Note for more information.

Related Topic