VLOOKUP_RECORD_CONNECTION

Description

Performs a dynamic lookup on a Salesforce object through a connection and returns the full Object record from the first record that matches the provided field-value pairs.

Syntax

VLOOKUP_RECORD_CONNECTION(connection_name, object_name, ...[matching_field_name, matching_field_value], additional_clause_optional) -> Object

Parameters

Parameter	Data Type	Required	Description
connection_name	String	Yes	The name of the connection to use.
object_name	String	Yes	The name of the object to look up.
...matching_field_name	String	Yes	The name of the field used to match records.
...matching_field_value	Object	Yes	The field value used as matching criteria.
additional_clause_optional	String	No	Additional query conditions for more precise filtering.

Examples

VLOOKUP_RECORD_CONNECTION("Salesforce_Prod",
                          "Opportunity",
                          "Source_System_Id__c",
                           Source_System_Id__c)
-- Returns the Opportunity record from the "Salesforce_Prod" org where Source_System_Id__c matches the current record’s Source_System_Id__c.

VLOOKUP_RECORD_CONNECTION("Salesforce_Prod", 
                          "Opportunity", 
                          "Source_System_Id__c", Source_System_Id__c, 
                          "Amount > 50000")
-- Returns the Opportunity record from the "Salesforce_Prod" org where:
-- Source_System_Id__c matches the current record’s Source_System_Id__c,
-- and Amount > 50,000.

Tips

Use VLOOKUP_RECORD_CONNECTION under the “Related” tab in the “Variables” section. You can also set the variable’s Scope—Executable, Pipeline, Connection, or Global.
After the variable is created, reference it in the Mapping step (e.g., #accountVar.Name, #accountVar.OwnerId).
SOQL queries have a governor limit of 50,000 rows per transaction. Optimize your filters or leverage indexing to ensure your lookup remains within this limit.

Note

Compilation, caching, and freshness

Both function families compile and merge invocations into consolidated SOQL queries — calls that share the same object, WHERE, and additional_criteria (differing only in the SELECT fields) are combined to minimize database calls. When multiple Executables are associated with the same triggering object, the two families differ in scope and caching:

VLOOKUP / VLOOKUP_CONNECTION — compile and merge at the Executable level only. Results do not cache beyond the current Executable's execution. Each Executable issues its own fresh query, returning the most recent committed data.

VLOOKUP_RECORD / VLOOKUP_RECORD_CONNECTION — compile and merge across all Executables that belong to the same triggering object. The query is issued once and cached afterwards; subsequent invocations return the cached result without issuing separate queries. If a prior Executable has updated the looked-up records, the cached value will be stale.

When to use which. Use VLOOKUP / VLOOKUP_CONNECTION to issue fresh queries and get the most recent data. Use VLOOKUP_RECORD / VLOOKUP_RECORD_CONNECTION when minimizing SOQL usage matters and the cached value is acceptable.

ORDER BY behavior

DSP automatically appends an ORDER BY clause to the SOQL generated by VLOOKUP, VLOOKUP_CONNECTION, VLOOKUP_RECORD, and VLOOKUP_RECORD_CONNECTION, based on the selected fields, to improve lookup performance.

If you include your own ORDER BY in additional_criteria, DSP uses your clause as written and does not add its own. This lets you override the default ordering whenever is needed.