Querying Records in Salesforce
This task will execute a read query against the Salesforce system and return the data in XML format. The query must be written in Salesforce Object Query Language (SOQL). Documentation on theĀ Salesforce Object Query Language (SOQL) is provided by Salesforce.
Settings
Connection
Required
The Salesforce connection to use, see Connecting to Salesforce
Output File
Required
The file name or variable to send the results of the query e.g. Output.xml
Date Created
Required
This setting will be automatically set to the highest CreatedDate value seen so far in the results produced by the query. The value can be referenced within your query, by using @CreatedDate. This allows you to filter the export so that it only returns new records.
Note
Your query must select the CreatedDate field as part of your query for this to work.
Date Modified
Required
This setting will be automatically set to the highest LastModifiedDate value seen so far in the results produced by the query. The value can be referenced within your query, by using @LastModifiedDate. This allows you to filter the export so that it only returns modified records.
Note
Your query must select the LastModifiedDate field as part of your query for this to work.
File Export Settings
The following settings allow you to download attached files from Salesforce.
Export Files
Required
Enables the export of attached files. For this to work, your query must select ContentVersion objects from Salesforce. You don't need to query this object directly, they can be selected via sub-queries or relationships. You must include the ContentVersion.VersionData field in your query, and at least one of the following: ContentVersion.Id, ContentVersion.ContentDocumentId, ContentVersion.PathOnClient or ContentVersion.Title (depending on the 'Filename Convention' setting). We recommend including the ContentVersion.FileExtension field in your query, as it allows Zynk to apply the correct file extension to the exported files.
The following query shows how to get accounts from Salesforce, along with their attached files. Note that the LatestPublishedVersion relationship points to a ContentVersion object.
SELECT Account.Id,
Account.Name,
(
SELECT ContentDocument.LatestPublishedVersion.Id,
ContentDocument.LatestPublishedVersion.ContentDocumentId,
ContentDocument.LatestPublishedVersion.VersionData,
ContentDocument.LatestPublishedVersion.PathOnClient,
ContentDocument.LatestPublishedVersion.Title,
ContentDocument.LatestPublishedVersion.FileExtension
FROM AttachedContentDocuments
)
FROM Account
Filename Convention
Required
Determines the naming convention to apply to the exported files. The following options are available:
- ContentVersionId - Each file will be named according to the content version ID. This is unique to each version of a file in Salesforce, and is the most robust option when it comes to ensuring uniqueness of the file names. This is the default option.
- ContentDocumentId - Each file will be named according to the content document ID. This is unique to each file in Salesforce, but not each version of the file. Therefore it should not be used if you want to export different versions of the same file.
- PathOnClient - Each file will be named according to the path on client field. This is not guaranteed to be unique, so it should be used with caution. It may also include folders as part of the path. If so, the folders will be replicated locally.
- Title - Each file will be named according to the title field. This is not guaranteed to be unique, so it should be used with caution.
Output Folder
Optional
The folder to export the attached files to. If left blank, the files will be saved to the workflow's working directory.
Overwrite Existing Copy
Required
When set to True, Zynk will always download every attached file returned by the query, overwriting the local copy if it already exists. When set to False, Zynk will check if a file already exists locally, and will only download it if not.
We recommend setting this to False, to ensure the task doesn't download the same file multiple times. When this is used in conjunction with the 'ContentVersionId' filename convention, it ensures each version of a file can only be downloaded once. When this is used in conjunction with the 'ContentDocumentId' filename convention, it ensures only one version of a file is downloaded, which is particularly useful if you are querying for the latest version of a file.
Ignore Child Record Dates
Required
Set to true to only consider the CreatedDate and LastModifiedDate fields at the parent record level when determining the values for the 'Date Created' and 'Date Modified' settings. When set to false, the CreatedDate and LastModifiedDate on all child records will also be considered.
Query
Required
The Salesforce Object Query (SOQL) to be executed, supports nested queries and custom objects. e.g. SELECT Id, Name FROM Account
Zynk Settings
Examples
Sample SOQL query, to download modified closed won opportunities and their related account and item lines:
SELECT Opportunity.Id,
Opportunity.Name,
Opportunity.Owner.Name,
Opportunity.CloseDate,
Opportunity.HasOpportunityLineItem,
Opportunity.Amount,
Opportunity.CreatedDate,
Opportunity.LastModifiedDate,
Account.Id,
Account.AccountNumber,
Account.Name,
Account.Website,
Account.Phone,
Account.Fax,
Account.BillingAddress,
(
SELECT Quantity,
ListPrice,
UnitPrice,
Product2.Name,
Product2.ProductCode
FROM OpportunityLineItems
)
FROM Opportunity
WHERE StageName = 'Closed Won' AND Opportunity.Account.Id != null AND LastModifiedDate > @LastModifiedDate
Sample output file:
<?xml version="1.0" encoding="utf-8"?>
<QueryResult>
<done>true</done>
<records type="Opportunity" url="/services/data/v58.0/sobjects/Opportunity/0068000000SvrwHAAR">
<Id>0068000000SvrwHAAR</Id>
<Name>Test Opp</Name>
<Owner type="User" url="/services/data/v58.0/sobjects/User/00580000001nhfBAAQ">
<Name>Andrew Snape</Name>
</Owner>
<CloseDate>2023-06-01</CloseDate>
<HasOpportunityLineItem>true</HasOpportunityLineItem>
<Amount>1860000.0</Amount>
<CreatedDate>2023-04-08T13:52:34.000Z</CreatedDate>
<LastModifiedDate>2023-06-01T11:50:58.000Z</LastModifiedDate>
<Account type="Account" rl="/services/data/v58.0/sobjects/Account/0018000000cGzpgAAC">
<Id>0018000000cGzpgAAC</Id>
<AccountNumber>000010</AccountNumber>
<Name>Dura Limited</Name>
<Website>http://www.duraltd.com</Website>
<Phone>01112100100</Phone>
<Fax>02222200200</Fax>
<BillingAddress xsi:type="address">
<latitude xsi:nil="true" />
<longitude xsi:nil="true" />
<city>Dura</city>
<country>England</country>
<countryCode xsi:nil="true" />
<geocodeAccuracy xsi:nil="true" />
<postalCode>DU1 7TD</postalCode>
<state>County Dura</state>
<stateCode xsi:nil="true" />
<street>123 Industrial Est.</street>
</BillingAddress>
</Account>
<OpportunityLineItems>
<done>true</done>
<records type="OpportunityLineItem" url="/services/data/v58.0/sobjects/OpportunityLineItem/00k8000000Dh12BAAR">
<Quantity>12.0</Quantity>
<ListPrice>150000.0</ListPrice>
<UnitPrice>150000.0</UnitPrice>
<Product2 type="Product2" url="/services/data/v58.0/sobjects/Product2/01t80000001LdRuAAK">
<Name>GenWatt Gasoline 2000kW</Name>
<ProductCode>GC5060</ProductCode>
</Product2>
</records>
<records type="OpportunityLineItem" url="/services/data/v58.0/sobjects/OpportunityLineItem/00k8000000Dh12AAAR">
<Quantity>12.0</Quantity>
<ListPrice>5000.0</ListPrice>
<UnitPrice>5000.0</UnitPrice>
<Product2 type="Product2" url="/services/data/v58.0/sobjects/Product2/01t80000001LdRgAAK">
<Name>GenWatt Diesel 10kW</Name>
<ProductCode>GC1020</ProductCode>
</Product2>
</records>
<totalSize>2</totalSize>
</OpportunityLineItems>
</records>
<totalSize>1</totalSize>
</QueryResult>