Example SOQL queries
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 query | Data 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 query | Data 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 query | Data 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 query | Data 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 query | Data 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 query | Data 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 query | Data 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 query | Data sources |
|---|---|
SELECT ID
FROM Case
WHERE Reference_Number__c like '%$(EnteredNumber)%'
LIMIT 1 | Case|Case ID |
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.