VLOOKUP_SOURCE

Description

Returns the value of the specified field (including relationship fields up to 5 levels) from the first record in the Source Connection that matches all of the given field-value pairs.

Syntax

VLOOKUP_SOURCE(source_object_name, return_field_name, ...[matching_field_name, matching_field_value], additional_clause_optional) -> Object

Parameters

Parameter	Data Type	Required	Description
source_object_name	String	Yes	The API name of the object in the Source Connection to query.
return_field_name	String	Yes	The API name of the field to return from the matching record.
…matching_field_name	String	Yes	The API 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 SOQL criteria for restricting or ordering the matching results.

Example

VLOOKUP_SOURCE("Opportunity", 
               "Amount", 
               "AccountId", Id, 
               "StageName = 'Closed Won' ORDER BY CloseDate DESC")
-- Returns the Amount of the most recent Closed Won Opportunity in the Source Connection whose AccountId matches the current Account's Id.

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_clause_optional (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_ALL / VLOOKUP_CONNECTION / VLOOKUP_SOURCE — 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_ALL / VLOOKUP_CONNECTION / VLOOKUP_SOURCE 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_ALL, VLOOKUP_CONNECTION, VLOOKUP_SOURCE, VLOOKUP_RECORD, and VLOOKUP_RECORD_CONNECTION, based on the selected fields, to improve lookup performance.

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