In this chapter, we will cover:
Retrieving the system date and time
Retrieving the work date
Determining the day, month, and year from a given date
Converting a value to a formatted string
Creating an array
Creating an Option variable
Converting a string to another data type
Manipulating string contents
Using date formulas to calculate dates
Simple data types are building blocks for everything you will program. C/AL contains the same data types that you will find in most other programming languages: Booleans, integers, decimals, dates, and strings. There are of course more than just these five, but majority of your programming will revolve around using these types of variables.
As a developer, your job is to build business logic that will manipulate the data that is input by users. This ensures that the data stored in tables is meaningful. Most of this data will be of one of the following data types. NAV is, after all, a financial system at heart. At its most basic level, it cares about three things: "How much money?" (decimal), "What was it for?" (string), and "When did it happen?" (date).
The recipes you will find in this section may not be the most interesting, but are valuable. The functionality described here is used throughout the system. As such, each example in this chapter is accompanied by actual code from base NAV objects in order to better illustrate how they can be used.
There are many instances when it is necessary to obtain the current date and time from the user's system. This recipe will show you how to get that information.
Create a new codeunit from Object Designer.
Write the following code in the
OnRun
trigger of the codeunit:MESSAGE('Todays Date: %1\Current Time: %2', TODAY, TIME);
Save and close the codeunit.
When you run the codeunit you should see a window similar to the following screenshot:
The TODAY
keyword returns the date from the system clock on the client computer. In Windows, the current system time is usually located at the bottom-right corner of the task bar. The same holds true for the system time which is returned by the TIME
keyword.
The actual date and time returned depends on which version of the NAV client you are using. In the RoleTailored client, the date and time come from the NAV server. In the Classic client, the date and time come directly from the client computer and users will be able to manipulate the system clock to their advantage if they need to. An example could be a time clock application where a user can clock in, change the system time to eight hours later, clock out, and change it back to the original time.
You can also retrieve the system date and time, all at once, using the CURRENTDATETIME
function. The date and time can be extracted using the DT2DATE
and DT2TIME
functions respectively.
Note
For a complete list of date functions, search the C/SIDE Reference Guide under the Help menu for date and time functions.
The ChangeLog
is a base NAV module that allows you to track changes to specific fields in tables. The following code can be found in Codeunit 423, Change Log Management, in the InsertLogEntry()
method.
ChangeLogEntry.INIT; system timeevents, loggingChangeLogEntry."Date and Time" := CURRENTDATETIME; ChangeLogEntry.Time := DT2TIME(ChangeLogEntry."Date and Time");
Here, instead of using the WORKDATE
function, we use the CURRENTDATETIME
function and then extract the time using the DT2TIME
function. The system designers could have just done the following setup:
ChangeLogEntry.Date := TODAY; ChangeLogEntry.Time := TIME;
The advantage of using CURRENTDATETIME
over TODAY
and TIME
is minimal. CURRENTDATETIME
makes one request to the system, while the second method makes two. It is possible that another operation or thread on the client machine could take over between retrieving the date and time from the computer, however, this is very unlikely. The operations could also take place right before and after midnight, generating some very strange data. The requirements for your modification will determine which method is suits best, but generally CURRENTDATETIME
is the correct method to use.
The work date is an essential part of the NAV system. This recipe will show you how to determine what that date is, as well as when and where you should use it.
The work date is a date internal to the NAV system. This date is returned using the WORKDATE
keyword. It can be changed at any time by going to Tools | Work Date.
It is important to understand the difference between the NAV work date and the computer system date. They should be used in specific circumstances. When performing general work in the system, you should almost always use the WORKDATE
keyword. In cases where you need to log information and the exact date or time when an action occurred, you should use TODAY
and TIME
or CURRENTDATETIME
.
The following code can be found in table 36, Sales Header, in the InitRecord()
method:
IF "Document Type" IN ["Document Type"::Order,"Document Type"::Invoice,"Document Type"::Quote] THEN BEGIN methodInitRecord()"Shipment Date" := WORKDATE; "Order Date" := WORKDATE; END; IF "Document Type" = "Document Type"::"Return Order" THEN "Order Date" := WORKDATE; IF NOT ("Document Type" IN ["Document Type"::"Blanket Order","Document Type"::Quote]) AND ("Posting Date" = 0D) THEN "Posting Date" := WORKDATE; IF SalesSetup."Default Posting Date" = SalesSetup."Default Posting Date"::"No Date" THEN "Posting Date" := 0D; "Document Date" := WORKDATE;
It is common to create and call an InitRecord()
method from a table's OnInsert
trigger especially for document-style tables. Unlike with the InitValue
property for fields in a table, fields here are filled in based on conditional logic. More importantly, validation can be performed to ensure data integrity.
Looking at this snippet of code, we can see that every date is filled in using the WORKDATE
keyword, and not using TODAY
. This is so that a user can easily create records that are pre-dated or post-dated.
Sometimes it is necessary to retrieve only a part of a date. NAV has built-in functions to do just that. We will show you how to use it in this recipe.
Create a new codeunit from Object Designer.
Add the following global variables:
Name
Type
Day
Integer
Month
Integer
Year
Integer
Write the following code in the
OnRun
trigger of the codeunit:Day := Date2DMY(TODAY, 1); Month := Date2DMY(TODAY, 2); Year := Date2DMY(TODAY, 3); MESSAGE('Day: %1\Month: %2\Year: %3', Day, Month, Year);
Save and close the codeunit.
When you run the codeunit you should see a window like the following screenshot:
The Date2DMY
function is a basic feature of NAV. The first parameter is a date variable. This parameter can be retrieved from the system using TODAY
or WORKDATE
, a hard-coded date such as 01312010D
, or a field from a table such as Sales Header or Order Date.
The second parameter is an integer that tells the function which part of the date to return. This number can be 1
, 2
, or 3
and corresponds to the day, month, and year (DMY) respectively.
NAV has a similar function called Date2DWY
. It will return the week of the year instead of the month if 2
is passed as the second parameter.
Codeunit 5616, Depreciation Calculation, contains functions to calculate depreciation based on start and end dates. In order to correctly calculate these values, you must know some details such as the number of days between two dates and whether or not any of those days is a leap day. It is with these types of operations that date functions like DATE2DMY
are extremely useful. Have a look at the function DeprDays365
in this codeunit.
StartingYear := DATE2DMY(StartingDate,3); EndingYear := DATE2DMY(EndingDate,3); LeapDays := 0; IF (DATE2DMY(StartingDate,1) = 29) AND (DATE2DMY(StartingDate,2) = 2) AND (DATE2DMY(EndingDate,1) = 29) AND (DATE2DMY(EndingDate,2) = 2) THEN LeapDays := -1; ActualYear := StartingYear; WHILE ActualYear <= EndingYear DO BEGIN LeapDate := (DMY2DATE(28,2,ActualYear) + 1); IF DATE2DMY(LeapDate,1) = 29 THEN BEGIN IF (LeapDate >= StartingDate) AND (LeapDate <= EndingDate) THEN LeapDays := LeapDays + 1; END; ActualYear := ActualYear + 1; END; EXIT((EndingDate - StartingDate) + 1 - LeapDays);
There will be many occasions when you will need to display information in a certain way or display multiple variable types on a single line. The FORMAT
function will help you change almost any data type into a string that can be manipulated in any way you see fit.
Create a new codeunit from Object Designer.
Add the following global variables:
Name
Type
Length
FormattedDate
Text
30
Add the following code to the
OnRun
trigger:FormattedDate := FORMAT(TODAY, 0, '<Month Text> <Day,2>, <Year4>'); MESSAGE('Today is %1', FormattedDate);
Save and close the codeunit.
When you run the codeunit you should see a window similar to the following :
The FORMAT
function takes one to three parameters. The first parameter is required and can be of almost any type: date, time, integer, decimal, and so on. This parameter is returned as a string.
The second parameter is the length of the string to be returned. A default zero means that the entire string will be returned. A positive number tells the function to return a string of exactly that length, and a negative number returns a string no larger than that length.
There are two options for the third and final parameter. One is a number, representing a predefined format you want to use for the string and the other is a literal string. In the example, we used the actual format string. The text contained in brackets (< >) will be parsed and replaced with the data in the first parameter.
There are many predefined formats for dates. The examples listed in the following table are taken from the C/SIDE Reference Guide in the Help menu of the NAV client. Search for "Format Property" to find more information.
Date |
Format |
Example |
---|---|---|
|
|
05-04-03 |
|
|
05-04-03 |
|
|
050403D |
|
|
03-04-05 |
|
|
5. April 2003 |
|
|
050403 |
|
|
030405 |
|
|
5. Apr 2003 |
|
|
2003-04-05 |
You will often need to create filters on dates or other simple data types. Usually these filters are not just for a single value. For example, a date filter for all values between January 1st, 2010 and January 31st, 2010 would look like 010110..013110
. Because ".." is a string, and you cannot concatenate it with two date variables. Instead, you will have to convert those dates into strings and then place the filters together.
Take the CreateAccountingDateFilter
function from codeunit 358, DateFilter-Calc. It creates date filters based on accounting periods for the exact scenario we are describing.
AccountingPeriod.RESET; IF FiscalYear THEN AccountingPeriod.SETRANGE("New Fiscal Year",TRUE); AccountingPeriod."Starting Date" := Date; AccountingPeriod.FIND('=<>'); IF AccountingPeriod."Starting Date" > Date THEN NextStep := NextStep - 1; IF NextStep <> 0 THEN IF AccountingPeriod.NEXT(NextStep) <> NextStep THEN BEGIN IF NextStep < 0 THEN Filter := '..' + FORMAT( AccountingPeriod."Starting Date" - 1) ELSE Filter := FORMAT(AccountingPeriod."Starting Date") + '..' + FORMAT(12319999D); Name := '...'; EXIT; END; StartDate := AccountingPeriod."Starting Date"; IF FiscalYear THEN Name := STRSUBSTNO(Text000,FORMAT(DATE2DMY(StartDate,3))) ELSE Name := AccountingPeriod.Name; IF AccountingPeriod.NEXT <> 0 THEN Filter := FORMAT(StartDate) + '..' + FORMAT(AccountingPeriod."Starting Date" - 1) ELSE BEGIN Filter := FORMAT(StartDate) + '..' + FORMAT(12319999D); Name := Name + '...'; END;
Creating multiple variables to store related information can be time consuming. It leads to more code and hence, more work. Using an array to store related and similar type of information can speed up development and lead to much more manageable code. This recipe will show you how to create and access array elements.
Create a new codeunit in Object Designer.
Add the following global variables:
Name
Type
i
Integer
IntArray
Integer
With the cursor on that variable, click on View | Properties ( Shift +F4).
Set the following property:
Property
Value
Dimensions
10
In the
OnRun
trigger add the following code:FOR i := 1 TO ARRAYLEN(IntArray) DO BEGIN IntArray[i] := i; MESSAGE('IntArray[%1] = %2', i, IntArray[i]); END;
When you run the codeunit you will see ten windows, one after the other, similar to the following screenshot:
An array is a single variable that holds multiple values. The values are accessed using an integer index. The index is passed within square brackets ([]).
NAV provides several functions to work with arrays. ARRAYLEN
returns the number of dimensions of the array. COPYARRAY
will copy all of the values from one array into a new array variable. For a complete list of functions, search the C/SIDE Reference Guide under the Help menu for "Array Functions".
Open codeunit 365, Format Address. Notice the first function, FormatAddr
, has a parameter which is an array. This is the basic function that all of the address formats use. It is rather long, so we will discuss only a few parts of it here.
This first section determines how the address should be presented based on the country of the user. Variables are initialized depending on which line of the address should certain information appear. The variables will be the indexes of our array.
CASE Country."Contact Address Format" OF arrayaddress creating, Format Address usedCountry."Contact Address Format"::First: BEGIN NameLineNo := 2; Name2LineNo := 3; ContLineNo := 1; AddrLineNo := 4; Addr2LineNo := 5; PostCodeCityLineNo := 6; CountyLineNo := 7; CountryLineNo := 8; END;
Then we will fill in the array values in the following manner:
AddrArray[NameLineNo] := Name; AddrArray[Name2LineNo] := Name2; AddrArray[AddrLineNo] := Addr; AddrArray[Addr2LineNo] := Addr2;
Scroll down and take a look at all the other functions. You'll see that they all take in an array as the first parameter. It is always a text array of length 90 with 8 dimensions. These are the functions you will call when you want to format an address. To use this codeunit correctly, you will need to create an empty array with the specifications listed before and pass it to the correct function. Your array will be populated with the appropriately formatted address data.
If you need to force the user to select a value from a pre-defined list then an Option is the way to go. This recipe explains how to create an Option variable and access each of its values.
Add the following global variables:
Name
Type
ColorOption
Option
Set the following property on the variable:
Property
Value
OptionString
None,Red,Green,Blue
Add the following code to the
OnRun
trigger of your codeunit:ColorOption := ColorOption::Red; CASE ColorOption OF ColorOption::None: MESSAGE('No Color Selected'); ColorOption::Red: MESSAGE('Red'); ColorOption::Green: MESSAGE('Green'); ColorOption::Blue: MESSAGE('Blue'); END;
When you run the codeunit you should see a window similar to the following screenshot:
An Option is a field or variable that stores one value from a selectable list. In a form, this list will appear as a drop-down from which the user can select a value. The list of options is stored as a comma-separated string in the OptionString
property.
These values are accessed using the variable_name::option_name
syntax. The first line of the example assigns one of the possible values (Red) to the variable. Then we use a CASE
statement to determine which of the values was selected.
You can also access possible options in other ways. In a database, an Option is stored as an integer. Each Option corresponds to a specific number, starting with the number 1. In this case None=1, Red=2, Green=3, and Blue=4. You could write this code to perform the safe actions:
ColorOption := ColorOption::"1"; option variableworkingCASE ColorOption OF ColorOption::None: MESSAGE('No Color Selected'); ColorOption::Red: MESSAGE('Red'); ColorOption::Green: MESSAGE('Green'); ColorOption::Blue: MESSAGE('Blue'); END;
To reduce your development time, you can also use a shorthand notation to access the Option values. Again, the following code is exactly the same as that above:
ColorOption := ColorOption::R; CASE ColorOption OF ColorOption::None: MESSAGE('No Color Selected'); ColorOption::Red: MESSAGE('Red'); ColorOption::Green: MESSAGE('Green'); ColorOption::Blue: MESSAGE('Blue'); END;
When you close, save, and reopen the codeunit, the Option values will automatically be filled in for you. That is, both of these examples will look exactly like the first example once it has been saved and reopened. It is always best to write the code exactly as you want it to appear.
Option fields are prevalent throughout the NAV system, but most commonly on documents. In NAV, many documents share the same table. For example, sales quotes, orders, invoices, and return orders are all based on the Sales Header table. In order to distinguish between the types, there is an Option field called Document Type. Design table 36, Sales Header, to see the available options for this field.
Now, design codeunit 80, Sales-Post. Examine the OnRun
trigger. Early in the function, you will see the following code:
CASE "Document Type" OF "Document Type"::Order: Receive := FALSE; "Document Type"::Invoice: BEGIN Ship := TRUE; Invoice := TRUE; Receive := FALSE; END; "Document Type"::"Return Order": Ship := FALSE; "Document Type"::"Credit Memo": BEGIN Ship := FALSE; Invoice := TRUE; Receive := TRUE; END; END;
This is a common example of how Options are used in NAV. You can scroll through the codeunit to find more examples.
Sometimes a string representation isn't enough. In order to perform certain actions, you need your data to be in a certain format. This recipe will show you how to change that data into a format that you can use.
Create a new codeunit from Object Designer.
Add the following global variables:
Name
Type
Length
DateText
Text
30
DateValue
Date
Write the following code in the
OnRun
trigger:DateText := '01/01/2010'; EVALUATE(DateValue, DateText); MESSAGE('Date: %1', DateValue);
Save and close the codeunit.
When you run the codeunit you should see a window similar to the following screenshot:
The EVALUATE()
function takes in two parameters. The first is a variable of the type that you want your value to be converted into. This could be date, time, boolean, integer, or any other simple data type. This parameter is passed by reference, meaning that the result of the function is stored in that variable. There is no need to do a manual assign using the :=
syntax.
The second parameter is the string which you need to convert. This text is usually stored in a field or variable, but can also be hard coded.
For a list of all of the functions related to text variables, search for "Text Data Type" in the C/SIDE Reference Guide under the Help menu.
EVALUATE()
returns a boolean value when executed. If the conversion is successful, it returns TRUE
or 1
; otherwise, it returns FALSE
or 0
. If the function returns FALSE
, an error will be generated. If you wish to display the standard system error, you can leave the code as it is, but if you want to handle the error yourself, you must make the following changes:
DateText := '01/01/2010'; IF NOT EVALUATE(DateValue, DateText) THEN ERROR('Custom Error Message'); MESSAGE('Date: %1', DateValue);
Number series are used throughout the NAV system. Every document has a unique identifier that is usually retrieved from the No. Series table. This table keeps a track of the last number used so that it knows what the next number should be.
However, this identifier is not just a number. A purchase order, for example, might have an identifier of PO123456, which means that it is actually a string. As you can't add a number to a string, you will have to figure out what the number part is, convert it to an actual number, and then increment it. This code from the IncrementNoText()
function in codeunit 396, NoSeriesManagement, does exactly that. As this code calls several other functions, it may be beneficial for you to look through the entire codeunit.
GetIntegerPos(No,StartPos,EndPos); stringnumber series, incrementingEVALUATE(DecimalNo,COPYSTR(No,StartPos,EndPos - StartPos + 1)); NewNo := FORMAT(DecimalNo + IncrementByNo,0,1); ReplaceNoText(No,NewNo,0,StartPos,EndPos);
It can be very useful to parse a string and retrieve certain values. This recipe will show you how to examine the contents of a string and manipulate that data.
Add a function called
RemoveNonNumeric()
. It should return a text variable namedNewString
.The function should take in the following parameter:
Name
Type
Length
String
Text
30
Add the following global variable:
Name
Type
I
Integer
Add the following global variables:
Name
Type
Length
OldPhoneNumber
Text
30
NewPhoneNumber
Text
30
Add the following code to the
RemoveNonNumeric()
function:FOR i := 1 TO STRLEN(String) DO BEGIN IF String[i] IN ['0', '1', '2', '3', '4', '5', '6', '7', '8', '9'] THEN NewString := NewString + FORMAT(String[i]); END;
Add the following code to the
OnRun
trigger:OldPhoneNumber := '(404) 555-1234'; NewPhoneNumber := RemoveNonNumeric(OldPhoneNumber); MESSAGE('Old Phone Number: %1\New Phone Number: %2', OldPhoneNumber, NewPhoneNumber);
When you run the codeunit you will see a window similar to the following screenshot:
A string is actually an array of characters. The same array syntax will be used to access the individual characters of the string.
We start with a FOR
loop that begins at the first character, with index 1, and goes until we reach the end of our string. This is determined using the STRLEN()
function which stands for STRing LENgth. As the first index is 1 the last index will be N, or the number of characters in the string.
Next, we access the character at that index using square brackets. If the character is a number, meaning we want to keep it because it is numeric, we add it to our resulting string.
NAV comes with plenty of built-in string manipulation functions to remove characters, return substrings, find characters within string, and many more. A search in the C/SIDE Reference Guide from the NAV client help menu for string functions will give you a complete list.
Parsing strings has several uses in NAV. Some easy-to-implement examples include checking/converting a phone number to a proper format based on country code, properly capitalizing names, and removing illegal characters.
Using the Object Designer run table 6508, Value Entry Relation. You should see a column named Source RowId that contains some strange looking text. A careful examination reveals that these are not as strange as they appear. It is simply a string containing six values, each separated by a semicolon and enclosed within quotes. For example: "123";"0";"123456";""; "0";"10000".
In a typical installation involving shipments and receipts, the value of the current inventory is adjusted every time an item comes in or goes out of stock. This amount is stored in the Value Entry table. In order to know which document created which value entry, a subsidiary table was created: Value Entry Relation. In this basic scenario, the first field refers to the table that the value entry came from. The most common are: 113 for shipments and 123 for receipts. The third value stores the document number and the sixth contains the line number. Take a look at the function DecomposeRowID()
in codeunit 6500, Item Tracking Management.
FOR ArrayIndex := 1 TO 6 DO Item Tracking ManagementStrArray[ArrayIndex] := ''; Len := STRLEN(IDtext); Pos := 1; ArrayIndex := 1; WHILE NOT (Pos > Len) DO BEGIN Char := COPYSTR(IDtext,Pos,1); IF (Char = '"') THEN BEGIN Write := FALSE; Count += 1; END ELSE BEGIN IF Count = 0 THEN Write := TRUE ELSE BEGIN IF Count MOD 2 = 1 THEN BEGIN Next := (Char = ';'); Count -= 1; END ELSE IF NoWriteSinceLastNext AND (Char = ';') THEN BEGIN Count -= 2; Next := TRUE; END; Count /= 2; WHILE Count > 0 DO BEGIN StrArray[ArrayIndex] += '"'; Count -= 1; END; Write := NOT Next; END; NoWriteSinceLastNext := Next; END; IF Next THEN BEGIN ArrayIndex += 1; Next := FALSE; END; IF Write THEN StrArray[ArrayIndex] += Char; Pos += 1; END;
This is an amazing example of how you can manipulate strings to your advantage. The code is fairly complex and may take some time to understand, but it can give you a basis to write your own code. You should be able to see the code that looks for semicolons, or field separators, as well as the code that finds quotes, or field identifiers. The code separates out those fields and stores them in a string array for later use.
Date formulas allow you to determine a new date based on a reference date. This recipe will show you how to use the built-in NAV function called CALCDATE
to calculate them.
Create a new codeunit from Object Designer.
Add the following global variable:
Name
Type
CalculatedDate
Date
In the
OnRun
trigger write the following code:CalculatedDate := CALCDATE('CM+1D', 01012010D); MESSAGE('Calculated Date: %1', CalculatedDate);
Save and close the codeunit.
When you run the codeunit you should see a window like the following screenshot:
The CALCDATE()
function takes in two parameters, a calculation formula and a starting date. The calculation formula is a string that tells the function how to calculate the new date. The second parameter tells the function which date it should start with. A new date is returned by this function, so the value must be assigned to a variable using standard :=
syntax.
The following units can be used in the calculation formula:
These units may be different depending on what language your version of NAV is running under.
You have two options for the number to place before the unit. This can either be a standard number ranging between 1 and 9, or the letter C, which stands for Current. These units can be added and subtracted to determine a new date based on any starting date.
Calculation formulas can become very complex. The best way to fully understand them is to write your own formulas to see the results. Start out with basic formulas like 1M+2W-1D and move on to more complex ones like — CY+2Q-1W.
NAV has the ability to issue a reminder whenever a customer goes past due on their balance. These reminders are issued at specific times based on date formulas entered by the user during setup.
Look at the MakeReminder()
method in codeunit 392, Reminder-Make. This function has a large amount of code so only a small section is shown here. The date formula is stored in a field called Grace Period and is used to determine if those many days have passed since the due date of the document.
IF (CALCDATE(ReminderLevel."Grace Period",ReminderDueDate) < ReminderHeaderReq."Document Date") AND ((LineLevel <= ReminderTerms."Max. No. of Reminders") OR (ReminderTerms."Max. No. of Reminders" = 0)) THEN BEGIN