Edit

VALUEIN ER function

  • Article

The VALUEIN function determines whether the specified input matches any value of a specified item in the specified list. It 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.

Syntax

VALUEIN (input, list, list item expression)

Arguments

input: Field

The valid path of an item of a data source 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: Boolean

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

In general, the VALUEIN function is translated to a set of OR conditions. If the list of OR conditions is large and the maximum total length of an SQL statement might be exceeded, consider using the VALUEINLARGE function.

(input = list.item1.value) OR (input = list.item2.value) OR …

In some cases, it can be translated to a database SQL statement by using the EXISTS JOIN operator.

Note

The value that the VALUEIN function returns is used differently, depending on whether this function is used to specify the selection criteria for the FILTER function or the WHERE function.

Example 1

In your model mapping, you define the List data source of the Calculated field type. This data source contains the expression SPLIT ("a,b,c", ",").

When a data source is called, if it has been configured as the VALUEIN ("B", List, List.Value) expression, it returns TRUE. In this case, the VALUEIN function is translated to the following set of conditions: (("B" = "a") or ("B" = "b") or ("B" = "c")), where ("B" = "b") equals TRUE.

When a data source is called, if it has been configured as the VALUEIN ("B", List, LEFT(List.Value, 0)) expression, it returns FALSE. In this case, the VALUEIN function is translated to the following condition: ("B" = ""), which doesn't equal TRUE.

The upper limit for the number of characters in the text of such a condition is 32,768 characters. Therefore, you should not create data sources that might exceed this limit at runtime. If the limit is exceeded, the application stops running, and an exception is thrown. For example, this situation can occur if the data source is configured as WHERE (List1, VALUEIN (List1.ID, List2, List2.ID), and the List1 and List2 lists contain a large volume of records.

In some cases, the VALUEIN function is translated to a database statement by using the EXISTS JOIN operator. This behavior occurs when the FILTER function is used and the following conditions are met:

  • The ASK FOR QUERY option is turned off for the data source of the VALUEIN function that refers to the list of records. No additional conditions will be applied to this data source at runtime.
  • No nested expressions are configured for the data source of the VALUEIN function that refers to the list of records.
  • A list item of the VALUEIN function refers to a field of the specified data source, not to an expression or method of that data source.

Consider using this option instead of the WHERE function that is described earlier in this example.

Example 2

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 Port data source of the Table records type. This data source refers to the IntrastatPort table.

When a data source is called that has been configured as the FILTER (In, VALUEIN(In.Port, Port, Port.PortId) expression, the following SQL statement is generated to return filtered records of the Intrastat table.

select … from Intrastat
exists join TableId from IntrastatPort
where IntrastatPort.PortId = Intrastat.Port

For dataAreaId fields, the final SQL statement is generated by the using IN operator.

Example 3

You define the following data sources in your model mapping:

  • The Le data source of the Calculated field type. This data source contains the expression SPLIT ("DEMF,GBSI,USMF", ",").
  • The In data source of the Table records type. This data source refers to the Intrastat table, and the Cross-company option is turned on for it.

When a data source is called that has been configured as the FILTER (In, VALUEIN (In.dataAreaId, Le, Le.Value) expression, the final SQL statement contains the following condition.

Intrastat.dataAreaId IN ('DEMF', 'GBSI', 'USMF')

Additional resources

Logical functions

VALUEINLARGE functions