[SalesForce] File SOQL query

I need to search for Partner Accounts that don't have a Partner Agreement attached in the Files section. To find that, I believe the best way is to get a list of all Files attached to the Accounts that match the criteria. I managed to get a list of Files ID, but that is a bit useless for my purpose, what I really need is to include the Title in the results. Is this possible?

Here is my query so far, but I get an error saying

INVALID_FIELD: SELECT ContentDocumentID, ContentDocumentLinks.Title
FROM ^ ERROR at Row:1:Column:27 Didn't understand relationship
'ContentDocumentLinks' in field path.

SELECT ContentDocumentID, ContentDocumentLinks.Title 
FROM   ContentDocumentLink 
WHERE  LinkedEntityId IN
  (SELECT ID FROM Account 
  WHERE   Type = 'Active Partner (signed contract)' 
  OR      Type = '2-Tier Partner')

Any help would be much appreciated.

Best Answer

There is not Title on ContentDocumentLink, availible fields are documented here

You could instead query the LinkedEntity.Name or the ContentDocument.Title if that hepls:

SELECT ContentDocumentID, LinkedEntity.Name, ContentDocument.Title
FROM   ContentDocumentLink 
WHERE  LinkedEntityId IN
  (SELECT ID FROM Account 
  WHERE   Type = 'Active Partner (signed contract)' 
  OR      Type = '2-Tier Partner')

Related Solutions

[SalesForce] Insert Content and link it programmatically using SOAP API

I used the Salesforce UI to add a File to an Opportunity. Then checked the resulting ContentDocumentLink records with SQOL.

Select Id,LinkedEntityId,ContentDocumentId,IsDeleted,ShareType,Visibility from ContentDocumentLink where ContentDocumentId = '0694000000548HHAAY'

Note how there are two records, one to the Opportunity and one to my user record (this will be why you can't see it in your private library). The one to the Opportunity has the ShareType V (Viewer permission) and Visibility AllUsers

Sorting ContentVersion based on Related objects like Account, Contact

If you have a relatively small number of types that you want to find the related content for, a query of this form for each type (Account, Contact, ...):

ContentDocumentLink[] cdls = [
    select LinkedEntityId, LinkedEntity.Name
    from ContentDocumentLink
    where LinkedEntityId in (select Id from Account)
    order by LinkedEntity.Name
    limit 1000
];

will work. The two queries are combined, allowing the platform's query optimiser to remove redundant work.

You can then combine the results using Apex code and sort as you wish in that Apex code.

Note that this construct that could avoid the need for Apex code:

    where LinkedEntityId in (select Id from Account)
    or LinkedEntityId in (select Id from Contact)

is not allowed as it results in this error:

Semi join sub-selects are not allowed with the 'OR' operator

Best Answer

Related Solutions

[SalesForce] Insert Content and link it programmatically using SOAP API

Sorting ContentVersion based on Related objects like Account, Contact

Related Topic