Example SOQL queries

Using a contact's ID to select NewVoiceMedia agent's ID

Using the contact's ID, which is stored in an existing data source, the SOQL subquery selects the IDs of the owners of the cases relating to that ID.

The main query returns the ID of the NewVoiceMedia agent who owns the first case.

SELECT NVMContactWorld__NVM_Agent_Id__c
FROM User
WHERE Id IN
(
    SELECT OwnerId
    FROM Case          
    WHERE contactID = '$(Contact|ID)'
) LIMIT 1

Using a contact's name to get a count of open cases that the contact has reported

Using the contact's id, which you have previously retrieved and stored in an existing data source, the SOQL query returns the number of unique open cases.

SELECT COUNT_DISTINCT(CaseNumber)
FROM Case
WHERE
	Contact.Id = '$(Contact|Id)' AND
	Status != 'Closed'

Using an account's ID to get the average opportunity amount

Using the account name, which you have previously retrieved and stored in an existing data source, the SOQL query returns the average time to resolve a case for the account in the last quarter.

SELECT AVG(Amount)
FROM Opportunity
WHERE
	Account.Id='$(Account|Id)'

Using a lead's ID to get the owner of the most recent activity

Using the lead's ID, which you have previously retrieved and stored in an existing data source, the SOQL query returns activity date, owner ID and call type for the most recent activity.

SELECT ownerID 
FROM Task 
WHERE 
	CallType in ('Inbound', 'Outbound') AND 
	whoID = '$(Lead|Id)'
ORDER BY ActivityDate desc 
LIMIT 1

Using a contact's ID to get the owner of the most recent non-closed case

Using the contact's ID, which you have previously retrieved and stored in an existing data source, the SOQL query returns created date, owner ID and status for the most recent non-closed case.

SELECT ownerID 
FROM Case
WHERE 
	contactID = '$(Contact|Id)' AND 
	status !='Closed'
ORDER BY CreatedDate desc 
LIMIT 1

Using a contact's ID to get the owner of the non-closed opportunity with the greatest amount value

Using the contact's ID, which you have previously retrieved and stored in an existing data source, the SOQL query returns opportunity ID, opportunity amount, owner ID, close date and stage for the most non-closed opportunity with the greatest amount value.

SELECT ownerID 
FROM Opportunity 
WHERE 
	CONTACT__C = '$(Contact|Id)' AND 
	StageName not in ('Closed Won', 'Closed Lost') 
ORDER BY AMOUNT desc
LIMIT 1

Using an account's ID to get the number of undelivered orders

Using the account's ID, which you have previously retrieved and stored in an existing data source, the SOQL query returns the order ID, order status, and owner ID for the first 10 undelivered orders.

SELECT ID 
FROM order 
WHERE 
	accountID = '$(Account|Id)' AND 
	status__c != 'Delivered' 
ORDER BY OrderNumber desc
LIMIT 10

Using a zip code and order number to get an order's delivery date

Using the order number and zip code associated with an order, which you have previously retrieved using IVR Collect Digit String applets, the SOQL query returns the delivery date for the order.

SELECT deliverydate__c 
FROM order 
WHERE 
	OrderNumNoZero__c = '$(IVRSlot|OrderNumber)' AND 
	OrderZip__c like '$(IVRSlot|OrderZip)%' 
ORDER BY OrderNumber desc
LIMIT 1

Using an account's ID to get the most recently closed opportunity (within the last 30 days)

Using the account's ID, which you have previously retrieved and stored in an existing data source, the SOQL query returns the opportunity ID, amount, owner ID and close date for the opportunity most recently closed within the last 30 days.

SELECT ID, amount, ownerID, closedate 
FROM Opportunity 
WHERE 
	AccountID = '$(Opportunity|AccountID)' AND 
	StageName = 'Closed Won' AND 
	closedate = LAST_N_DAYS:30 
ORDER BY closedate DESC
LIMIT 1

Using a subquery to get the most recently created opportunity for a matched account

Using the agent's CLID, the SOQL returns the account ID for the first matching account. The subquery returns the opportunity ID and name for the most recently created opportunity related to the matching account.

SELECT Id,(Select Name, id FROM Opportunities order by CreatedDate desc Limit 1) 
FROM Account 
WHERE Phone ='$(CLID)' 
Limit 1