Query extension examples related to accounts

This is for:

Developer System Administrator

This article describes complex queries using extensions that provide information related to the context of the currently selected account in Salesforce.

Recent emails

This query lists, in chronological order from the most recent to the latest, all emails exchanged between all people connected to the currently selected account.

{{accounts=$type(name:'Account') @title="{!>Name}"}}
{{contacts=$join(fromResultSet: {{accounts}}, toResultSet: $type(name: 'Contact'), field: '@sfaccountid')}}
{{emailAddresses=$valuesOfField(field: '@sfemail', resultSet: {{contacts}})}}
$emailsExchangedWithThoseAddresses(addresses: {{emailAddresses}})
NOT @sysisattachment
$sort(criteria: 'datedescending')

For more information on this type of query, see Anatomy of a complex query using extensions.

This query lists accounts for which the email address of attached contacts share the email domain of the currently selected account.

{{accounts=$type(name:'Account') @title="{!>Name}"}}
{{contacts=$join(fromResultSet: {{accounts}}, toResultSet: $type(name: 'Contact'), field: '@sfaccountid')}}
{{emailDomains=$valuesOfField(field: '@sfemaildomainname', resultSet: {{contacts}})}}
$join(toResultSet: $type(name: 'Account'),
      fromResultSet: $joinOnValues(resultSet: @uri,
                                   field: '@sfemaildomainname',
                                   values: {{emailDomains}}),
                                   field: '@sfaccountid')

In more details:

  • Line 1 gets the current account context.

    It creates an alias named accounts only containing results of type Account in which the name contains the name of the currently selected account.

  • Line 2 gets the contacts for this account context.

    It uses the standard extension $join to create an alias named contacts only containing results of type Contact that share the Salesforce account ID (field: '@sfaccountid') with accounts in the accounts alias.

  • Line 3 gets the email domains for these contacts.

    It uses the standard extension $valuesOfField to create an alias named emailDomains containing a list of email domains from the contacts in the contacts alias.

  • Lines 4 to 8 get the accounts that share these email domains.

    They use the standard extensions $join and $joinOnValues to return only accounts that share the email domains from the emailDomains alias.

Key colleagues

This query lists co-workers who exchanged emails in connection with the currently selected account.

{{accounts=$type(name:'Account') @title="{!>Name}"}}
{{contacts=$join(fromResultSet: {{accounts}}, toResultSet: $type(name: 'Contact'), field: '@sfaccountid')}}
{{contactEmailAddresses=$valuesOfField(field: '@sfemail', resultSet: {{contacts}})}}
{{emailsExchangedWithAccounts=$emailsExchangedWithThoseAddresses(addresses: {{contactEmailAddresses}})}}
{{emailAddressesInvolvedWithAccounts=$participantsForThoseEmails(emails: {{emailsExchangedWithAccounts}})}}
{{onlyInternalEmailAddresses=$keepMatchingValues(regex: '.*@\Qmyorganization.com\E', values: {{emailAddressesInvolvedWithAccounts}})}}
$joinOnValues(resultSet: $type(name: 'Contact'), field: '@sfemail', values: {{onlyInternalEmailAddresses}})

In more details:

  • Line 1 gets the current account context.

    It creates an alias named accounts only containing results of type Account in which the name contains the name of the currently selected account.

  • Line 2 gets the contacts for this account context.

    It uses the standard extension $join to create an alias named contacts only containing results of type Contact that share the Salesforce account ID (field: '@sfaccountid') with accounts in the accounts alias.

  • Line 3 gets the email addresses for these contacts.

    It uses the standard extension $valuesOfField to create an alias named contactEmailAddresses containing a list of emails for the contacts in the contacts alias.

  • Line 4 gets the emails messages exchanged with these email addresses.

    It uses the $emailsExchangedWithThoseAddresses standard extension to create an emailsExchangedWithAccounts alias containing the email messages exchanged with addresses from the contactEmailAddresses alias.

  • Line 5 gets the participants to these email exchanges.

    It uses the $participantsForThoseEmails standard extension to create an emailAddressesInvolvedWithAccounts alias containing the email addresses involved in exchanges with emails from the emailsExchangedWithAccounts alias.

  • Line 6 keeps only the internal participants to these email exchanges.

    It uses the $keepMatchingValues standard extension to create an onlyInternalEmailAddresses alias only containing internal addresses involved in exchanges with emails from the emailAddressesInvolvedWithAccounts alias.

    Important

    In the regular expression regex '.*@\Qmyorganization.com\E', you must change myorganization.com with your own domain. Use the Java regular expression (REGEX) syntax (see Lesson: Regular Expressions).

  • Line 7 gets the names of the internal participants to these email exchanges.

    It uses the $joinOnValues standard extension to return contact names for the emails addresses from the onlyInternalEmailAddresses alias.

Key contacts

This query lists external contacts with whom emails were exchanged in connection with the currently selected account.

{{accounts=$type(name:'Account') @title="{!>Name}"}}
{{contacts=$join(fromResultSet: {{accounts}}, toResultSet: $type(name: 'Contact'), field: '@sfaccountid')}}
{{contactEmailAddresses=$valuesOfField(field: '@sfemail', resultSet: {{contacts}})}}
{{emailsExchangedWithAccounts=$emailsExchangedWithThoseAddresses(addresses: {{contactEmailAddresses}})}}
{{emailAddressesInvolvedWithAccounts=$participantsForThoseEmails(emails: {{emailsExchangedWithAccounts}})}}
{{onlyInternalEmailAddresses=$keepMatchingValues(regex: '.*@\Qmyorganization.com\E', values: {{emailAddressesInvolvedWithAccounts}})}}
$joinOnValues(resultSet: $type(name: 'Contact'), field: '@sfemail', values: {{onlyInternalEmailAddresses}})

In more details:

  • Lines 1 to 5 are identical to that of the Key colleagues query.

  • Line 6 only keeps the internal participants to these email exchanges.

    It uses the $removeMatchingValues standard extension to create an onlyExternalEmailAddresses alias only containing external addresses involved in exchanges with emails from the emailAddressesInvolvedWithAccounts alias.

    Important

    In the regular expression regex: '.*@\Qmyorganization.com\E', you must change myorganization.com by your own domain. Use the Java regular expression (REGEX) syntax (see Lesson: Regular Expressions).

  • Line 7 gets the names of the internal participants to these email exchanges.

    It uses the $joinOnValues standard extension to return contact names for the emails addresses from the onlyExternalEmailAddresses alias.