VLOOKUP_CONNECTION

Description

Performs a dynamic lookup on a Salesforce object through a connection and returns the specified field value (including relational fields up to 5 levels), from the first matching record.

Syntax

VLOOKUP_CONNECTION(connection_name, object_name, return_field_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.
return_field_name	String	Yes	The name of the field whose value will be returned.
…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_CONNECTION("Salesforce_Prod",
                   "Opportunity",
                   "Amount", 
                   "Account.Source_System_Id__c",
                    Source_System_Id__c)
-- Returns the Amount from the first Opportunity in the "Salesforce_Prod" org where Account.Source_System_Id__c matches the current record’s Source_System_Id__c.

VLOOKUP_CONNECTION("Salesforce_Prod", 
                   "Opportunity", 
                   "Amount", 
                   "AccountId", Id, 
                   "OwnerId", $User.Id, 
                   "FiscalYear = 2024")
-- Returns the Amount from the first Opportunity in the "Salesforce_Prod" org where:
-- AccountId matches the current record’s Id,
-- OwnerId matches the current user’s Id,
-- and FiscalYear = 2024.

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.