Filtering in Microsoft® Dynamics™ NAV

Exclusive offer: get 50% off this eBook here
Programming Microsoft® Dynamics™ NAV

Programming Microsoft® Dynamics™ NAV — Save 50%

Create, modify, and maintain applications in Microsoft Dynamics NAV 5.0 using this book and eBook

$35.99    $18.00
by David A. Studebaker | October 2007 | .NET Microsoft

Filtering is one of the very powerful tools within NAV C/AL. Filtering is the application of defined limits on the data to be considered in a process. Filter structures can be applied in at least three different ways, depending on the design of the process.

 

 

Filtering

As mentioned earlier, filtering is one of the very powerful tools within NAV C/AL. Filtering is the application of defined limits on the data to be considered in a process (to learn more about Data types in NAV, visit here). Filter structures can be applied in at least three different ways, depending on the design of the process. The first way is for the developer to fully define the filter structure and the value of the filter. This might be done in a report designed to show only information on a selected group of customers, for example those with an open Balance on Account. The Customer table would be filtered to report only customers who have an Outstanding Balance greater than zero.

The second way is for the developer to define the filter structure, but allow the user to fill in the specific value to be applied. This approach would be appropriate in an accounting report that was to be tied to specific accounting periods. The user would be allowed to define what period(s) were to be considered for each report run.

The third way is the ad hoc definition of a filter structure and value by the user. This approach is often used for general analysis of ledger data where the developer wants to give the user total flexibility in how they slice and dice the available data.

It is quite common within the standard NAV applications and in the course of enhancements to use a combination of the different filtering types. For example, the report just mentioned that lists only customers with an open Balance on Account (via a developer-defined filter) could also allow the user to define additional filter criteria. Perhaps, the user wants to see only Euro currency-based customers, so they would filter on the Customer Currency Code field.

Filters are an integral part of FlowFields and FlowFilters, two of the three Field Classes. These are very flexible and powerful tools, which allow the NAV designer to create forms, reports, and other processes that can be used by the user under a wide variety of circumstances for various purposes. In most systems, user inquiries (forms and reports) and processes need to be quite specific to different data types and ranges. The NAV C/AL toolset allows you to create relatively generic user inquiries and processes and then allow the user to apply filtering to fit their specific needs.

Defining Filter Syntax and Values

Let us go over some common ways in which we can define filter values and syntax. Remember, when you apply a filter, you will only view or process records where the filtered data field satisfies the limits defined by the filter.

Equality and inequalityeither an equal (=) sign or no sign filters for data "equal to" the filter value.

Data Type - description

Example Filters
Integer =200
Integer 200
Text Chicago
Text " (two single quote marks)

        a greater than (>) sign filters for data greater than the filter value

Data Type - description Example Filters
Integer >200
Date >10/06/07
Decimal >450.50

        a less than (<) sign filters for data less than the filter value

Data Type - description Example Filters
Integer <150
Date <10/07/07

Programming Microsoft® Dynamics™ NAV Create, modify, and maintain applications in Microsoft Dynamics NAV 5.0 using this book and eBook
Published: October 2007
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

the equal sign can be combined with the greater than (>=) or less than (<=) signs to filter for data "greater than or equal" OR "less than or equal" to the filter value

Data Type - description Example Filters
Integer <=100
Date <=12/31/07
Date >=1/1/08
Text '>= Grade B

Not Equal is represented by the combination of the "less than" symbol plus the "Greater than" symbol to filter for data not equal to the filter value

Data Type - description Example Filters
Integer <>1
Date <>TODAY (TODAY is a system variable representing the current system date
Boolean <>yes (an awkward way of stating "No"

Ranges Ranges are defined by an expression containing two dots in a row (in other words ..). Ranges are inclusive, that is the maximum and minimum values are included within the range. Ranges have three variations. The first is the from - to version which includes both a bottom end or minimum to the range and a top end or maximum.

Data Type - description Example Filters
Integer 1..10
Date 5/1/07..5/31/07
Text Jones..Smith
Decimal 100.01..199.99

The second range variation consists of the range operator (the two dots ..) plus a range maximum This means "give me all the values from the lowest possible value up to and including the range maximum. This is generally the same as using the less than or equal to (<=) format.

Data Type - description Example Filters
Integer ..10 (Gives the same results as <=10)
Date ..12/31/07
Decimal ..99.99

The third range variation consists of a lower limit (minimum) value flowed by the range operator (..).

Data Type - description Example Filters
Integer 100.. (Gives the same results as >=100)
Date 1/1/07..
Decimal 100000.00..

Boolean operators

  • There are two Boolean operators. The operators are the ampersand sign (&) representing the logical AND operation and the pipe symbol (|) representing the logical OR operation.

The OR operator can be used to create a discontinuous set of allowed values.

Data Type - description Example Filters
Integer 5|10|15|20 (This will give you matches on all four of the stated values and only on those values.)
Date 10/1/07|11/1/07|12/1/07 (This filter will pass through on records dated on the first date of the three months)

The AND operator can generally only be used in combination with other filtering operators.

Data Type - description Example Filters
Integer (>=100) & (<=1000) (Gives the same result as the range 100.1000)
Date <>TODAY (TODAY is a system variable representing the current system date)
Boolean <>yes (an awkward way of stating "No")

Wild cards

  • There are three wild card characters that can be used within filter constructs. Wild cards only apply to string data. You will not find the term wildcard defined or the usage of wildcards described in the Microsoft documentation or Help.

Asterisk (*) represents any character and any number of characters.

Data Type - description Example Filters
Text *st* (Includes all data containing the lowercase letters 'st')
Text st* (Includes only the data starting with the lowercase letters 'st')
Text *st (Includes only the data ending with the lowercase letters 'st')

Question mark (?) represents any character, but only one character.

Data Type - description Example Filters
Text ?st? (Includes all data which is four characters long with the middle two characters being the lowercase letters 'st')
Text ????st (Includes all data which is exactly six characters long ending with the lowercase letters 'st')

"At" symbol (@) eliminates case sensitivity for the value following. The @ is often used in combination with the asterisk to make the filter value satisfy a wider range of data

Data Type - description Example Filters
Text *@st* (Includes all data containing any of the strings 'st', 'St', 'ST' or 'sT')
Text @*st* (Gives the same results as the previous example)

  • Combinations – Many of these filter constructs can be used in combination. Again, the caution applies about thoroughly testing your creations before inflicting them on unsuspecting users. It is relatively easy to create a filter which, on initial thought seems logical, but which won't work the way you thought it would. In addition, the C/AL compiler routine which interprets filters is not perfect. It can get confused or just fail. Be very cautious about using combinations that contain wildcards, especially (but not limited to) those expressions containing both wildcards and Boolean operators.

Be very cautious about constructing filters based on exclusions. Generally, the limited "inclusive" approach works better. For example, you might want to print a Customer list excluding all Customers for the Salespeople with codes of JR and MD.

You might try create a filter on Salesperson Code such as (<>JR) AND (<>MD). The C/SIDE routine that checks filter will not accept that as a valid entry. The same goes for <> (JR AND MD), as well as the attempt to put in two separate filter entries (only one filter string is allowed per data field.). What to do?

To simplify, let us assume all our Salesperson Code are just two characters long. You should create a filter on the Salesperson Code in the form (..JQ) | (JP..MC) | (ME..). This translates to all the Customers having either a Salesperson Code less than or equal to JQ or (the pipe symbol: | ) from JP to MC or greater than or equal to ME. In other words, all the two character codes except JR and MD.

 

 

 

 

Programming Microsoft® Dynamics™ NAV Create, modify, and maintain applications in Microsoft Dynamics NAV 5.0 using this book and eBook
Published: October 2007
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

Experimenting with Filters

Now it is the time for you to do some creative experimenting with filters. We want to accomplish several things through our experimentation. Our first purpose is to get more comfortable with how filters are entered. Secondly, we want to see the effects of different types of filter structures and combinations. If we had a database with a large volume of data in it, we could also experience the speed of effecting the filtering on fields in keys and fields not in keys. But the amount of data in the Cronus database is small and our computers are very fast, so any speed differences will be difficult to see.

We could experiment on any report that allows filtering. To give us some options for our experimentation, we will use the Customer/Item List. This will report which Customer purchased what Items. The Customer/Item List can be accessed on the NAV user menu via Sales & Marketing | Reports | Customer | Customer/Item List.

When you initially run the Customer/Item List, you will see just three data fields listed for entry of Filters on the Customer table as shown in the following screenshot:

Filtering in Microsoft® Dynamics™ NAV

There are also three data fields listed for entry of Filters on the Value Entry table as shown in the following screenshot:

Filtering in Microsoft® Dynamics™ NAV

In each case, these are the fields that the developer determined should be emphasized. If you run the report without any filters at all, using the standard Cronus data, the contents of the first page of the report will resemble the following screenshot:

Filtering in Microsoft® Dynamics™ NAV

If you want to print information only for customers whose names begin with a letter A, your filter will be very simple, similar to the following screenshot:

Filtering in Microsoft® Dynamics™ NAV

The resulting report will be similar to the following screenshot, showing only data for the two customers on file whose names begin with the letter A.

Filtering in Microsoft® Dynamics™ NAV

If you want to expand the customer fields on which you can apply filters, click on the first empty field and you will see something similar to the following screenshot. The size of the pop-up window can be stretched as large as your display image allows and you can then scroll down to see rest of the fields.

Filtering in Microsoft® Dynamics™ NAV

This provides us access to all the fields in the customer record, the table identified in the Tab heading. From this list we can choose one or more fields and then enter filters on those fields. If we chose Territory Code, for example, then the Request Form would look similar to the following screenshot. And if we clicked on the lookup arrow in the Filter column, a screen would pop-up allowing us to choose data items from the related table, in this case, Territories.

Filtering in Microsoft® Dynamics™ NAV

This particular Request Form has tabs for each of the two primary tables in the report. Click on the Value Entry tab to filter on the Item-related data. If we filter on the Item No. for Item No's that contain the letter W, the report will be similar to the following screenshot:

Filtering in Microsoft® Dynamics™ NAV

If we want to see all the items containing either the letter W or the letter S, our filter would be *W* | *S*. If you made the filter W | S, then you would get only entries equal exactly to W or to S because we didn't use any wild cards.

You should go back over the various types of filters we discussed and try them all. Then you should try some combinations. Get creative! Try some things that may or may not work and see what happens. Explore a variety of reports or list screens in the system and try applying filters to see what happens. A good screen to which to apply filters is the Customer List (Sales & Marketing menu | Sales | Customers | F5). This is supposed to be a non-threatening learning experience (you can't hurt anything or anyone).

This is also an opportunity to learn more about the NAV User Interface, because that is what you must use to do your filtering. There are four buttons at the top of the screen that relate to filtering, plus one for choosing the active key. In Windows XP, they look like the following screenshot:

Filtering in Microsoft® Dynamics™ NAV

From left to right, they are as follows:

  • Field Filter (F7)—Highlight a field, press F7 (or select View | Field Filter), and the data in that field will appear in a display ready for you to define a filter on that data field. You can edit the filter in any way before you click OK.
  • Table Filter (Ctrl+F7)—Press the Ctrl Key and F7 simultaneously (or select View | Table Filter). You will be presented with a form that allows you to choose any number of fields in the left column and, in the right column, enter filters to apply to those fields. Each of these individual filters is a Field Filter, the same as would have been applied using the Field Filter option just described. The filters for the individual fields are "ANDed" together (i.e. they all apply simultaneously). If you invoke the Table Filter form when any Field Filters are already applied, they will be displayed.
  • Flow Filter (Shift+F7)—Press the Shift Key and F7 simultaneously (or select View | Flow Filter). You will be presented with a form that allows you to choose any number of fields in the left column and in the right column, enter filters to use with those fields. On initial display, it will show all the Flow Filter fields available. For any Flow Filter field, you can enter a filter, which will then be applied to the underlying data for FlowFields whose definition includes a constraint by that particular Flow Filter field.

    You can also use this form to enter Field Filters, but you will not be able to see the field filters that are already in effect via this form. To remove Flow Filters, you must call up this form and manually remove the filters, by deleting the filter lines or at least the filter values.

  • Show All (Shift+Ctrl+F7)—This will remove all Field Filters, but will not remove any Flow Filters.
  • Sort (Shift+F7)—allows you (or your user) to choose which key is active on a displayed data list (unless the underlying C/AL code overrules). By properly choosing a key that contains the field on which you wish to filter, you can significantly affect the speed of the filtering process. Of course this is true for filtering processes coded in C/AL as well. When you are viewing a form and want to check if filters are in effect, check the bottom of the screen for the word FILTER as shown in the next image.
    Filtering in Microsoft® Dynamics™ NAV 

    One of the most frequent support calls by new users seems to be "My data has disappeared." The proper response is "Does it say FILTER at the bottom of the screen?" Almost always the answer is "Yes", in which case the proper assistance is to use the Filter icons we just reviewed to inspect and/or clear unwanted filters (typically using the Table Filter and Flow Filter to inspect, possibly using the Show All and Flow Filter to clear filters.

Summary

In this article, we discussed Filtering in some detail, and how filtering is considered as we design our database structure, and how the users will access data.

Go back to Data Types in Microsoft® Dynamics™ NAV

About the Author :


David A. Studebaker

David Studebaker is Chief Technical Officer and a founder of Liberty Grove Software with his partner Karen Studebaker. Liberty Grove Software, a Microsoft Partner, provides development, consulting, training, and upgrade services internationally for Microsoft Dynamics NAV resellers and end user customers.

David has been recognized by Microsoft as a Certified Professional for NAV in all three areas: Development, Applications, and Installation & Configuration. He has been honored by Microsoft as a Lead Certified Microsoft Trainer for NAV.

David just celebrated his first half century of programming, having started programming in 1962. He has been developing in C/AL since 1996. David has been an active participant in each step of computing technology from the first solid state mainframes to today's technology, from binary assembly language coding to today's C/AL and C#.

David's special achievements include his role as co-developer of the first production multi-programmed SPOOLing system in 1967. David has worked on a diverse set of software applications including manufacturing, distribution, retail, engineering, general accounting, association management, professional services billing, distribution/inventory management, freight carriage, data collection and production management, among others. Prior to co-authoring this book, David was the author of Programming Microsoft Dynamics NAV (for the Classic Client) and Programming Microsoft Dynamics NAV 2009 (for the Role Tailored Client).

David has had a wide range of development, consulting, sales and management roles throughout his career. He has been partner or owner and manager of several software development businesses, while always maintaining a hands-on role as a business applications developer.

David has a BS in Mechanical Engineering from Purdue University and an MBA from the University of Chicago. He has been writing for publication since he was an undergraduate. David has been a member of the Association for Computing Machinery since 1963 and was a founding officer of two local chapters of the ACM.

Books From Packt

Entity Framework Tutorial
Entity Framework Tutorial

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

VSTO 3.0 for Office 2007 Programming
VSTO 3.0 for Office 2007 Programming

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

Small Business Server 2008 – Installation, Migration, and Configuration
Small Business Server 2008  Installation, Migration, and Configuration

C# 2008 and 2005 Threaded Programming: Beginner's Guide
C# 2008 and 2005 Threaded Programming: Beginner's Guide

 


 

 

No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
4
4
X
U
j
b
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software