RSMUS.com
Many times in AX, users would like to manage their items in a matrix or dimensional grid. I’ve used this technique many times in the past. It is only now that I’ve decided to write about it. I would like to see if I can perfect it so that it can easily be implemented for any scenario. This walkthrough will be broken down into chunks so that you can continue to see progress as the product is developed.
Phase I:
It begins with a simple temporary table (look here for a great write up on how to use temp tables with forms and reports). The primary index, which allows duplicates of this table will be ItemId. In addition, we’ll need the three generic data types: string, real, and date (about 20 fields each). Name each field numerically so that they are all unique and easy to identify.
jsMatrix
Note: the screenshot shows the table is partially built with only five real numbers. The final version of the table will be much larger. The following job can help create all the generic fields in the table above. Before running the job, start with a new table, jsMatrix, and drag the EDT ItemID as the first field. In AX 2012, this will prompt the user to automatically create the index and relationship to InventTable. The most important property setting for this table is to set it as temporary. In AX 2012, this can be done by changing the table setting to TempDB.
Job to build the temp table
Now that we have our temporary table built, we’ll need to create a method to populate it with data. For now, we will keep it simple to test, and will modify it after we get the form and/or report working. Add this method to the table:
With the temporary table in place, we can simply add it to a form and/or report. We will add in more functionality later.
Override the init method on the form:
Override the fetch method on the report:
And voila! You have all you need for the generic matrix form. It should look something like this:
Here are some examples of forms that I’ve replaced with this new functionality.
Original Purchase Order Create Line Form:
New Purchase Order Create Line Matrix:
Notice how I’ve added filters on the top for warehouse and ItemId.
Original Purchase Order
New Purchase Order using the matrix:
With the report it’s easy to calculate subtotals and totals either by row or column.
Original Sales Forecast form
New Forecast Grid using the matrix
