Storing and retrieving data are the two main activities in Dynamics NAV. While retrieving data, most times we are looking for a specific set of data. To choose our desired data, the system needs to handle large datasets; in this situation, filtering plays a very important role. In this recipe, we will look at the advanced filtering of datasets in the C/AL code.
- Create a new codeunit with Object Designer.
- Add the following local variable into the
OnRuntrigger:Name
Type
Subtype
GLEntryRecordG/L Entry - Add the following code into the
OnRuntrigger of the codeunit:GLEntry.RESET; GLEntry.SETCURRENTKEY("Document No.","Posting Date"); GLEntry.SETRANGE("Posting Date",010113D,310113D); GLEntry.SETRANGE("Document Type",GLEntry."Document Type"::Invoice); GLEntry.SETFILTER(Amount,'>%1',25000); IF GLEntry.FINDFIRST THEN MESSAGE('Found!Document No.: %1Amount: %2', GLEntry."Document No.", GLEntry.Amount) ELSE MESSAGE('Not Found!'), - Save and close the codeunit.
- On executing the codeunit, you should see a window similar to that shown in the following screenshot:
In this recipe, we are filtering the G/L Entry table to retrieve a specific record set and selecting the first record out of the filtered record set. The RESET function will remove all filters and change the current key to the primary key. SETCURRENTKEY is used to select a key for a record and set the order of sorting. The key selected by us will sort data by Document No. and then by Posting Date.
SETRANGE removes any filters that were set previously and replaces them with the "from-value" and "to-value" parameters. While providing Date as a parameter, we need to remove date separators and add D at the end of the Date value. In the filter, when we provide the Date value, we need to consider the current system's data format. In this recipe, the data format used is dd/mm/yyyy. If you are following the US date format, the value should be 013113D, and if it is the UK date format, the value should be 310113D. Our filter will provide data between 01/01/2013 and 31/01/2013.
In the next SETRANGE filter, we provide only one value ("from-value"). In this system, we will set the "to-value" to the same as the "from-value".
SETFILTER provides functionalities to use multiple operators to filter data. We selected the Amount field and provided the relational operator > (greater than) with a placeholder. Finally, we set the value 25000 for the placeholder. If you are not sure about the exact filter value, you can use operators such as * and @ to provide an approximate or nearby value.
After applying filters, we used the
FINDFIRST function to choose the first record out of the filtered record set. If your database does not have any value for a given filter, you will receive the Not Found! message.
There are many ways to filter your data; for more detailed information, run a search for the help topic titled Field Filters and Table Filters in the Developer and IT Pro Help menu in the Help menu of Microsoft NAV Development Environment. Microsoft provides wonderful examples of all of the available filtering options, both individually and combined.