Formula designer in Electronic reporting (ER)

This topic explains how to use the formula designer in Electronic reporting (ER). When you design a format for a specific electronic document in ER, you can use formulas to transform data so that it meets the requirements for the document's fulfillment and formatting. These formulas resemble formulas in Microsoft Excel. Various types of functions are supported in the formulas: text, date and time, mathematical, logical, information, data type conversion, and other (business domain–specific functions).

Formula designer overview

ER supports the formula designer. Therefore, at design time, you can configure expressions that can be used for the following tasks at runtime:

  • Transform data that is received from a Microsoft Dynamics 365 for Finance and Operations database, and that should be entered in an ER data model that is designed to be a data source for ER formats. (For example, these transformations might include filtering, grouping, and data type conversion.)
  • Format data that must be sent to a generating electronic document in accordance with the layout and conditions of a specific ER format. (For example, the formatting might be done in accordance with the requested language or culture, or the encoding).
  • Control the process of creating electronic documents. (For example, the expressions can enable or disable the output of specific elements of the format, depending on processing data. They can also interrupt the document creation process or throw messages to users.)

You can open the Formula designer page when you perform any of the following actions:

  • Bind data source items to data model components.
  • Bind data source items to format components.
  • Complete maintenance of calculated fields that are part of data sources.
  • Define the visibility conditions for user input parameters.
  • Design a format's transformations.
  • Define the enabling conditions for the format's components.
  • Define the file names for the format's FILE components.
  • Define the conditions for process control validations.
  • Define the message text for process control validations.

Designing ER formulas

Data binding

The ER formula designer can be used to define an expression that transforms data that is received from data sources, so that the data can be entered in the data consumer at runtime:

  • From Finance and Operations data sources and runtime parameters to an ER data model
  • From an ER data model to an ER format
  • From Finance and Operations data sources and runtime parameters to an ER format

The following illustration shows the design of an expression of this type. In this example, the expression rounds the value of the Intrastat.AmountMST field the Intrastat table in Finance and Operations to two decimal places and then returns the rounded value.

Data binding

The following illustration shows how an expression of this type can be used. In this example, the result of the designed expression is entered in the Transaction.InvoicedAmount component of the Tax reporting model data model.

Data binding being used

At runtime, the designed formula, ROUND (Intrastat.AmountMST, 2), rounds the value of the AmountMST field for each record in the Intrastat table to two decimal places. It then enters the rounded value in the Transaction.InvoicedAmount component of the Tax reporting data model.

Data formatting

The ER formula designer can be used to define an expression that formats data that is received from data sources, so that the data can be sent as part of the generating electronic document. You might have formatting that must be applied as a typical rule that should be reused for a format. In this case, you can introduce that formatting one time in the format configuration, as a named transformation that has a formatting expression. This named transformation can then be linked to many format components where the output must be formatted according to the formatting expression that you created.

The following illustration shows the design of a transformation of this type. In this example, the TrimmedString transformation truncates incoming data of the String data type by removing leading and trailing spaces. It then returns the truncated string value.

Transformation

The following illustration shows how a transformation of this type can be used. In this example, several format components send text as output to the generating electronic document at runtime. All these format components refer to the TrimmedString transformation by name.

Transformation being used

When format components, such as the partyName component in the preceding illustration, refer to the TrimmedString transformation, the transformation sends text as output to the generating electronic document. This text doesn't include leading and trailing spaces.

If you have formatting that must be applied individually, you can introduce that formatting as an individual expression of a binding of a specific format component. The following illustration shows an expression of this type. In this example, the partyType format component is bound to the data source via an expression that converts incoming data from the Model.Company.RegistrationType field in the data source to uppercase text. The expression then sends that text as output to the electronic document.

Applying formatting to an individual component

Process flow control

The ER formula designer can be used to define expressions that control the process flow of generating electronic documents. You can perform the following tasks:

  • Define conditions that determine when a document creation process must be stopped.
  • Specify expressions that either create messages for the user about stopped processes or throw execution log messages about the continuing process of report generation.
  • Specify the file names of generating electronic documents, and control the conditions of their creation.

Each rule of the process flow control is designed as an individual validation. The following illustration shows a validation of this type. Here is an explanation of the configuration in this example:

  • The validation is evaluated when the INSTAT node is created during generation of the XML file.
  • If the list of transactions is empty, the validation stops the execution process and returns FALSE.
  • The validation returns an error message that includes the text of Finance and Operations label SYS70894 in the user's preferred language.

Validation

The ER formula designer can also be used to generate a file name for a generating electronic document and control the file creation process. The following illustration shows the design of a process flow control of this type. Here is an explanation of the configuration in this example:

  • The list of records from the model.Intrastat data source is divided into batches. Each batch contains up to 1,000 records.
  • The output creates a zip file that contains one file in XML format for every batch that was created.
  • An expression returns a file name for generating electronic documents by concatenating the file name and the file name extension. For the second batch and all subsequent batches, the file name contains the batch ID as a suffix.
  • An expression enables (by returning TRUE) the file creation process for batches that contain at least one record.

File control

Basic syntax

ER expressions can contain any or all of the following elements:

  • Constants
  • Operators
  • References
  • Paths
  • Functions

Constants

When you design expressions, you can use text and numeric constants (that is, values that aren't calculated). For example, the expression VALUE ("100") + 20 uses the numeric constant 20 and the string constant "100", and returns the numeric value 120. The ER formula designer supports escape sequences. Therefore, you can specify an expression string that should be handled differently. For example, the expression "Leo Tolstoy ""War and Peace"" Volume 1" returns the text string Leo Tolstoy "War and Peace" Volume 1.

Operators

The following table shows the arithmetic operators that you can use to do basic mathematical operations, such as addition, subtraction, multiplication, and division.

Operator Meaning Example
+ Addition 1+2
- Subtraction, negation 5-2, -1
* Multiplication 7*8
/ Division 9/3

The following table shows the comparison operators that are supported. You can use these operators to compare two values.

Operator Meaning Example
= Equal X=Y
> Greater than X>Y
< Less than X<Y
>= Greater than or equal to X>=Y
<= Less than or equal to X<=Y
<> Not equal to X<>Y

Additionally, you can use an ampersand (&) as a text concatenation operator. In this way, you can join, or concatenate, one or more text strings into a single piece of text.

Operator Meaning Example
& Concatenate "Nothing to print" & ": " & "no records found"
Operator precedence

The order that the parts of a compound expression are evaluated in is important. For example, the result of the expression 1 + 4 / 2 varies, depending on whether the addition operation or the division operation is done first. You can use parentheses to explicitly define how an expression is evaluated. For example, to indicate that the addition operation should be done first, you can change the preceding expression to (1 + 4) / 2. If you don't explicitly indicate the order of operations in an expression, the order is based on the default precedence that is assigned to the supported operators. The following table shows the precedence that is assigned to each operator. Operators that have a higher precedence (for example, 7) are evaluated before operators that have a lower precedence (for example, 1).

Precedence Operators Syntax
7 Grouping ( … )
6 Member access … . …
5 Function call … ( … )
4 Multiplicative … * …
… / …
3 Additive … + …
… - …
2 Comparison … < …
… <= …
… => …
… > …
… = …
… <> …
1 Separation … , …

If an expression includes multiple consecutive operators that have the same precedence, those operations are evaluated from left to right. For example, the expression 1 + 6 / 2 * 3 > 5 returns true. We recommend that you use parentheses to explicitly indicate the desired order of operations in expressions, so that the expressions are easier to read and maintain.

References

All data sources of the current ER component that are available during the design of an expression can be used as named references. (The current ER component can be either a model or a format.) For example, the current ER data model contains the ReportingDate data source, and this data source returns a value of the DATETIME data type. To correctly format that value in the generating document, you can reference the data source in the expression as DATETIMEFORMAT (ReportingDate, "dd-MM-yyyy").

All characters in the name of a referencing data source that don't represent a letter of the alphabet must be preceded by a single quotation mark ('). If the name of a referencing data source contains at least one symbol that doesn't represent a letter of the alphabet, the name must be enclosed in single quotation marks. (For example, these non-alphabetic symbols can be punctuation marks or other written symbols.) Here are some examples:

  • The Today's date & time data source must be referred to in an ER expression as 'Today''s date & time'.
  • The name() method of the Customers data source must be referred to in an ER expression as Customers.'name()'.

If the methods of Finance and Operations data sources have parameters, the following syntax is used to call those methods:

  • If the isLanguageRTL method of the System data source has an EN-US parameter of the String data type, this method must be referred to in an ER expression as System.'isLanguageRTL'("EN-US").
  • Quotation marks aren't required when a method name contains only alphanumeric symbols. However, they are required for a method of a table if the name includes brackets.

When the System data source is added to an ER mapping that refers to the Global Finance and Operations application class, the expression returns the Boolean value FALSE. The modified expression System.' isLanguageRTL'("AR") returns the Boolean value TRUE.

You can limit the way that values are passed to the parameters of this type of method:

  • Only constants can be passed to methods of this type. The values of the constants are defined at design time.
  • Only primitive (basic) data types are supported for parameters of this type. (The primitive data types are integer, real, Boolean, string, and so on.)

Paths

When an expression references a structured data source, you can use the path definition to select a specific primitive element of that data source. A dot character (.) is used to separate individual elements of a structured data source. For example, the current ER data model contains the InvoiceTransactions data source, and this data source returns a list of records. The InvoiceTransactions record structure contains the AmountDebit and AmountCredit fields, and both these fields return numeric values. Therefore, you can design the following expression to calculate the invoiced amount: InvoiceTransactions.AmountDebit - InvoiceTransactions.AmountCredit.

Functions

The next section describes the functions that can be used in ER expressions. All data sources of the expression context (the current ER data model or ER format) can be used as parameters of calling functions, in accordance with the list of arguments for calling functions. Constants can also be used as parameters of calling functions. For example, the current ER data model contains the InvoiceTransactions data source, and this data source returns a list of records. The InvoiceTransactions record structure contains the AmountDebit and AmountCredit fields, and both these fields return numeric values. Therefore, to calculate the invoiced amount, you can design the following expression that uses the built-in ER rounding function: ROUND (InvoiceTransactions.AmountDebit - InvoiceTransactions.AmountCredit, 2).

Supported functions

The following tables describe the data manipulation functions that you can use to design ER data models and ER reports. The list of functions isn't fixed. Developers can extend it. To see the list of functions that you can use, open the functions pane in the ER formula designer.

Date and time functions

Function Description Example
ADDDAYS (datetime, days) Add the specified number of days to the specified date/time value. ADDDAYS (NOW(), 7) returns the date and time seven days in the future.
DATETODATETIME (date) Convert the specified date value to a date/time value. DATETODATETIME (CompInfo. 'getCurrentDate()') returns the current Finance and Operations session date, December 24, 2015, as 12/24/2015 12:00:00 AM. In this example, CompInfo is an ER data source of the Finance and Operations/Table type and refers to the CompanyInfo table.
NOW () Return the current Finance and Operations application server date and time as a date/time value.
TODAY () Return the current Finance and Operations application server date as a date value.
NULLDATE () Return a null date value.
NULLDATETIME () Return a null date/time value.
DATETIMEFORMAT (datetime, format) Convert the specified date/time value to a string in the specified format. (For information about the supported formats, see standard and custom.) DATETIMEFORMAT (NOW(), "dd-MM-yyyy") returns the current Finance and Operations application server date, December 24, 2015, as "24-12-2015", based on the specified custom format.
DATETIMEFORMAT (datetime, format, culture) Convert the specified date/time value to a string in the specified format and culture. (For information about the supported formats, see standard and custom.) DATETIMEFORMAT (NOW(), "d", "de") returns the current Finance and Operations application server date, December 24, 2015, as "24.12.2015", based on the selected German culture.
SESSIONTODAY () Return the current Finance and Operations session date as a date value.
SESSIONNOW () Return the current Finance and Operations session date and time as a date/time value.
DATEFORMAT (date, format) Return a string representation of the specified date in the specified format. DATEFORMAT (SESSIONTODAY (), "dd-MM-yyyy") returns the current Finance and Operations session date, December 24, 2015, as "24-12-2015", based on the specified custom format.
DATEFORMAT (date, format, culture) Convert the specified date value to a string in the specified format and culture. (For information about the supported formats, see standard and custom.) DATETIMEFORMAT (SESSIONNOW (), "d", "de") returns the current Finance and Operations session date, December 24, 2015, as "24.12.2015", based on the selected German culture.
DAYOFYEAR (date) Return an integer representation of the number of days between January 1 and the specified date. DAYOFYEAR (DATEVALUE ("01-03-2016", "dd-MM-yyyy")) returns 61. DAYOFYEAR (DATEVALUE ("01-01-2016", "dd-MM-yyyy")) returns 1.
DAYS (date 1, date 2) Return the number of days between the first specified date and the second specified date. Return a positive value when the first date is later than the second date, return 0 (zero) when the first date equals the second date, or return a negative value when the first date is earlier than the second date. DAYS (TODAY (), DATEVALUE( DATETIMEFORMAT( ADDDAYS(NOW(), 1), "yyyyMMdd"), "yyyyMMdd")) returns -1.

Data conversion functions

Function Description Example
DATETODATETIME (date) Convert the specified date value to a date/time value. DATETODATETIME (CompInfo. 'getCurrentDate()') returns the current Finance and Operations session date, December 24, 2015, as 12/24/2015 12:00:00 AM. In this example, CompInfo is an ER data source of the Finance and Operations/Table type and refers to the CompanyInfo table.
DATEVALUE (string, format) Return a date representation of the specified string in the specified format. DATEVALUE ("21-Dec-2016", "dd-MMM-yyyy") returns the date December 21, 2016, based on specified custom format and the default application's EN-US culture.
DATEVALUE (string, format, culture) Return a date representation of the specified string in the specified format and culture. DATEVALUE ("21-Gen-2016", "dd-MMM-yyyy", "IT") returns the date January 21, 2016, based on the specified custom format and culture. However, DATEVALUE ("21-Gen-2016", "dd-MMM-yyyy", "EN-US") throws an exception to inform the user that the specified string isn't recognized as a valid date.
DATETIMEVALUE (string, format) Return a date/time representation of the specified string in the specified format. DATETIMEVALUE ("21-Dec-2016 02:55:00", "dd-MMM-yyyy hh:mm:ss") returns 2:55:00 AM on December 21, 2016, based on the specified custom format and the default application's EN-US culture.
DATETIMEVALUE (string, format, culture) Return a date/time representation of the specified string in the specified format and culture. DATETIMEVALUE ("21-Gen-2016 02:55:00", "dd-MMM-yyyy hh:mm:ss", "IT") returns 2:55:00 AM on December 21, 2016, based on the specified custom format and culture. However, DATETIMEVALUE ("21-Gen-2016 02:55:00", "dd-MMM-yyyy hh:mm:ss", "EN-US") throws an exception to inform the user that the specified string isn't recognized as a valid date/time.

List functions

Function Description Example
SPLIT (input, length) Split the specified input string into substrings, each of which has the specified length. Return the result as a new list. SPLIT ("abcd", 3) returns a new list that consists of two records that have a STRING field. The field in the first record contains the text "abc", and the field in the second record contains the text "d".
SPLIT (input, delimiter) Split the specified input string into substrings, based on the specified delimiter. SPLIT ("XAb aBy", "aB") returns a new list that consists of three records that have a STRING field. The field in the first record contains the text "X", the field in the second record contains the text " ", and the field in the third record contains the text "y". If the delimiter is empty, a new list is returned that consists of one record that has a STRING field that contains the input text. If the input is empty, a new empty list is returned. If either the input or the delimiter is unspecified (null), an application exception is thrown.
SPLITLIST (list, number) Split the specified list into batches, each of which contains the specified number of records. Return the result as a new list of batches that contains the following elements:
  • Batches as regular lists (Value component)
  • The current batch number (BatchNumber component)
In the following illustration, a Lines data source is created as a record list of three records. This list is divided into batches, each of which contains up to two records.

Data source that is divided into batches

The following illustration shows the designed format layout. In this format layout, bindings to the Lines data source are created to generate output in XML format. This output presents individual nodes for each batch and the records in it.

Format layout that has bindings to a data source

The following illustration shows the result when the designed format is run.

Result of running the format
LIST (record 1 [, record 2, …]) Return a new list that is created from the specified arguments. LIST (model.MainData, model.OtherData) returns an empty record, where the list of fields contains all fields of the MainData and OtherData record lists.
LISTJOIN (list 1, list 2, …) Return a joined list that is created from lists of specified arguments. LISTJOIN (SPLIT ("abc", 1), SPLIT ("def", 1)) returns a list of six records, where one field of the STRING data type contains single letters.
ISEMPTY (list) Return TRUE if the specified list contains no elements. Otherwise, return FALSE.
EMPTYLIST (list) Return an empty list by using the specified list as a source for the list structure. EMPTYLIST (SPLIT ("abc", 1)) returns a new empty list that has the same structure as the list that is returned by the SPLIT function.
FIRST (list) Return the first record of the specified list, if that record isn't empty. Otherwise, throw an exception.
FIRSTORNULL (list) Return the first record of the specified list, if that record isn't empty. Otherwise, return a null record.
LISTOFFIRSTITEM (list) Return a list that contains only the first item of the specified list.
ALLITEMS (path) This function runs as an in-memory selection. It returns a new flattened list that represents all items that match the specified path. The path must be defined as a valid data source path of a data source element of a record list data type. Data elements such as the path string and date should raise an error in the ER expression builder at design time. If you enter SPLIT("abcdef" , 2) as a data source (DS), COUNT( ALLITEMS (DS.Value)) returns 3.
ALLITEMSQUERY (path) This function runs as a joined SQL query. It returns a new flattened list that represents all items that match the specified path. The specified path must be defined as a valid data source path of a data source element of a record list data type, and it must contain at least one relation. Data elements such as the path string and date should raise an error in the ER expression builder at design time. Define the following data sources in your model mapping:
  • CustInv (Table records type), which refers to the CustInvoiceTable table
  • FilteredInv (Calculated field type), which contains the expression FILTER (CustInv, CustInv.InvoiceAccount = "US-001")
  • JourLines (Calculated field type), which contains the expression ALLITEMSQUERY (FilteredInv.'<Relations'.CustInvoiceJour.'<Relations'.CustInvoiceTrans)

When you run your model mapping to call the JourLines data source, the following SQL statement is run:

SELECT ... FROM CUSTINVOICETABLE T1 CROSS JOIN CUSTINVOICEJOUR T2 CROSS JOIN CUSTINVOICETRANS T3 WHERE...
ORDERBY (list [, expression 1, expression 2, …]) Return the specified list after it has been sorted according to the specified arguments. These arguments can be defined as expressions. If Vendor is configured as an ER data source that refers to the VendTable table, ORDERBY (Vendors, Vendors.'name()') returns a list of vendors that is sorted by name in ascending order.
REVERSE (list) Return the specified list in reversed sort order. If Vendor is configured as an ER data source that refers to the VendTable table, REVERSE (ORDERBY (Vendors, Vendors.'name()')) ) returns a list of vendors that is sorted by name in descending order.
WHERE (list, condition) Return the specified list after it has been filtered according to the specified condition. The specified condition is applied to the list in memory. In this way, the WHERE function differs from the FILTER function. If Vendor is configured as an ER data source that refers to the VendTable table, WHERE(Vendors, Vendors.VendGroup = "40") returns a list of just the vendors that belong to vendor group 40.
ENUMERATE (list) Return a new list that consists of enumerated records of the specified list, and that exposes the following elements:
  • Specified list records as regular lists (Value component)
  • The current record index (Number component)
In the following illustration, an Enumerated data source is created as an enumerated list of vendor records from the Vendors data source that refers to the VendTable table.

Enumerated data source

The following illustration shows the format. In this format, data bindings are created to generate output in XML format. This output presents individual vendors as enumerated nodes.

Format that has data bindings

The following illustration shows the result when the designed format is run.

Result of running the format
COUNT (list) Return the number of records in the specified list, if the list isn't empty. Otherwise, return 0 (zero). COUNT (SPLIT("abcd" , 3)) returns 2, because the SPLIT function creates a list that consists of two records.
LISTOFFIELDS (path) Return a record list that is created from an argument of one of the following types:
  • Model enumeration
  • Format enumeration
  • Container

The list that is created consists of records that have the following fields:

  • Name
  • Label
  • Description
At runtime, the Label and Description fields return values that are based on the format's language settings.
In the following illustration, an enumeration is introduced in a data model.

Enumeration in a model

The following illustration shows these details:

  • The model enumeration is inserted into a report as a data source.
  • An ER expression uses the model enumeration as a parameter of the LISTOFFIELDS function.
  • A data source of the record list type is inserted into a report by using the ER expression that is created.

Format

The following example shows the ER format elements that are bound to the data source of the record list type that was created by using the LISTOFFIELDS function.

Format design

The following illustration shows the result when the designed format is run.

Format output

[!NOTE] Based on the language settings of the parent FILE and FOLDER format elements, translated text for labels and descriptions is entered in the output of the ER format.
LISTOFFIELDS (path, language) Return a record list that is created from an argument, such as a model enumeration, a format enumeration, or a container. The list that is created consists of records that have the following fields:
  • Name
  • Label
  • Description
  • Is translated
At runtime, the Label and Description fields return values that are based on the format's language settings and the specified language. The Is translated field indicates that the Label field has been translated into the specified language.
For example, you use the Calculated field data source type to configure the enumType_de and enumType_deCH data sources for the enumType data model enumeration.
  • enumType_de = LISTOFFIELDS (enumType, "de")
  • enumType_deCH = LISTOFFIELDS (enumType, "de-CH")

In this case, you can use the following expression to get the label of the enumeration value in Swiss German, if this translation is available. If the Swiss German translation isn't available, the label is in German.

IF (NOT (enumType_deCH.IsTranslated), enumType_de.Label, enumType_deCH.Label)
STRINGJOIN (list, field name, delimiter) Return a string that consists of concatenated values of the specified field from the specified list. The values are separated by the specified delimiter. If you enter SPLIT("abc" , 1) as a data source (DS), STRINGJOIN (DS, DS.Value, "-") returns "a-b-c".
SPLITLISTBYLIMIT (list, limit value, limit source) Split the specified list into a new list of sub-lists, and return the result in record list content. The limit value parameter defines the value of the limit for splitting the original list. The limit source parameter defines the step that the total sum is increased on. The limit isn't applied to a single item of the original list if the limit source exceeds the defined limit. The following illustration shows a format.

Format

The following illustration shows the data sources that are used for the format.

Data sources

The following illustration shows the result when the format is run. In this case, the output is a flat list of commodity items.

Output

In the following illustrations, the same format has been adjusted so that it presents the list of commodity items in batches when a single batch must include commodities and the total weight should not exceed the limit of 9.

Adjusted format

Data sources for the adjusted format

The following illustration shows the result when the adjusted format is run.

Output of the adjusted format

[!NOTE] The limit isn't applied to the last item of the original list, because the value (11) of the limit source (weight) exceeds the defined limit (9). Use either the WHERE function or the Enabled expression of the corresponding format element to ignore (skip) sub-lists during report generation, as required.
FILTER (list, condition) Return the specified list after the query has been modified to filter for the specified condition. This function differs from the WHERE function, because the specified condition is applied to any ER data source of the Table records type at the database level. The list and condition can be defined by using tables and relations. If Vendor is configured as an ER data source that refers to the VendTable table, FILTER (Vendors, Vendors.VendGroup = "40") returns a list of just the vendors that belong to vendor group 40. If Vendor is configured as an ER data source that refers to the VendTable table, and if parmVendorBankGroup is configured as an ER data source that returns a value of the String data type, FILTER (Vendor.'<Relations'.VendBankAccount, Vendor.'<Relations'.VendBankAccount.BankGroupID = parmVendorBankGroup) returns a list of just the vendor accounts that belong to a specific bank group.

Logical functions

Function Description Example
CASE (expression, option 1, result 1 [, option 2, result 2] … [, default result]) Evaluate the specified expression value against the specified alternative options. Return the result of the option that equals the value of the expression. Otherwise, return the optional default result, if a default result is specified. (The default result is the last parameter that isn't preceded by an option.) CASE( DATETIMEFORMAT( NOW(), "MM"), "10", "WINTER", "11", "WINTER", "12", "WINTER", "") returns the string "WINTER" when the current Finance and Operations session date is between October and December. Otherwise, it returns a blank string.
IF (condition, value 1, value 2) Return the first specified value when the specified condition is met. Otherwise, return the second specified value. If value 1 and value 2 are records or record lists, the result has only the fields that exist in both lists. IF (1=2, "condition is met", "condition is not met") returns the string "condition is not met".
NOT (condition) Return the reversed logical value of the specified condition. NOT (TRUE) returns FALSE.
AND (condition 1[, condition 2, …]) Return TRUE if all specified conditions are true. Otherwise, return FALSE. AND (1=1, "a"="a") returns TRUE. AND (1=2, "a"="a") returns FALSE.
OR (condition 1[, condition 2, …]) Return FALSE if all specified conditions are false. Return TRUE if any specified condition is true. OR (1=2, "a"="a") returns TRUE.
VALUEIN (input, list, list item expression) Determine whether the specified input matches any value of an item in the specified list. Return TRUE if the specified input matches the result of running the specified expression for at least one record. Otherwise, return FALSE. The input parameter represents the path of a data source element. The value of this element will be matched. The list parameter represents the path of a data source element of the record list type as a list of records that contains an expression. The value of this element will be compared with the specified input. The list item expression argument represents an expression that either points to or contains a single field of the specified list that should be used for the matching. For examples, see the Examples: VALUEIN (input, list, list item expression) section that follows.

Examples: VALUEIN (input, list, list item expression)

In general, the VALUEIN function is translated to a set of OR conditions:

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

Example 1

You define the following data source in your model mapping: List (Calculated field type). This data source contains the expression SPLIT ("a,b,c", ",").

When a data source is called that is 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") is equal to TRUE

When a data source is called that is 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 isn't equal to TRUE

Note that 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 will stop running, and an exception will be 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 (not an expression or a method) of the specified data source.

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

Example 2

You define the following data sources in your model mapping:

  • In (Table records type), which refers to the Intrastat table
  • Port (Table records type), which refers to the IntrastatPort table

When a data source is called that is 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:

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

When a data source is called that is 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')

Mathematical functions

Function Description Example
ABS (number) Return the absolute value of the specified number. (In other words, return the number without its sign.) ABS (-1) returns 1.
POWER (number, power) Return the result of raising the specified positive number to the specified power. POWER (10, 2) returns 100.
NUMBERVALUE (string, decimal separator, digit grouping separator) Convert the specified string to a number. The specified decimal separator is used between the integer and fractional parts of a decimal number. The specified digit grouping separator is used as the thousands separator. NUMBERVALUE("1 234,56", ",", " ") returns the value 1234.56.
VALUE (string) Convert the specified string to a number. Commas and dot characters (.) are considered decimal separators, and a leading hyphen (-) is used as a negative sign. Throw an exception if the specified string contains other non-numeric characters. VALUE ("1 234,56") throws an exception.
ROUND (number, decimals) Return the specified number after it has been rounded to the specified number of decimal places:
  • If the value of the decimals parameter is more than 0 (zero), the specified number is rounded to that many decimal places.
  • If the value of the decimals parameter is 0 (zero), the specified number is rounded to the nearest integer.
  • If the value of the decimals parameter is less than 0 (zero), the specified number is rounded to the left of the decimal point.
ROUND (1200.767, 2) rounds to two decimal places and returns 1200.77. ROUND (1200.767, -3) rounds to the nearest multiple of 1,000 and returns 1000.
ROUNDDOWN (number, decimals) Return the specified number after it has been rounded down to the specified number of decimal places.
[!NOTE] This function behaves like ROUND, but it always rounds the specified number down (toward zero).
ROUNDDOWN (1200.767, 2) rounds down to two decimal places and returns 1200.76. ROUNDDOWN (1700.767, -3) rounds down to the nearest multiple of 1,000 and returns 1000.
ROUNDUP (number, decimals) Return the specified number after it has been rounded up to the specified number of decimal places.
[!NOTE] This function behaves like ROUND, but it always rounds the specified number up (away from zero).
ROUNDUP (1200.763, 2) rounds up to two decimal places and returns 1200.77. ROUNDUP (1200.767, -3) rounds up to the nearest multiple of 1,000 and returns 2000.

Data conversion functions

Function Description Example
VALUE (string) Convert the specified string to a number. Commas and dot characters (.) are considered decimal separators, and a leading hyphen (-) is used as a negative sign. Throw an exception if the specified string contains other non-numeric characters. VALUE ("1 234,56") throws an exception.
NUMBERVALUE (string, decimal separator, digit grouping separator) Convert the specified string to a number. The specified decimal separator is used between the integer and fractional parts of a decimal number. The specified digit grouping separator is used as the thousands separator. NUMBERVALUE("1 234,56", ",", " ") returns 1234.56.
INTVALUE (string) Return an integer representation of the specified string. Any decimal places are truncated. INTVALUE ("100.77") returns 100.
INTVALUE (number) Return an integer representation of the specified number. Any decimal places are truncated. INTVALUE (-100.77) returns -100.
INT64VALUE (string) Return an int64 representation of the specified string. Any decimal places are truncated. INT64VALUE ("22565422744") returns 22565422744.
INT64VALUE (number) Return an int64 representation of the specified number. Any decimal places are truncated. INT64VALUE (22565422744.00) returns 22565422744.

Record functions

Function Description Example
NULLCONTAINER (list) Return a null record that has the same structure as the specified record list or record.
[!NOTE] This function is obsolete. Use EMPTYRECORD instead.
NULLCONTAINER (SPLIT ("abc", 1)) returns a new empty record that has the same structure as the list that is returned by the SPLIT function.
EMPTYRECORD (record) Return a null record that has the same structure as the specified record list or record.
[!NOTE] A null record is a record where all fields have an empty value. An empty value is 0 (zero) for numbers, an empty string for strings, and so on.
EMPTYRECORD (SPLIT ("abc", 1)) returns a new empty record that has the same structure as the list that is returned by the SPLIT function.

Text functions

Function Description Example
UPPER (string) Return the specified string after it has been converted to uppercase letters. UPPER("Sample") returns "SAMPLE".
LOWER (string) Return the specified string after it has been converted to lowercase letters. LOWER ("Sample") returns "sample".
LEFT (string, number of characters) Return the specified number of characters from the start of the specified string. LEFT ("Sample", 3) returns "Sam".
RIGHT (string, number of characters) Return the specified number of characters from the end of the specified string. RIGHT ("Sample", 3) returns "ple".
MID (string, starting position, number of characters) Return the specified number of characters from the specified string, starting at the specified position. MID ("Sample", 2, 3) returns "amp".
LEN (string) Return the number of characters in the specified string. LEN ("Sample") returns 6.
CHAR (number) Return the string of characters that is referenced by the specified Unicode number. CHAR (255) returns "ÿ".
[!NOTE] The string that this function returns depends on the encoding that is selected in the parent FILE format element. For the list of supported encodings, see Encoding class.
CONCATENATE (string 1 [, string 2, …]) Return all specified text strings after they have been joined into one string. CONCATENATE ("abc", "def") returns "abcdef".
[!NOTE] The expression "abc" & "def" also returns "abcdef".
TRANSLATE (string, pattern, replacement) Return the specified string after all occurrences of the characters in the specified pattern string have been replaced by the characters at the corresponding position in the specified replacement string. TRANSLATE ("abcdef", "cd", "GH") replaces the pattern "cd" with the string "GH" and returns "abGHef".
REPLACE (string, pattern, replacement, regular expression flag) When the specified regular expression flag parameter is true, return the specified string after it has been modified by applying the regular expression that is specified as the pattern argument for this function. This expression is used to find characters that must be replaced. Characters of the specified replacement argument are used to replace characters that are found. When the specified regular expression flag parameter is false, this function behaves like TRANSLATE. REPLACE ("+1 923 456 4971", "[^0-9]", "", true) applies a regular expression that removes all non-numeric symbols, and returns "19234564971". REPLACE ("abcdef", "cd", "GH", false) replaces the pattern "cd" with the string "GH" and returns "abGHef".
TEXT (input) Return the specified input after it has been converted to a text string that is formatted according to the server locale settings of the current Finance and Operations instance. For values of the real type, the string conversion is limited to two decimal places. If the server locale of the Finance and Operations instance is defined as EN-US, TEXT (NOW ()) returns the current Finance and Operations session date, December 17, 2015, as the text string "12/17/2015 07:59:23 AM". TEXT (1/3) returns "0.33".
FORMAT (string 1, string 2[, string 3, …]) Return the specified string after it has been formatted by substituting any occurrences of %N with the nth argument. The arguments are strings. If an argument isn't provided for a parameter, the parameter is returned as "%N" in the string. For values of the real type, the string conversion is limited to two decimal places. In the following illustration, the PaymentModel data source returns the list of customer records via the Customer component and the processing date value via the ProcessingDate field.

PaymentModel data source

In the ER format that is designed to generate an electronic file for selected customers, PaymentModel is selected as a data source and controls the process flow. An exception is thrown to inform the user when a selected customer is stopped for the date when the report is processed. The formula that is designed for this type of processing control can use the following resources:

  • Finance and Operations label SYS70894, which has the following text:
    • For the EN-US language: "Nothing to print"
    • For the DE language: "Nichts zu drucken"
  • Finance and Operations label SYS18389, which has the following text:
    • For the EN-US language: "Customer %1 is stopped for %2."
    • For the DE language: "Debitor '%1' wird für %2 gesperrt."

Here is the formula that can be designed:

FORMAT (CONCATENATE (@"SYS70894", ". ", @"SYS18389"), model.Customer.Name, DATETIMEFORMAT (model.ProcessingDate, "d"))

If a report is processed for the Litware Retail customer on December 17, 2015, in the EN-US culture and the EN-US language, this formula returns the following text, which can be presented to the user as an exception message:

"Nothing to print. Customer Litware Retail is stopped for 12/17/2015."

If the same report is processed for the Litware Retail customer on December 17, 2015, in the DE culture and the DE language, the formula returns the following text, which uses a different date format:

"Nichts zu drucken. Debitor 'Litware Retail' wird für 17.12.2015 gesperrt."

[!NOTE] The following syntax is applied in ER formulas for labels:
  • For labels from Finance and Operations resources: @"X", where X is the label ID in the Application Object Tree (AOT)
  • For labels that reside in ER configurations: @"GER_LABEL:X", where X is the label ID in the ER configuration
NUMBERFORMAT (number, format) Return a string representation of the specified number in the specified format. (For information about the supported formats, see standard and custom.) The context that this function is run in determines the culture that is used to format numbers. For the EN-US culture, NUMBERFORMAT (0.45, "p") returns "45.00 %". NUMBERFORMAT (10.45, "#") returns "10".
NUMERALSTOTEXT (number, language, currency, print currency name flag, decimal points) Return the specified number after it has been spelled out (converted to text strings) in the specified language. The language code is optional. When it's defined as an empty string, the language code for the running context is used. (The language code for the running context is defined for a generating folder or file.) The currency code is also optional. When it's defined as an empty string, the company currency is used.
[!NOTE] The print currency name flag and decimal points parameters are analyzed only for the following language codes: CS, ET, HU, LT, LV, PL, and RU. Additionally, the print currency name flag parameter is analyzed only for Finance and Operations companies where the country's or region's context supports declension of currency names.
NUMERALSTOTEXT (1234.56, "EN", "", false, 2) returns "One Thousand Two Hundred Thirty Four and 56". NUMERALSTOTEXT (120, "PL", "", false, 0) returns "Sto dwadzieścia". NUMERALSTOTEXT (120.21, "RU", "EUR", true, 2) returns "Сто двадцать евро 21 евроцент".
PADLEFT (string, length, padding chars) Return a string of the specified length, where the start of the specified string is padded with the specified characters. PADLEFT ("1234", 10, " ") returns the text string "      1234".
TRIM (string) Return the specified text string after leading and trailing spaces have been truncated, and after multiple spaces between words have been removed. TRIM ("     Sample     text     ") returns "Sample text".
GETENUMVALUEBYNAME (enumeration data source path, enumeration value label text) Return a value of the specified enumeration data source, based on the specified text of the enumeration label. In the following illustration, the ReportDirection enumeration is introduced in a data model. Note that labels are defined for enumeration values.

Available values for data model enumeration

The following illustration shows these details:

  • The ReportDirection model enumeration is inserted into a report as a data source, $Direction.
  • An ER expression, $IsArrivals, is designed to use the model enumeration as a parameter of this function. The value of this expression is TRUE.
Example of data model enumeration
GUIDVALUE (input) Convert the specified input of the String data type to a data item of the GUID data type.
[!NOTE] To do a conversion in the opposite direction (that is, to convert specified input of the GUID data type to a data item of the String data type), you can use the TEXT() function.
You define the following data sources in your model mapping:
  • myID (Calculated field type), which contains the expression GUIDVALUE("AF5CCDAC-F728-4609-8C8B- A4B30B0C0AA0")
  • Users (Table records type), which refers to the UserInfo table
When these data sources are defined, you can use an expression such as FILTER (Users, Users.objectId = myID) to filter the UserInfo table by the objectId field of the GUID data type.
JSONVALUE (id, path) Parse data in JavaScript Object Notation (JSON) format that is accessed by the specified path to extract a scalar value that is based on the specified ID. The data source $JsonField contains the following data in JSON format: {"BuildNumber":"7.3.1234.1", "KeyThumbprint":"7366E"}. For this data source, JSONVALUE ( "BuildNumber", $JsonField) returns the value 7.3.1234.1 of the String data type.

Data conversion functions

Function Description Example
TEXT (input) Return the specified input after it has been converted to a text string that is formatted according to the server locale settings of the current Finance and Operations instance. For values of the real type, the string conversion is limited to two decimal places. If the server locale of the Finance and Operations instance is defined as EN-US, TEXT (NOW ()) returns the current Finance and Operations session date, December 17, 2015, as the text string "12/17/2015 07:59:23 AM". TEXT (1/3) returns "0.33".
QRCODE (string) Return a Quick Response Code (QR code) image in base64 binary format for the specified string. QRCODE ("Sample text") returns U2FtcGxlIHRleHQ=.

Data collection functions

Function Description Example
FORMATELEMENTNAME () Return the name of the current format's element. Return an empty string when the Collect output details flag of the current files is turned off. To learn more about how to use this function, see the ER Use data of format output for counting and summing task guide, which is part of the Acquire/Develop IT service/solution components business process.
SUMIFS (key string for summing, criteria range1 string, criteria value1 string [, criteria range2 string, criteria value2 string, …]) Return the sum of values that was collected for XML nodes (where the name is defined as a key) when the format was run, and that satisfies the specified conditions (pairs of ranges and values). Return a 0 (zero) value when the Collect output details flag of the current files is turned off.
SUMIF (key string for summing, criteria range string, criteria value string) Return the sum of values that was collected for XML nodes (where the name is defined as a key) when the format was run, and that satisfies the specified condition (a range and value). Return a 0 (zero) value when the Collect output details flag of the current files is turned off.
COUNTIFS (criteria range1 string, criteria value1 string [, criteria range2 string, criteria value2 string, …]) Return the number of XML nodes that was collected when the format was run, and that satisfies the specified conditions (pairs of ranges and values). Return a 0 (zero) value when the Collect output details flag of the current files is turned off.
COUNTIF (criteria range string, criteria value string) Return the number of XML nodes that was collected when the format was run, and that satisfies the specified condition (a range and value). Return a 0 (zero) value the flag Collect output details flag of the current files is turned off.
COLLECTEDLIST (criteria range1 string, criteria value1 string [, criteria range2 string, criteria value2 string, …]) Return the list of values that was collected for XML nodes when the format was run, and that satisfies the specified conditions (a range and value). Return an empty list when the Collect output details flag of the current files is turned off.

Other (business domain–specific) functions

Function Description Example
CONVERTCURRENCY (amount, source currency, target currency, date, company) Convert the specified monetary amount from the specified source currency to the specified target currency by using the settings of the specified Finance and Operations company on the specified date. CONVERTCURRENCY (1, "EUR", "USD", TODAY(), "DEMF") returns the equivalent of one euro in US dollars on the current session date, based on settings for the DEMF company.
ROUNDAMOUNT (number, decimals, round rule) Round the specified amount to the specified number of decimal places according to the specified rounding rule.
[!NOTE] The rounding rule must be specified as a value of the Finance and Operations RoundOffType enumeration.
If the model.RoundOff parameter is set to Downward, ROUNDAMOUNT (1000.787, 2, model.RoundOff) returns the value 1000.78. If the model.RoundOff parameter is set to either Normal or Rounding-up, ROUNDAMOUNT (1000.787, 2, model.RoundOff) returns the value 1000.79.
CURCredRef (digits) Return a creditor reference, based on the digits of the specified invoice number. CURCredRef ("VEND-200002") returns "2200002".
MOD_97 (digits) Return a creditor reference as a MOD97 expression, based on the digits of the specified invoice number. MOD_97 ("VEND-200002") returns "20000285".
ISOCredRef (digits) Return an International Organization for Standardization (ISO) creditor reference, based on the digits and alphabetic symbols of the specified invoice number.
[!NOTE] To eliminate symbols from alphabets that aren't ISO-compliant, the input parameter must be translated before it's passed to this function.
ISOCredRef ("VEND-200002") returns "RF23VEND-200002".
CN_GBT_AdditionalDimensionID (string, number) Get the specified additional financial dimension ID. In the string parameter, dimensions are represented as IDs that are separated by commas. The number parameter defines the sequence code of the requested dimension in the string. CN_GBT_AdditionalDimensionID ("AA,BB,CC,DD,EE,FF,GG,HH",3) returns "CC".
GetCurrentCompany () Return a text representation of the code for the legal entity (company) that a user is currently signed in to. GETCURRENTCOMPANY () returns USMF for a user who is signed in to the Contoso Entertainment System USA company in Finance and Operations.
CH_BANK_MOD_10 (digits) Return a creditor reference as an MOD10 expression, based on the digits of the specified invoice number. CH_BANK_MOD_10 ("VEND-200002") returns 3.
FA_SUM (fixed asset code, value model code, start date, end date) Return the prepared data container of the fixed asset amount for the specified period. FA_SUM ("COMP-000001", "Current", Date1, Date2) returns the prepared data container of fixed asset "COMP-000001" that has the "Current" value model for a period from Date1 to Date2.
FA_BALANCE (fixed asset code, value model code, reporting year, reporting date) Return the prepared data container of the fixed asset balance. The reporting year must be specified as a value of the AssetYear enumeration in Finance and Operations. FA_SUM ("COMP-000001", "Current", AxEnumAssetYear.ThisYear, SESSIONTODAY ()) returns the prepared data container of balances for fixed asset "COMP-000001" that has the "Current" value model on the current Finance and Operations session date.
TABLENAME2ID (string) Return an integer representation of a table ID for the specified table name. TABLENAME2ID ("Intrastat") returns 1510.
ISVALIDCHARACTERISO7064 (string) Return the Boolean value TRUE when the specified string represents a valid international bank account number (IBAN). Otherwise, return the Boolean value FALSE. ISVALIDCHARACTERISO7064 ("AT61 1904 3002 3457 3201") returns TRUE. ISVALIDCHARACTERISO7064 ("AT61") returns FALSE.
NUMSEQVALUE (number sequence code, scope, scope id) Return the new generated value of a number sequence, based on the specified number sequence code, scope, and scope ID. The scope must be specified as a value of the ERExpressionNumberSequenceScopeType enumeration (Shared, Legal entity, or Company). For the Shared scope, specify an empty string as the scope ID. For the Company and Legal entity scopes, specify the company code as the scope ID. For the Company and Legal entity scopes, if you specify an empty string as the scope ID, the current company code is used. You define the following data sources in your model mapping:
  • enumScope (Dynamics 365 for Operations enumeration type), which refers to the ERExpressionNumberSequenceScopeType enumeration
  • NumSeq (Calculated field type), which contains the expression NUMSEQVALUE ("Gene_1", enumScope.Company, "")
When the NumSeq data source is called, it returns the new generated value of the Gene_1 number sequence that has been configured for the company that supplies the context that the ER format is run under.
NUMSEQVALUE (number sequence code) Return the new generated value of a number sequence, based on the specified number sequence, the Company scope, and (as the scope ID) the code of the company that supplies the context that is ER format is run under. You define the following data source in your model mapping: NumSeq (Calculated field type). This data source contains the expression NUMSEQVALUE ("Gene_1"). When the NumSeq data source is called, it returns the new generated value of the Gene_1 number sequence that has been configured for the company that supplies the context that the ER format is run under.
NUMSEQVALUE (number sequence record ID) Return the new generated value of a number sequence, based on the specified number sequence record ID. You define the following data sources in your model mapping:
  • LedgerParms (Table type), which refers to the LedgerParameters table
  • NumSeq (Calculated field type), which contains the expression NUMSEQVALUE (LedgerParameters.'numRefJournalNum()'.NumberSequenceId)
When the NumSeq data source is called, it returns the new generated value of the number sequence that has been configured in the General ledger parameters for the company that supplies the context that the ER format is run under. This number sequence uniquely identifies journals and acts as a batch number that links the transactions together.

Functions list extension

ER lets you extend the list of functions that are used in ER expressions. Some engineering effort is required. For detailed information, see Extending the list of Electronic reporting functions.

Additional resources