MS SQL Server Installation and Configuration of NAV 2015

Some frequently used built-in functions in CAL programming

As a programmer everyone knows what is function..!! A function is nothing but a subprogram or a subroutine mainly used for code re usability. In CAL programming we call the code of a function as Trigger code.

Function call is written as same as in C programming-

result := add(a,2);
or
result := add(a+2*3,b);

Pass by value

Change i.e. made in function trigger if it doesn't affect the variables in calling trigger then it 's call by value.

If a change in function trigger affects the variables in calling trigger then it' s call by reference.
You cannot use constants for pass by reference.

actual parameter is used in a calling function trigger.
Formal parameter is used in called function trigger.

5 commonly used built-in functions are:

MESSAGE - Displays message on the screen

STRLEN - returns no. of characters used.
MAXSTRLEN - returns the defined length of  the string variable.
COPYSTR - Returns a part or whole of a string.
CLEAR - clears the passed-in variable.
ARRAYLEN - returns the no. of elements in an array.

1. DATA access functions :

GET, FIND, FINDFIRST, FINDLAST, FINDSET, NEXT 

GET function-doesn't respect filters, produces run-time error if it fails.

FIND-You can find a value based on specified key value and sorting order.

Avoid using find function as much as possible.

FINDFIRST- to find the first record in a table based on key and filter.

FINDLAST- to find the last record in a table based on key and filter.

FINDSET - to find set of records in a table that corresponds to specified set of filters.
note: In FINDSET you can iterate only from first to last. To iterate from last to first use FIND(+).
Usually, we use this in combination with repeat-until loop.

NEXT - used with FIND and FINDSET to step through records of the table.


2. SORTING AND FILTERING functions : 

For key selection,sorting and filtering we use these functions.

SETCURRENTKEY - To select a key for a record.

SETRANGE - sets a simple range ex. to restrict a phone no. to be b/w 8 to 14 numbers we use this.

SETFILTER - It includes in more general way, These filtering includes placeholders, operators, wildcard expressions.

GETRANGEMIN and GETRANGEMAX

retrieve the minimum or maximum value of the filter that is currently in effect on a field.

4. DATA MANIPULATION functions : 

INSERT - inserts a record in a table

MODIFY - changes an existing record

MODIFYALL - performs bulk update of record. Respects filters. It doesn't return values and it doesn't display any errors.

DELETE - to delete a record from the database. 

DELETEALL - to delete all records that are selected by the filter settings.

5. Frequently used functions with fields

CALCFIELDS - to calculate flowfields,

if you're accessing record from the code then calcfields must be explicitly specified. 

Rec or xRec variable is automatically calculated when a record is retrieved. (no need to call here)

SETAUTOCALCFIELDS - improves performance by reducing no. of round trips to database. 

CALCSUMS - calculates total of a column in a table. It respects filters.

note : Maintaining an index for a SumIndexField improves the performance of CALCSUMS, but may decrease the performance of insert, modify, and delete operations.

FIELDERROR - stops execution of code causing run-time error. It creates an error message for a field. 

FIELDCAPTION - returns caption of a field. We can show the messages very accurately to user by displaying FIELDERROR along with fieldname. 

INIT - initializes record by assigning default values. 

This function is not applied for primary key. 

TESTFIELD - tests if value of field matches the specified value. 

VALIDATE - to call OnValidate trigger of a field. It checks the business rules and makes sure that it is not violated by storing invalid value in the field. 

6. USER INTERACTION functions:

MESSAGE - displays a text string in a message window. 

It's displayed after CAL code finishes execution or when it waits for user interaction. 

% - placeholder

\ - new line character 

CONFIRM - prompts the user for yes/no option. (to confirm that user wants to continue with the process)

STRMENU - creates a menu window that displays series of options from a comma-delimited string.

ERROR - displays error message and ends the CAL execution. All uncommitted data are rolled back. 

7. String Functions:

MAXSTRLEN, STRLEN, STRPOS(case sensitive), COPYSTR, LOWERCASE, UPPERCASE, INCSTR, SELECTSTR(retrieves sub-string from a comma separated string).

8. Date Functions:

DATE2DMY, NORMALDATE, CALCDATE, CLOSINGDATE

9. Numeric functions:

ROUND, ABS, POWER, RANDOM, RANDOMIZE

10. ARRAY functions:

ARRAYLEN,COMPRESSARRAY, COPYARRAY

11. STREAM functions:

CREATEINSTREAM, CREATEOUTSTREAM, READ, READTEXT, WRITE, WRITETEXT, EOS, COPYSTREAM,

12. System functions:

EXIT, CLEAR, CLEARALL, EVALUATE, FORMAT