Using SOQL Query in DataRaptor Formula

We can execute a SOQL query in the Dataraptor formula by using the QUERY function. But it can only retrieve up to one column. Let's see a few examples of using it:

• Passing a static value

QUERY ("SELECT Name FROM Account WHERE BillingState ='CA'")        

• Querying with a single dynamic value

QUERY( "SELECT AccountId FROM User WHERE Id = '{0}'",$Vlocity.UserId )        

• Querying with multiple dynamic values

QUERY ( "SELECT Id FROM Account WHERE RecordType.DeveloperName = 'Member' And LastName Like '{0}' And FirstName Like '{1}' And DateOfBirth__pc = {2} And Phone = '{3}' ", lastNameToSearch, firstNameToSearch, formattedDate, Phone)        

• Similarly, we have a CountQuery function that returns the number of records returned

COUNTQUERY ( "SELECT COUNT() FROM Case WHERE AccountId = '{0}'" , Id)        

Considerations :

1. To use the Like operator in the query we have to form the string in the below format and use this concatenated value in the Query.

2. To Query with the date field, the dynamic notation should not have quotes i.e., instead of ‘{1}’ use {1}. For example, DateOfBirth__pc = {1}

References:

https://help.salesforce.com/s/articleView?id=sf.os_function_reference_56716.htm&type=5