Edit

Share via

VALUEIN ER function

Note

Community interest groups have now moved from Yammer to Microsoft Viva Engage. To join a Viva Engage community and take part in the latest discussions, fill out the Request access to Finance and Operations Viva Engage Community form and choose the community you want to join.

The VALUEIN function checks whether the specified input matches any value of a specified item in the specified list. It returns 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 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 is 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 you use for the matching.

Return values

Boolean

The resulting Boolean value.

Usage notes

In general, the VALUEIN function translates 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, the function translates to a database SQL statement by using the EXISTS JOIN operator.

Note

Depending on whether you use the VALUEIN function to specify the selection criteria for the FILTER function or the WHERE function, the value that the VALUEIN function returns is used differently.

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 you call a data source, if you configure it as the VALUEIN ("B", List, List.Value) expression, it returns TRUE. In this case, the VALUEIN function translates to the following set of conditions: (("B" = "a") or ("B" = "b") or ("B" = "c")), where ("B" = "b") equals TRUE.

When you call a data source, if you configure it as the VALUEIN ("B", List, LEFT(List.Value, 0)) expression, it returns FALSE. In this case, the VALUEIN function translates 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, don't 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 translates 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 are 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 you call a data source that you configured as the FILTER (In, VALUEIN(In.Port, Port, Port.PortId)) expression, the system generates the following SQL statement 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 uses the 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 you call a data source that you 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

  • Last updated on