a!sfcQuery() Function

Allows querying a Salesforce instance using the Salesforce Object Query Language. For more information on SOQL, refer to Salesforce SOQL reference documentation.

Syntax

a!sfcQuery( scsExternalSystemKey, usePerUserCredentials, endpoint, query)

Common Parameters:

scsExternalSystemKey: (Text) The key from the Third Party Credentials admin console page that corresponds to the set of credentials that should be used to authenticate.

usePerUserCredentials: (Boolean) If true the credentials set in the Third-Party Credentials settings page by the current user running the expression will be used. If false the site-wide credential values will be used.

endpoint: (Text) The Salesforce endpoint URL to use for authentication. Possible values are "https://login.salesforce.com/services/Soap/u/27.0", which is used for production and development environments, and "https://test.salesforce.com/services/Soap/u/27.0", which is used for the Salesforce "sandbox" test environment. Update the 27.0 portion of the endpoint URL to match the version of the API that you are accessing. As a best practice, create a constant for this value.

Specific Parameters:

query: (Text) The SOQL query to send to Salesforce

Returns

The function returns the standard connector result dictionary described in the main Connectors page.

If successful, the function returns a dictionary representation of the Salesforce QueryResult within the result field. Refer to Salesforce documentation for details regarding the contents of QueryResult.

Examples

Retrieve a List of Opportunities

Copy and paste the expression into the Interface Designer, save it, then call the interface in a Tempo Report to test.

load(
  local!pagingInfo: a!pagingInfo(
    startIndex: 1, 
    batchSize: 10,
    sort: {
      a!sortInfo("Amount", false)
    }
  ),
  local!countResult: a!sfcQuery(
    scsExternalSystemKey: cons!SALESFORCE_SCS_KEY,
    usePerUserCredentials: true,
    endpoint: cons!SALESFORCE_ENDPOINT,
    query: "SELECT count(Id) FROM Opportunity"
  ),
  local!numOpportunities: if(
    local!countResult.success,
    local!countResult.result.result.AggregateResult.expr0,
    0
  ),
  with(
    local!queryResult: a!sfcQuery(
      scsExternalSystemKey: cons!SALESFORCE_SCS_KEY,
      usePerUserCredentials: true,
      endpoint: cons!SALESFORCE_ENDPOINT,
      query: concat(
        "SELECT Name, Account.Name, Amount ", 
        "FROM Opportunity ",
        "ORDER BY ",
        local!pagingInfo.sort.field,
        if(local!pagingInfo.sort.ascending, " ASC", " DESC"),
        " LIMIT ",
        local!pagingInfo.batchSize,
        " OFFSET ",
        local!pagingInfo.startIndex - 1
      )
    ),
    local!opportunities: if(
      local!queryResult.success, 
      toDataSubset(local!queryResult.result.result.Opportunity),
      null
    ),
    a!dashboardLayout(
      firstColumnContents:{
        if(
          isnull(local!opportunities),
          a!textField(
            label: "An error occurred",
            readOnly:true,
            value: local!queryResult.error.message
          ),
          a!gridField(
            totalCount: local!numOpportunities,
            label:local!pagingInfo.startIndex,
            columns:{
              a!gridTextColumn(
                label: "Name",
                field: "Name",
                data: local!opportunities.data.Name
              ),
              a!gridTextColumn(
                label: "Account",
                field: "Account.Name",
                data: local!opportunities.data.Account.Name
              ),
              a!gridTextColumn(
                label: "Amount",
                field: "Amount",
                alignment: "RIGHT",
                data: dollar(local!opportunities.data.Amount,0)
              )
            },
            value: local!pagingInfo,
            saveInto: local!pagingInfo
          )
        )
      }
    )
  )
)
17.1

On This Page

FEEDBACK