Edit

VALUEINLARGE ER function

  • Article

The VALUEINLARGE function determines whether the specified input of the Int64 or Integer type matches any value of a specified item in the specified list. The function returns a Boolean value of TRUE if the specified input matches the result of running the specified expression for at least one record of the specified list. Otherwise, it returns a Boolean value of FALSE. To understand the difference with the VALUEIN function, see the Usage note section later in this article.

Syntax

VALUEINLARGE (input, list, list item expression)

Arguments

input: Field

The valid path of a data source item of the Record list type. The value of this item will be matched.

list: Record list

The valid path of a data source of the Record list data type.

list item expression: Expression

A valid conditional expression that either points to or contains a single field of the specified list that should be used for the matching.

Return values

Boolean

The resulting Boolean value.

Usage notes

When the specified input represents an Int64 or Integer type of a data source item, the call to which is translatable to a direct SQL statement, the specified list is converted to a temporary SQL table and matching is performed in the database by executing a single EXISTS JOIN query. Otherwise, this function works as the VALUEIN function.

When the specified input represents a data source item that is designed as an item other than Int64 and Integer type, an error occurs at design time informing you that the VALUEINLARGE function is not applicable for the configured ER expression.

When the VALUEINLARGE function expression is executed and more than one temporary table is used in scope of this execution, a runtime error occurs.

Example

You define the following data sources in your model mapping:

  • The In data source of the Table records type.
    • This data source refers to the Intrastat table.
    • The Cross-company option is set to No.
  • The InMemory data source of the Calculated field type.
    • This data source contains the expression WHERE (In, In.Port <> "").
  • The InFiltered data source of the Calculated field type.
    • This data source contains the expression FILTER (In, VALUEINLARGE(In.RecId, InMemory, InMemory.RecId).

When the data source InFiltered is called under the context of the company DEMF, a new temporary table is created in the application database, the collected in memory list of record identification codes are inserted to this table, and the following SQL statement is generated to return filtered records of the Intrastat table.

SELECT … from Intrastat T1
WHERE ((T1.PARTITION=?) AND (T1.DATAAREAID IN (N'DEMF'))) AND
EXISTS (SELECT 'x' FROM tempdb."DBO".? T2 WHERE ((T2.PARTITION=?) AND (T1.RecId=T2.RecId)))

Additional resources

Logical functions

VALUEIN functions