SETCURRENTKEY,SETRANGE,SETFILTER

The following functions are used to filter records in a table:

#. SETCURRENTKEY #. SETRANGE #. SETFILTER
These functions are some of the most commonly used C/AL functions. They set limits on the value of one or more specified fields, so that only a subset of the records are displayed, modified, deleted, and so on.

SETCURRENTKEY Function

SETCURRENTKEY selects a key for a record and sets the sort order that is used for the table in question. SETCURRENTKEY has the following syntax.

Variable := Record.SETCURRENTKEY(Field1, [Field2],...)

When you use SETCURRENTKEY the following rules apply:

Inactive fields are ignored.

When searching for a key, the first occurrence of the specified field(s) is selected. This means the following:

If you specify only one field as a parameter when you call SETCURRENTKEY, the key that is actually selected may consist of more than one field.

If the field that you specify is the first component of several keys, the key that is selected may not be the key that you expect.
If no keys can be found that include the field(s) that you specify, the return value is FALSE. If you do not test the return value, a runtime error occurs. If you do test the return value, the program will continue to run even though no key was found.
SETRANGE Function

SETRANGE sets a simple filter on a field. SETRANGE has the following syntax.
Record.SETRANGE(Field [,From-Value] [,To-Value]);

In the following example, SETRANGE filters the Customer table by selecting only those records where the No. field has a value between 10000 and 90000.

Customer.SETRANGE("No.",'10000','90000');

When you use SETRANGE the following rules apply:

SETRANGE removes any filters that were set previously and replaces them with the From-Value and To-Value parameters that you specify.

If you use SETRANGE without setting the From-Value and To-Value parameters, the function removes any filters that are already set.

If you only set the From-Value, the To-Value is set to the same value as the From-Value.

SETFILTER Function

SETFILTER sets a filter in a more general way than SETRANGE. SETFILTER has the following syntax.

Record.SETFILTER(Field, String [, Value], ...];
Field is the name of the field on which you want to set a filter. String is the filter expression. String may contain placeholders, such as %1 and %2, to indicate where to insert the Value parameter(s) in a filter expression.

The following example selects records where the value of No. is larger than 10000 and not equal to 20000.

Customer.SETFILTER("No.", '>10000 & <> 20000');

If the variables Value1 and Value2 have been assigned "10000" and "20000" respectively, then you can use the following statement to create the same filter.

Customer.SETFILTER("No.",'>%1&<>%2',Value1, Value2);

CALCFIELDS, CALCSUMS

CALCFIELDS updates FlowFields. FlowFields are automatically updated when they are the direct source expressions of controls, but they must be explicitly calculated when they are part of a more complicated expression.
CALCFIELDS has the following syntax.
Variable := Record.CALCFIELDS(Field1, [Field2],...)

When you use FlowFields in C/AL functions, you must use the CALCFIELDS function to update them.
For Eg:
In the following example, the SETRANGE function sets a filter and then the CALCFIELDS function calculates the Balance and Balance Due fields by using the current filter and performing the calculations that are defined as the CalcFormula properties of the FlowFields.
Let the Variable Be 'Customer', Data Type be 'Record'.
Customer.GET(INDG-CUS-005); // Will Selct The Customer No: INDG-CUS-005
Customer.SETRANGE("Date Filter",0D,TODAY); // Will set the date Range for the Present Dt
Customer.CALCFIELDS(Balance,"Balance Due"); // Using, Calcfields system calcs the balance as per Dt.
MESSAGE('The Balance is %1 and your Balance Due is 2',Customer.Balance,Customer."Balance Due"); // Will Displays the balance in a Message Box, with Corresponding Value.

CALCSUMS Function:CALCSUMS calculates the sum of one or more fields that are SumIndexFields in the record. CALCSUMS has the following syntax.
Variable := Record.CALCSUMS (Field1, [Field2],...)

For CALCSUMS, a key that contains the SumIndexFields must be selected as the current key. Similar to CALCFIELDS, CALCSUMS uses the current filter settings when performing the calculation.
For Eg:
Let the Variable be 'CustLdgentry' & Datatype be 'Record'
CustLdgentry.SETCURRENTKEY("Customer No."); // will Set the Key Customer No.
CustLdgentry.SETRANGE("Customer No.",'50000','90000');// Will set Range of Customers No. From 50000-90000.
CustLdgentry.SETRANGE("Posting Date",0D,TODAY); // will Filter the posting date with Present date.
CustLdgentry.CALCSUMS("Sales (LCY)"); // Will Calculate total Sales Froam Sales(LCY) Field.
MESSAGE ('%1 calculated sales',custledgerentry."Sales (LCY)") // Total sales Amount in Local Currency Will be displayed in Message Box.

NEXT Function

NEXT is often used with FIND to step through the records of a table. NEXT has the following syntax.

Steps := Record.NEXT([Steps])

In the following example, FIND is used to go to the first record of the table. NEXT is used to step through every record, until there are no more. When there are no more records, NEXT returns 0 (zero).

FIND('-');
REPEAT
// process record
UNTIL NEXT = 0;

C/SIDE Objects

Here I am giving an Introduction to user interface and to some of the basic concepts that are relevant to application design such as the different object types.

C/SIDE applications are based on certain application objects.

They are

Table: You use tables to store data. For example, a business application usually contains a customer table that stores information about each customer.Understanding tables is the key to using all the other objects.

Form: You use forms to access the information that is stored in the tables. You use forms when you enter new information and when you view information that already exists in the database. Report: You use reports to present information. You use filters and sorting to select the data that you want to present in a report.

Dataport: You use dataports to import data from and export data to external text files.

Codeunit: A codeunit contains user-defined functions written in C/AL code. C/AL is the application language you use to write functions in C/SIDE. The functions that a codeunit contains can be used from the other objects in your application. This helps to minimize application size because the same code can be reused.

MenuSuite: A MenuSuite object contains the set of menus that are displayed in the Navigation Pane.

Page: Pages have the same functionality as forms but are intended to run on the RoleTailored client. A page can be role tailored, to suite a users needs.

GET, FIND Functions

GET & FIND Functions
The following functions are used to search for records:
1. GET 2. FIND
These functions are some of the most commonly used C/AL functions. When you search for records, it is important to know the difference between GET and FIND and to know how to use FIND and NEXT in conjunction.
GET Function

GET retrieves one record based on values of the primary key fields. GET has the following syntax.

Variable := Record.GET([Value],...)

For example,

If the No. field is the primary key of the Customer table and if you have created a Record variable called CustomerRec that has a Subtype of Customer, then you can use GET in the following way:

CustomerRec.GET('4711');

The result is that the record of customer 4711 is retrieved.

GET produces a runtime error if it fails and the return value is not checked by the code. In the preceding example, the actual code that you write should be similar to the following.

IF CustomerRec.GET('4711') THEN .... // Do some processing.

ELSE .... // Do some error processing.

GET searches for the records, regardless of the current filters, and it does not change any filters. GET always searches through all the records in a table.
FIND Function

FIND locates a record in a C/SIDE table based on the values stored in the keys. FIND has the following syntax.

Variable := Record.FIND([Which])

The important differences between GET and FIND are the following: FIND uses the current filters. FIND can be instructed to look for records where the key value is equal to, greater than, or smaller than the search string. FIND can find the first or the last record, depending on the sort order defined by the current key. When you are developing applications in a relational database, there are often one-to-many relationships defined between tables. An example could be the relationship between an Item table, which registers items, and a Sales Line table, which registers the detailed lines from sales orders. One record in the Sales Line table can only be related to one item, but each item can be related to any number of sales line records. You would not want an item record to be deleted as long as there are still open sales orders that include the item. You can use FIND to check for open sales orders.

The OnDelete trigger of the Item table includes the following code that illustrates using FIND. SalesOrderLine.SETCURRENTKEY(Type,"No.");

SalesOrderLine.SETRANGE(Type,SalesOrderLine.Type::Item);

SalesOrderLine.SETRANGE("No.","No.");

IF SalesOrderLine.FIND('-') THEN

ERROR(Text001,TABLECAPTION,"No.",SalesOrderLine."Document Type");

Common C/AL Functions

Although there are more than 100 functions in C/AL, there are several functions that you will use more often than the others.
1. GET, FIND, and NEXT Functions
2. SETCURRENTKEY, SETRANGE, SETFILTER, GETRANGEMIN, and GETRANGEMAX Functions
3. INSERT, MODIFY, MODIFYALL, DELETE and DELETEALL Functions
4. LOCKTABLE Function
5. CALCFIELDS, CALCSUMS,FIELDERROR, FIELDNAME, INIT, TESTFIELD, and VALIDATE Functions
6. Progress Windows, MESSAGE, ERROR and CONFIRM Functions
7. STRMENU Function

Triggers And Events

This section describes where to write C/AL code.

Almost every object in C/SIDE contains triggers where you can place your C/AL code.

Triggers exist for the following objects:
1. Tables , 2. Table fields , 3. Forms, including request forms , 4. Form controls , 5. Reports ,6. Data items , 7. Sections , 8. Pages.

You can initiate the execution of your C/AL code from the following:

#. Command buttons #. Menu items #. Codeunits.

Coding Standards in C/AL

The following are simple guidelines for placing C/AL code:

In general, place the code as close as possible to the object on which it will operate. This implies that code which modifies records in the database should normally be placed in the triggers of the table fields that are involved.

In reports, always maintain a clear distinction between logical and visual processing and position your C/AL code accordingly. This implies that it is acceptable to have C/AL code in a section trigger if that code controls either the visual appearance of the controls or whether the section should be printed. Never place code that manipulates data in section triggers.

The principle of placing code near the object on which it operates can be overruled in some situations. One reason to overrule this principle is security. Normal users do not have direct access to tables that contain sensitive data, such as the General Ledger Entry table. If you place the code that operates on the general ledger in a codeunit, give the codeunit access to the table, and give the user permission to execute the codeunit, you will not compromise the security of the table and the user will be able to access the table.
There are reasons other than security for putting a posting function in a codeunit. A function that is placed in a codeunit can be called from many places in the application, including, perhaps, some that you did not anticipate when you first designed the application.

Reusing Code : The most important reason for placing C/AL code consistently, and as close to the objects that it manipulates, is that it lets you reuse code. Reusing code makes developing applications both faster and easier. More importantly, if you place your C/AL code as suggested, your applications will be less prone to errors. By centralizing the code, you will not inadvertently create inconsistencies by performing essentially the same calculation in many places.

About Nav 2009

This topic contains information about some of the new features and enhancements in Microsoft Dynamics NAV 2009.
1. Three-tier Architecture With Microsoft Dynamics NAV 2009, the architecture has changed from a two- to three-tier architecture, enabling the new RoleTailored client and Web services.
2. Customizable, Role-Based User Interface Microsoft Dynamics NAV 2009 provides a more intuitive and customizable user interface (UI) called the RoleTailored client, which you can modify to support the job functions of different work roles in your organization. For each role that you support, you can create a customizable Role Center that displays key information that is required by that role and makes day-to-day tasks easier to complete. Partners, administrators, and super users can customize the UI for specific roles, and individual users can also further personalize the UI to meet their specific needs.
3. Page Objects Microsoft Dynamics NAV 2009 includes a new object called a page, which is used in the same way as a form. However, unlike forms, you have the flexibility to display pages in the RoleTailored client or to publish a page using a Web service. You can also transform your existing form objects to page objects.
4. Improved Report Design and Functionality Microsoft Dynamics NAV 2009 offers an improved reporting experience. You can now create interactive reports to which you can add charts, graphs, tables, and other data presentation elements that were not available in previous versions of Microsoft Dynamics NAV. You can also save your reports in .pdf or .xls file formats.
5. Business Connections with Web Services Web services are a standard, widely used method for integrating applications and are supported in Microsoft Dynamics NAV 2009. By implementing Web services, you can access Microsoft Dynamics NAV data and business logic from outside the product in a standard, secure way. This enables you to connect Microsoft Dynamics NAV to other systems within an organization.
6. RoleTailored client control add-ins RoleTailored client control add-ins extend the RoleTailored client with custom functionality. A control add(Available in NAV2009-SP1)-in is a visual element for displaying and modifying data on RoleTailored client pages.

About Micosoft Dynamics Nav

A comprehensive, easy-to-use ERP software system, Microsoft Dynamics-Navision ERP enables mid-size organizations to streamline business processes for accelerated growth. You can tailor Microsoft Dynamics-Navision to your business processes—manufacturing, distribution, financial management, sales and marketing, human resources, and others—and use it to accomplish your goals with optimal efficiency. The Official Website For Dynamics- Nav is www.microsoft.com/dynamics/nav/default.mspx