AOCR - Advanced Configuration & Use
Install Guide - Configuration Guide - Managing Subscription and Billing - Advanced Config & Use
If the “wizard” configuration options don’t quite get you what you need, we have a couple of more advanced ways that you can use the app.
Using these advanced options, we can:
- Define specific criteria for Contact Selection
- Define specific criteria for Opportunity Selection (batch mode only)
Better Contact Selection
Out of the box, you can choose how many Contacts from an Opportunity’s Account, and how to sort them to choose the best ones. For many customers this works great, but what if you want greater control?
We have the option to provide part of the SOQL statement to select the correct Contacts. First, let’s describe what happens by default.
Assuming the number of desired Contacts is 7 and the field we are sorting by is pi__last_activity__c this is the SOQL that we end up generating:
SELECT Id, (
SELECT Id
FROM Contacts
ORDER BY pi__last_activity__c DESC
LIMIT 7
)
FROM Account
WHERE Id IN (<List of Account Ids here>)
We provide a limited ability to customize the SubQuery so that you can provide your own WHERE and ORDER BY conditions to help fine-tune the Contact selection. If you don’t want to provide your own ORDER BY, the app will add in the one configured through the Wizard.
Planning Your Custom SOQL Statement
Providing a custom SOQL segment can be a bit intimidating. The best way to play with this is to find a place where you can type in your own SOQL and view the results. There are a few ways one can do this and we find the easiest is right in the Developer Console built right into Salesforce (see below for details on how).
To get started, we need to understand how our customized SOQL will be used within the app. Below we have an additional placeholder to indicate exactly what you are tweaking.
SELECT Id, (
SELECT Id
FROM Contacts
<Custom Contact SOQL Extension>
LIMIT 7
)
FROM Account
WHERE Id IN (<List of Account Ids here>)
As an example, we want to only automatically create Opportunity Contact Role records for Contacts having the First Name of Bob, and we want to only have Bobs that have attended an event recently. We will leverage the Contact’s standard field FirstName and a Custom Field last_event_date__c (populated by some magic elsewhere).
First, we will find an Account that has a Contact fitting our criteria to test with, then we will write a complete SOQL query that works. For our example, here is the final working SOQL:
SELECT Id, ( SELECT Id FROM Contacts WHERE FirstName = 'Bob' ORDER BY last_event_date__c DESC LIMIT 7 ) FROM Account WHERE Id IN ('001J000002ckGTwIAM')
Running this SOQL gives us the correct Contact IDs for the Account, so let’s take the important bits and update the app.
For our example above, the Contact SOQL Extension is: WHERE FirstName = 'Bob' ORDER BY last_event_date__c
.
Updating the App to use your Customization
There is no validation for making changes here. Do so at your own risk!
Try this out in a Sandbox before deploying to Production
- Log in to Salesforce Lightning, go to Setup
- Search for Custom Settings, click the Manage link beside the AutomatedOpportunityContactRole setting
- Click Edit
- Provide your Contact SOQL Extension (depending on your browser, you may want to resize the text area that you are working with, it is quite small. For our example above, the screen would look like this:
- Save
- Test!
How can I execute SOQL Statements using the Developer Console?
- Log in to Salesforce Lightning, click on the Gear icon at the top right, and click Developer Console
- Once the console pops up, click on the Query Editor tab in the bottom section
- Paste in the SOQL statement provided earlier, and replace
<List of Account Ids here>
with a single Account ID that you will be testing with. - Click the Execute button to see the results.
Additional Examples
Filter Contacts based on CampaignMember records
With this example, we are only looking for Contacts on the Opportunity's Account that have been associated to a Campaign of type "Webinar" within the last 6 months.
WHERE Id IN (
SELECT ContactId
FROM CampaignMember
WHERE CreatedDate = LAST_N_DAYS:180
AND Campaign.Type = 'Webinar'
)
This can be tested by executing the following SOQL query
SELECT Id, (
SELECT Id
FROM Contacts
WHERE Id IN (
SELECT ContactId
FROM CampaignMember
WHERE CreatedDate = LAST_N_DAYS:180
AND Campaign.Type = 'Webinar'
)
LIMIT 10
)
FROM Account
WHERE Id IN (<List of Account Ids here>)
Better Opportunity Selection (batch mode only)
Out of the box, you can select the Stages an Opportunity is in, as well as the Opportunity Type (not Record Type).
In version 1.3.0 of the app, we've created another SOQL extension, allowing you to provide additional filter criteria, allowing you to process only the Opportunities you desire.
Planning Your Custom SOQL Statement
If you've read the "Planning Your Custom SOQL Statement" for Contacts, this approach is very similar.
To get started, we need to understand how our customized SOQL will be used within the app. Below we have an additional placeholder to indicate exactly what you are tweaking.
SELECT Id, AccountId, OwnerId
FROM Opportunity
WHERE (
sl_auto_ocr__Auto_Roles_Created__c = null
OR sl_auto_ocr__Auto_Roles_Created__c != TRUE
)
AND LastModifiedDate = LAST_N_DAYS:<daysReachFromConfig>
AND StageName IN (<stageNamesFromConfig>)
AND Type NOT IN (<typesFromConfig>)
<Custon Opportunity SOQL Extension>
As an example, we want to only automatically process Opportunity records having the Opportunity Name starting with United.
We can then add an "AND" expression to continue to filter our list of Opportunities to process. With our example, the complete SOQL query would be below, and our SOQL Extension would be only what we have on line 10:
SELECT Id, AccountId, OwnerId
FROM Opportunity
WHERE (
sl_auto_ocr__Auto_Roles_Created__c = null
OR sl_auto_ocr__Auto_Roles_Created__c != TRUE
)
AND LastModifiedDate = LAST_N_DAYS:30
AND StageName IN ('Closed Won','Negotiation/Review')
AND Type NOT IN ('Downgrade')
AND Name LIKE 'United%'
Updating the App to use your Custom SOQL Statement
There is no validation for making changes here. Do so at your own risk!
Try this out in a Sandbox before deploying to Production
- Log in to Salesforce Lightning, go to Setup
- Search for Custom Settings, click the Manage link beside the AutomatedOpportunityContactRole setting
- Click Edit
- Provide your Opportunity SOQL Extension (depending on your browser, you may want to resize the text area that you are working with, it is quite small. For our example above, the screen would look like this:
- Save
- Test!
Additional Examples
Filter Opportunities by Record Type Name
With this example, we are only looking for Opportunities of certain record types.
AND RecordType.Name IN ('RecordType1','RecordType2')
This can be tested by executing the following SOQL query
SELECT Id, AccountId, OwnerId
FROM Opportunity
WHERE (
sl_auto_ocr__Auto_Roles_Created__c = null
OR sl_auto_ocr__Auto_Roles_Created__c != TRUE
)
AND LastModifiedDate = LAST_N_DAYS:30
AND StageName IN ('Closed Won','Negotiation/Review')
AND Type NOT IN ('Downgrade')
AND RecordType.Name IN ('RecordType1','RecordType2')