This article series by Matt Traxinger, author of Microsoft Dynamics NAV 2009 Programming Cookbook, shows you how to create displays that will allow your users to interact with the data.

A matrix shows information from multiple tables at the same time. This article will show you how to create a matrix that shows the amount a customer has spent on specific items.

Microsoft Dynamics NAV 2009 Programming Cookbook

Build better business applications with NAV Write NAV programs to do everything from finding data in a table to integration with an instant messenger client Develop your own .NET code to perform tasks that NAV cannot handle on its own Work with SQL Server to create better integration between NAV and other systems Learn to use the new features of the NAV 2009 Role Tailored Client Easy-to-read recipes with detailed explanations and images Maximize your learning with short tutorials that tell you exactly what you need to know without all of the fluff

(For more resources on Microsoft Dynamics, see here)

The reader would benefit by reading the previous article on Microsoft Dynamics NAV 2009: Designing Forms

How to do it...

1. Add a global function CalculateData that returns a text variable.
2. Add a global function ColumnHeader that returns a text variable.
   

   

3. Add a matrix box to the form.
4. Set the following properties on the matrix box control:
   

   

   

5. Set the following property on the form:
   

   

   

6. Add the No. and Name fields to the left-hand side of the matrix box using the Field menu.
7. Add a textbox to the right-hand side of the matrix box.
8. Set the following property on the textbox:
   

   

9. Add a textbox as a column header above that textbox.
10. Set the following property on the textbox:
    

    

    

11. Add the following code to the ColumnHeader function.
    

    EXIT(CurrForm.MatrixBox.MatrixRec."No.");

12. Add the following local variables to the CalculateData function:
    

    

13. Add the following code to the CalculateData function
    

    ItemLedgerEntry.RESET;
    
    ItemLedgerEntry.SETCURRENTKEY("Source Type", "Source No.",
                        "Item No.", "Variant Code", "Posting Date");
    
    ItemLedgerEntry.SETRANGE("Source Type", ItemLedgerEntry."Source
                                                  Type"::Customer);
    
    ItemLedgerEntry.SETRANGE("Source No.", "No.");
    
    ItemLedgerEntry.SETRANGE("Item No.",
                               CurrForm.MatrixBox.MatrixRec."No.");
    
    ItemLedgerEntry.SETRANGE("Entry Type", ItemLedgerEntry."Entry
                                                      Type"::Sale);
    
    IF ItemLedgerEntry.FINDSET THEN
      REPEAT
        ItemLedgerEntry.CALCFIELDS("Sales Amount (Actual)");
        TotalSales := TotalSales + ItemLedgerEntry."Sales Amount
                                                         (Actual)";
      UNTIL ItemLedgerEntry.NEXT = 0;
    
    EXIT(FORMAT(TotalSales));

14. After running the resulting form, you should see something similar to the following screenshot:
    

    

How it works...

A matrix form consists of two tables and some calculation based on those two tables. One set of records runs vertically along the left-hand side of the matrix box while the other set runs horizontally across the top. A grid is displayed on the rest of the form displaying a calculated value. We'll examine each of these pieces individually.

We begin by creating a normal form that is bound to the Customer table. For this special form we add a matrix box control. The left-hand side operates exactly the same as a standard list form. It will display all of the customers and there will be a scrollbar to look through the list. As we don't want the user to change anything on this form, we set the Editable property of the matrix box to No. We will also have to write code that refers to this control so we must give it a name.

Also, the matrix box itself operates on a table. In this case it is the Item table. As there is so much data stored in a table, we have to tell the control what we want to see. That's why we add a textbox as a column header to the top of the form. The source expression for that textbox is the ColumnHeader method. Let's take a look at the code there.

EXIT(CurrForm.MatrixBox.MatrixRec."No.");

CurrForm is the current form. MatrixBox is the value in the name property of our matrix box control. MatrixRec is the record in the matrix box that we are referring to (just like rec on a normal form). Finally, No. is the field from the MatrixSourceTable property(in this case the Item No). So our column headers will just be the Item Number from the Item table.

Lastly, we have to tell the form how to calculate the data we want to see. We add another textbox to the form and give it a source expression of CalculateData, which is a function on our form. This function could return anything, but in our case it returns the amount a customer has spent on a specific item. Let's take a look at the important code that combines the data from both tables.

ItemLedgerEntry.SETRANGE("Source No.", "No.");
ItemLedgerEntry.SETRANGE("Item No.",
             CurrForm.MatrixBox.MatrixRec."No.");

The Item Ledger Entry table already has fields that refer to the Customer table and to the Item table. The first filter uses the No. field from the source table (Customer). The second filter determines the current Item Number from the matrix box and uses it. Later in the function, a number is calculated and returned as a text variable.

Summary

In this part of the article series we covered:

In the next part we will create a wizard-style form.

Further resources on this subject:

• Microsoft Dynamics NAV 2009: Designing Forms
• Microsoft Dynamics NAV 2009: Creating a Wizard-style Form

Microsoft Dynamics NAV 2009 Programming Cookbook

Build better business applications with Microsoft Dynamics NAV 2009 with this book and eBook

Published: October 2010
eBook Price: $35.99
Book Price: $59.99
See more

close

Select your format and quantity:

Book 01234567891011121314151617181920

eBook 01234567891011121314151617181920

About the Author :

Books From Packt