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.

Notes

If called multiple times in the same expression with the same parameters, only one query is made to Salesforce when the expression is evaluated and the result is reused for each identical call. This caching only applies within a single expression evaluation. Return values are not cached in separate expressions, or in separate evaluations of the same expression.

Examples

Retrieve a List of Opportunities

Copy and paste the expression into the INTERFACE DEFINITION in EXPRESSION MODE, save it, then call the interface in a Tempo Report to test.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
=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!textField(
          label: "An error occurred",
          readOnly:true,
          value: local!queryResult.error.message,
          showWhen: isnull(local!opportunities)
        ),
        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,
          rowHeader: 1,
          showWhen: not(isnull(local!opportunities))
        )
    }
  )
)
FEEDBACK