The Dynamic Fields Step allows adding or removing fields in a Query Object at runtime based on dynamic metadata. It lets you pivot data from the input source and dynamically fetch metadata for field properties.

This step is used when data needs to be manipulated at runtime based on metadata that will enable a data source. The primary functionalities include pivoting data and dynamically fetching field properties such as field name, caption, and data type.

Dynamic Metadata

Dynamic metadata refers to the structure of the result set that will be determined and applied at runtime. This is typically achieved by defining the metadata using an SQL query or another data source before fetching the input data.

Key features:

• SQL Execution: The SQL or other data source that defines the metadata is executed just before fetching the input data source.

• Field Attribute Mapping: Once the metadata is retrieved, it is mapped to the Query Object field properties. The most important mappings include:

  • Field ID

  • Field Name

  • Caption

  • Data Type

These attributes define how the fields in the metadata will be mapped to the Query Object’s fields for further use.

Pivoting

Pivoting is a technique used to convert highly normalized, name-value paired data into a flat tabular format. This is particularly useful when working with databases or data sources where data is structured in a way that makes querying difficult due to normalization.

Pivoting involves two key components:

1. Pivot Columns:

   • This specifies which column contains the Field ID and which column contains the Value.

2. Select Grouping:

   • This defines the grouping fields. Grouping is done on specified fields to convert normalized data into a flat table format. Essentially, the data is aggregated and restructured for easier analysis and reporting.