Search icon CANCEL
Subscription
0
Cart icon
Your Cart (0 item)
Close icon
You have no products in your basket yet
Arrow left icon
Explore Products
Best Sellers
New Releases
Books
Videos
Audiobooks
Learning Hub
Free Learning
Arrow right icon
Arrow up icon
GO TO TOP
Extending Microsoft Dynamics NAV 2016 Cookbook

You're reading from   Extending Microsoft Dynamics NAV 2016 Cookbook Extend Dynamics NAV 2016 to win the business world

Arrow left icon
Product type Paperback
Published in Jan 2017
Publisher Packt
ISBN-13 9781786460608
Length 458 pages
Edition 1st Edition
Languages
Arrow right icon
Author (1):
Arrow left icon
Alexander Drogin Alexander Drogin
Author Profile Icon Alexander Drogin
Alexander Drogin
Arrow right icon
View More author details
Toc

Table of Contents (11) Chapters Close

Preface 1. Writing Basic C/AL Code FREE CHAPTER 2. Advanced C/AL Development 3. Reporting and Data Analysis 4. .NET Interoperability in C/AL 5. Extending C/AL with COM Components 6. SharePoint Integration 7. Control Add-ins 8. Web Services 9. Events and Extension Packages 10. PowerShell

Accessing the database in C/AL

Microsoft Dynamics NAV is an information system, and its primary purpose is to collect, store, organize, and present data. Therefore C/AL has a rich set of functions for data access and manipulation.

The next example will present a set of basic functions to read data from the NAV database, filter and search records in a table, and calculate aggregated values based on database records.

In this example, suppose we want to calculate the total amount in all open sales orders and invoices for a certain customer in a specified period.

How to do it...

  1. In the NAV Object Designer, create a new codeunit object.
  2. Open the codeunit you just created in code designer, position it in the OnRun trigger, and open the local declarations window (C/AL Locals). Declare the following local variables:

    Name

    DataType

    Subtype

    SalesLine

    Record

    Sales Line

    StartingDate

    Date

    EndingDate

    Date

  3. Close the local variables window and declare a global text constant in the C/AL Globals window:

    Name

    ConstValue

    SalesAmountMsg

    Total amount in sales documents: %1

  4. Return to the code editor and type the function code:
           StartingDate := CALCDATE('<-1M>',WORKDATE); 
           EndingDate := WORKDATE; 
     
           SalesLine.SETRANGE("Sell-to Customer No.",'10000'); 
           SalesLine.SETFILTER( 
             "Document Type",'%1|%2', 
             SalesLine."Document Type"::Order, 
             SalesLine."Document Type"::Invoice); 
           SalesLine.SETRANGE( 
             "Posting Date",StartingDate,EndingDate); 
           SalesLine.CALCSUMS("Line Amount"); 
           MESSAGE(SalesAmountMsg,SalesLine."Line Amount"); 
    
  5. Save the changes, then close the code editor and run the codeunit.

How it works...

A record is a complex data type. Variable declared as record refers to a table in the database. A variable contains a single table record and can move forward and backward through the recordset. A C/AL record resembles an object in object-oriented languages, although they are not exactly the same. You can call record methods and read fields using dot notation.

For example below are valid statements with the Customer record variable:

Customer.Name := 'New Customer'; 
IF Customer.Balance <= 0 THEN 
  MESSAGE 

The variable we just declared refers to the table Sales Line, which stores all open sales documents lines.

Since we want to calculate the sales amount in a certain period, first of all we need to define the date range for the calculation.

The first line in the code example finds the starting date of the period. In this calculation we refer to the system-defined global variable WORKDATE. If you are an experienced NAV user, you know what a workdate is; this is the default date for all documents created in the system. It does not always match the calendar date, so in the application code we use WORKDATE as the pivot date. Another system variable TODAY stores the actual calendar date, but it is used much less frequently than workdate.

Workdate is the last date of the period we want to analyze. To find the first date, use the CALCDATE function. It calculates a date based on the formula and the reference date. CALCDATE('<-1M>',WORKDATE) means that the resulting date will be one month earlier than the workdate. In the NAV 9.0 demo database workdate is 25.01.2017, so the result of this CALCDATE will be 25.12.2016.

The next line sets a filter on the SalesLine table. Filtering is used in C/AL to search for records corresponding to given criteria. There are two functions to apply filters to a table: SETFILTER and SETRANGE. Both take the field name to which the filter is applied, as the first parameter.

SETRANGE can filter all values within a given range or a single value. In the code example we use it to filter sales lines where the customer code is '10000'. Then we apply one more filter on the Posting Date field to filter out all dates less than StartingDate and greater than EndingDate.

Another filter is applied on the Document Type field:

SalesLine.SETFILTER( 
  "Document Type",'%1|%2', 
  SalesLine."Document Type"::Order, 
  SalesLine."Document Type"::Invoice); 

We want to see only invoices and orders in the final result, and we can combine these two values in a filter with the SETFILTER function. '%1|%2' is a combination of two placeholders that will be replaced with actual filter values in the runtime.

The last database statement in this example is the CALCSUMS function. SETRANGE itself does not change the state of the record variable - it only prepares filters for the following records search or calculation. Now CALCSUMS will calculate the result based on the record filters. It will find the sum of the Line Amount field in all records within the filtered range.

Only sales lines in which all filtering conditions are satisfied will be taken into account:

  • Customer No is '10000'
  • Document Type is Order or Invoice
  • Posting Date is between 25.12.2016 and 25.01.2017

Finally, we will show the result as a message with the MESSAGE function. Placeholders "%1" in the message text will be replaced with the second parameter of the function (SalesLine."Line Amount"):

How it works...

You have been reading a chapter from
Extending Microsoft Dynamics NAV 2016 Cookbook
Published in: Jan 2017
Publisher: Packt
ISBN-13: 9781786460608
Register for a free Packt account to unlock a world of extra content!
A free Packt account unlocks extra newsletters, articles, discounted offers, and much more. Start advancing your knowledge today.
Unlock this book and the full library FREE for 7 days
Get unlimited access to 7000+ expert-authored eBooks and videos courses covering every tech area you can think of
Renews at $19.99/month. Cancel anytime
Banner background image