0

I want to run a SOQL query that fetches files associated with opportunities(in a particular stage). If I am not wrong content version is the actual object I need to be querying on to fetch content? Here is the query I am running

SELECT IsAssetEnabled, Checksum, ContentBodyId, ContentLocation, ContentModifiedDate, Origin, ContentUrl, ContentDocumentId, Id, CreatedById, CreatedDate, Description, ExternalDataSourceId, ExternalDocumentInfo1, ExternalDocumentInfo2, FeaturedContentBoost, FeaturedContentDate, FileExtension, SharingPrivacy, FileType, IsDeleted, FirstPublishLocationId, LastModifiedById, IsLatest, LastModifiedDate, IsMajorVersion, OwnerId, NegativeRatingCount, PathOnClient, PositiveRatingCount, SharingOption, PublishStatus, RatingCount, ContentSize, ReasonForChange, SystemModstamp, TagCsv, TextPreview, Title, ContentModifiedById, VersionData, VersionNumber 
 FROM ContentVersion

Questions:

  1. How do I add the column in the query that tells me what opportunity is this file associated with, plus how do I fetch files associated with the opportunity in XYZ stage only?

  2. I am running this query in G-connector for salesforce, when the above query runs I get the data in spreadsheet, how do I convert this data to the actual file png, JP, Pdf whatever it is, to make it usable for other application?

Thanks

0

Welcome to SSE.

You will have to do separate queries.

SELECT ContentDocumentId, LinkedEntityId FROM ContentDocumentLink where LinkedEntityId in ( SELECT Id FROM Opportunity where StageName = 'XYZ'  ) and LinkedEntity.Type='Opportunity'

Once you have the ContentDocumentId, please query ContentVersion for the column VersionData which stores base64 format which can then be converted as per your need.

select VersionData from contentversion where contentdocumentid in ('069XXXXXXXXXXXX','069YYYYYYYYYYYY')

where ('069XXXXXXXXXXXX','069YYYYYYYYYYYY') is comma separated values of Content Document Id

Please check following apps to download/extract files: https://appexchange.salesforce.com/listingDetail?listingId=a0N30000003Iz0jEAC https://www.sfdcfileexporter.com/sffxhome/

I suggest you go through Salesforce SOAP API Developer Guide which provides detailed information about the objects/tables.

  • Thanks for the help. I am trying to run a query in a tool 'G-connector for salesforce'. I am not sure if I can run 3 different queries in a single tab? Is there a way to create a nested query that queries all 3 objects, opportunities, ContentDocumentLink, ContentVersion? – Anonymous Sep 27 at 17:05
  • My usecase is that I want to export all the files that are attached to all the opportunities that are in XYZ stage. How can I achieve that? – Anonymous Sep 27 at 17:44
  • @Anonymous I have updated my answer with a new nested query and some tools to be used for downloading/extracting files. – pmehta28 Sep 30 at 14:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.