Matrices
Matrices are lookup tables that provide dynamic values based on filter conditions. They are used in BOM Calculation Template expressions to retrieve values that depend on multiple input criteria, such as material weight based on thickness and grade.
What is a matrix
A matrix is a structured data table with:
- Filter columns (up to 5) that define the lookup criteria
- Value columns (up to 5) that contain the result values
- Lines where each row represents a combination of filter conditions and corresponding values
During calculation, the system evaluates the filter conditions against the provided input values and returns the matching result.
How to access matrices
From the template card
- Open the BOM Calculation Template Card.
- Choose the Matrices action to open the matrix list for this template.
From the matrix list
- Choose the
icon, enter Matrix BOM Templates, and then choose the related link.
Matrix BOM Template Card
The Matrix BOM Template Card defines the structure and data of a matrix.
Header fields
| Field | Description |
|---|---|
| Code | Unique code that identifies the matrix. Used in expressions with the MAT[] function. |
| Description | Description of the matrix. |
Filter setup
The Filters section defines up to 5 filter columns. Each filter has a caption and a data type:
| Field | Description |
|---|---|
| Filter 1-5 Caption | Caption for the filter column, displayed as the column header in the lines grid. |
| Filter Type 1-5 | Data type of the filter: Number or Text. Determines how filter matching is performed. |
Value setup
The Values section defines up to 5 value columns:
| Field | Description |
|---|---|
| Value 1-5 Caption | Caption for the value column. |
| Value Type 1-5 | Data type of the value: Number or Text. |
Lines
Each line in the matrix represents one row of filter conditions and corresponding values:
| Field | Description |
|---|---|
| Filter 1-5 | Filter condition for this row. Supports exact values and Business Central filter syntax (e.g., 100..200 for ranges, * for wildcards). |
| Value 1-5 | The result value returned when all filter conditions match. |
How to create a matrix
- Open or create a Matrix BOM Template Card.
- Enter a Code and Description.
- In the Filters section, define the filter columns:
- Set a Filter Caption (e.g., "Thickness" or "Grade").
- Set the Filter Type to Number or Text.
- In the Values section, define the value columns:
- Set a Value Caption (e.g., "Weight per m²").
- Set the Value Type to Number or Text.
- On the Lines section, add rows with filter conditions and values.
The MAT[] function
Matrices are referenced in expressions using the MAT[] function:
MAT["MatrixCode", FilterValue1, FilterValue2, ...]
The function looks up the matrix with the given code, matches the filter values against the filter columns, and returns the corresponding value from the first value column.
To retrieve a value from a specific value column, use:
MAT["MatrixCode", FilterValue1, FilterValue2, ..., ValueColumnIndex]
Filter matching
The system evaluates each line's filter conditions:
- Exact match: The filter value equals the input value exactly.
- Range match: The filter uses Business Central range syntax (e.g.,
100..200matches any value between 100 and 200). - Wildcard match: Text filters can use
*for partial matching. - Empty filter: An empty filter column matches all values.
The first matching line (in line number order) provides the result.
Practical example
A matrix to look up the weight per square meter of corrugated board based on flute type and basis weight:
Matrix setup:
- Code:
BOARD_WEIGHT - Filter 1 Caption: "Flute Type", Filter Type: Text
- Filter 2 Caption: "Basis Weight", Filter Type: Number
- Value 1 Caption: "Weight per m²", Value Type: Number
Matrix lines:
| Flute Type | Basis Weight | Weight per m² |
|---|---|---|
| B | 100..150 | 0.55 |
| B | 151..200 | 0.68 |
| C | 100..150 | 0.62 |
| C | 151..200 | 0.75 |
| BC | 100..150 | 0.90 |
| BC | 151..200 | 1.05 |
Usage in a variable expression:
BoardWeight = MAT["BOARD_WEIGHT", PROP["FluteType"], PROP["BasisWeight"]]
This looks up the weight per square meter based on the user's selected flute type and basis weight.
Tip
Combine matrices with properties for dynamic lookups. Define properties for the lookup criteria (such as material type or quality grade) and use them as filter values in the MAT[] function.