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"):