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