Skip to content

Latest commit

 

History

History
67 lines (44 loc) · 2.52 KB

er-functions-list-allitemsquery.md

File metadata and controls

67 lines (44 loc) · 2.52 KB
Raw
title description author ms.author ms.topic ms.date ms.reviewer audience ms.search.region ms.search.validFrom ms.search.form ms.dyn365.ops.version ms.assetid
ALLITEMSQUERY ER function
Learn about how the ALLITEMSQUERY Electronic reporting (ER) function is used, including syntax strings, arguments, return values, usage notes, and examples.
kfend
filatovm
article
12/12/2019
johnmichalak
IT Pro
Global
2016-02-28
ERDataModelDesigner, ERExpressionDesignerFormula, ERMappedFormatDesigner, ERModelMappingDesigner
AX 7.0.0
24223e13-727a-4be6-a22d-4d427f504ac9

ALLITEMSQUERY ER function

[!include banner]

The ALLITEMSQUERY function runs as a joined SQL query. It returns a new flattened Record list value that consists of a list of records that represent all items that match the specified path.

Syntax

ALLITEMSQUERY (path)

Arguments

path: Record list

The valid path of a data source of the Record list data type. It must contain at least one relation.

Return values

Record list

The resulting list of records.

Usage notes

The specified path must be defined as a valid data source path of a data source element of the Record list data type. It must also contain at least one relation. Data elements such as the path String and Date should raise an error in the Electronic reporting (ER) expression builder at design time.

When this function is applied to data sources of the Record list data type that refer to an application object that can be directly called by using SQL (for example, an table, entity, or query), it runs as a joined SQL query. Otherwise, it runs in memory as the ALLITEMS function.

Example

You define the following data sources in your model mapping:

  • A CustInv data source of the Table records type that refers to the CustInvoiceTable table
  • A FilteredInv data source of the Calculated field type that contains the expression FILTER (CustInv, CustInv.InvoiceAccount = "US-001")
  • A JourLines of the Calculated field type that contains the expression ALLITEMSQUERY ( FilteredInv.'<Relations'.CustInvoiceJour.'<Relations'.CustInvoiceTrans)

When you run the 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...

Additional resources

List functions

[!INCLUDEfooter-include]