Example SOQL queries

In this page

Using a contact's ID to select a Vonage Contact Center 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 Vonage Contact Center agent who owns the first case.

SOQL query

Data sources

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

User|NVM Agent Id

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.

SOQL query

Data sources

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

Case|Case Number|Count Distinct

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.

SOQL query

Data sources

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

Opportunity|Amount|Avg

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.

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

Task|Assigned To ID

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.

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

Case|Owner ID

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.

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

Opportunity|Owner ID

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.

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

Order(1-10)|Order ID

Order(1-10)|Record Count

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.

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

Order|deliverydate__c

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.

SOQL queryData sources
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

Opportunity|Amount

Opportunity|Close Date

Opportunity|Opportunity ID

Opportunity|Owner ID

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.

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

Account.Opportunities|Name

Account.Opportunities|Opportunity ID

Account|Account ID

Using the entered number to locate a case or other record (using contains)

The SOQL returns the case ID of the first case located with a reference number that contains the entered number.

SOQL queryData sources
SELECT ID
FROM Case
WHERE Reference_Number__c like '%$(EnteredNumber)%'
LIMIT 1

Case|Case ID

Support and documentation feedback

For general assistance, please contact Customer Support.

For help using this documentation, please send an email to docs_feedback@vonage.com. We're happy to hear from you. Your contribution helps everyone at Vonage! Please include the name of the page in your email.