VLOOKUP_ALL

Description

Queries the specified object and returns the designated fields (plus the record Id and lookup field) from all records that match one or more field-value pairs, with an optional additional filtering clause.

Syntax

VLOOKUP_ALL(target_object_name, return_field_names, ...[matching_field_name, matching_field_value], additional_clause_optional) -> List<Map<String, ANY>>

Parameters

Parameter	Data Type	Required	Description
target_object_name	String	Yes	The API name of the object to query.
return_field_names	String	Yes	A comma-separated string of field API names to return from each 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_ALL("Opportunity", 
            "Name, Amount", 
            "AccountId", Id)
-- Returns the Name and Amount from every Opportunity where AccountId matches the current record's Id. 
-- Each record also includes Id and the lookup field (AccountId).

Tips

SOQL queries have a governor limit of 50,000 rows per transaction. Because VLOOKUP_ALL returns every matching record, refine your matching criteria or additional_clause_optional to keep the result set within this limit.

Note

Compilation, caching, and freshness

Both function families compile and merge invocations into consolidated SOQL queries — calls that share the same Connection, 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.