Tips and tricks

Any() function

It is possible for multiple records in the input table to be mapped to a single record in the current table. For example, a case has exactly one Case Type, which means that the Case Type value will be the same for every record within a case. 

You can use the any() function to pick one of the records within a case.

This is implemented in the expression any(records).Casetype.

This can be used in both Lookup expressions and Aggregate expressions.

Note: The record returned from the any() function is random. Only use this function if you are certain the value of the returned attribute is the same in all records in the current scope

Debugging lists

When developing Aggregate or Lookup expressions you might get an error message stating: “List encountered where a scalar was expected.” This error means that your expression returned multiple values and you need to select one of them, for example using the any() function. However, you should only use this function if it is certain that the value of the returned attribute is the same in all records in the current scope.

To test whether this is actually the case you can use the jointext() or listtojson() functions, which will display all the values in the list.

See the illustration below for an example of listtojson() to inspect the results of a Invoice type lookup.

The Value preview shows that each list consists of one value only. This implies that it is safe to use the any() function.