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.
In the NAV Object Designer, create a new codeunit object.
Open the codeunit you just created in code designer, position it in the
OnRuntrigger, and open the local declarations window (C/AL Locals). Declare the following local variables:Name
DataType
Subtype
SalesLineRecord
Sales Line
StartingDateDate
EndingDateDate
Close the local variables window and declare a global text constant in the C/AL Globals window:
Name
ConstValue
SalesAmountMsgTotal amount in sales documents: %1
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");Save the changes, then close the code editor and run the codeunit.
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"):
