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:
Comments