Microsoft Dynamics NAV 2009: Creating a Matrix Form

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics NAV 2009 Programming Cookbook

Microsoft Dynamics NAV 2009 Programming Cookbook — Save 50%

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

$35.99    $18.00
by Matt Traxinger | October 2010 | Cookbooks Microsoft

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

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
        Read more about the Microsoft Dynamics NAV 2009 Programming Cookbook      

(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:

  • Creating a Matrix Form
  • In the next part we will create a wizard-style form.


    Further resources on this subject:


    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
    Select your format and quantity:

    About the Author :


    Matt Traxinger

    Matt Traxinger graduated from the Georgia Institute of Technology in 2005 with a B.S. in Computer Science, specializing in Human Computer Interaction and Cognitive Science. After college he took a job as an add-on developer using a language he was unfamiliar with for a product he had never heard of: Navision. It turned out to be a great decision.

    In the years following Matt learned all areas of the product and earned Microsoft Certified Business Solutions Professional certifications in both technical and functional areas of NAV. He continues to stay current with new releases of the product and is certified in multiple areas for versions 4.0, 5.0, and 2009.

    Currently Matt works in Norcross, GA, for Canvas Systems, one of the largest resellers of new and refurbished computer equipment as an in-house NAV Developer and Business Analyst. He supports multiple offices in the United States as well as locations in the United Kingdom and the Netherlands.

    In his spare time you can find him on the online communities Mibuso.com and DynamicsUser.net under the name MattTrax, helping others learn more about the Dynamics NAV software.

    Books From Packt


    Microsoft Dynamics NAV 2009 Application Design
    Microsoft Dynamics NAV 2009 Application Design

    Microsoft Dynamics NAV Administration
    Microsoft Dynamics NAV Administration

    Microsoft Dynamics GP 2010 Cookbook
    Microsoft Dynamics GP 2010 Cookbook

    Microsoft Dynamics AX 2009 Development Cookbook
    Microsoft Dynamics AX 2009 Development Cookbook

    Microsoft Windows Workflow Foundation 4.0 Cookbook
    Microsoft Windows Workflow Foundation 4.0 Cookbook

    Applied Architecture Patterns on the Microsoft Platform
    Applied Architecture Patterns on the Microsoft Platform

    WCF 4.0 Multi-tier Services Development with LINQ to Entities
    WCF 4.0 Multi-tier Services Development with LINQ to Entities

    Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications
    Microsoft Windows Communication Foundation 4.0 Cookbook for Developing SOA Applications


    Code Download and Errata
    Packt Anytime, Anywhere
    Register Books
    Print Upgrades
    eBook Downloads
    Video Support
    Contact Us
    Awards Voting Nominations Previous Winners
    Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
    Resources
    Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software