In a live ERP system, your database will have thousands of entries. Being able to search through this data or limit the data by filtering it is important so that you can continue with your work.
When you look for a specific record in a list, for example, a customer for which you know the name, you can easily use the Find function.
When you are looking for a record having a certain value in a field on a card page, for example, a customer resides in a specific city or has a specific salesperson, you can easily use the Go To function.
A quick filter or an advanced filter can be used to limit the number of records displayed on a list page or the number of records to examine starting from a card page. You can set a filter by specifying criteria for one or several fields in a table. Microsoft Dynamics NAV 2016 then tests all records, and only records that match the criteria pass through the filter. If criteria are specified for several fields at the same time, records must match all criteria to pass through the filter.
These filters are page-dependent. If you set a filter on a table in a page and then see the same table in a different page, the filter is not in effect there. On the other hand, the filter will remain in effect in the page where you set it until you remove it or until you close the page.
A third filter option is the Limit totals filter, which is used to limit the contents of Flow Fields that display amounts or quantities.
Entering Criteria in Filters
When you enter criteria, you can use all the numbers and letters that you typically use in the field. In addition, you can use some special symbols or mathematical expressions. The following tables provide the available formats.
|Symbol||Meaning||Sample Expression||Records Displayed|
|=||Equal to||377||Number 37.|
|BLUE||Those with the BLUE code. For example, the BLUE warehouse code.|
|22||A date time: from 22-current month-current year 22:59:59.|
|22 10||An exact date time: 22-01-01 10:00:00.|
|..||Interval||1100..2100||Numbers 1100 through 2100.|
|..2500||Up to and including 2500.|
|..12 31 00||Dates up to and including 12 31 00.|
|P8..||Information for accounting period 8 and thereafter.|
|..23||From the beginning of time until 23-current month-current year 23:59:59.|
|23..||From 23-current month-current year 0:00:00 until the end of time.|
|22..23||From 22-current month-current year 0:00:00 until 23-current month-current year 23:59:59.|
||||Either/or||1200|1300||Those with number 1200 or 1300.|
|&||And||<2000&> 10 00||Numbers that are less than 2000 and greater than 1000.
The ampersand (&) cannot be used by itself with numbers because no record can have two numbers.
|< >||Not equal to||< > 0||All numbers except 0.
With the SQL Server option, you can combine this symbol with a wildcard expression. For example, < > A* meaning not equal to any texts that begin with A.
|>||Greater than||> 1200||Numbers greater than 1200.|
|> =||Greater than or equal to||> = 1200||Numbers greater than or equal to 1200.|
|<||Less than||< 1200||Numbers less than 1200.|
|< =||Less than or equal to||< = 1200||Numbers less than or equal to 1200.|
|*||An indefinite number of unknown characters||*Co*||Texts that contain Co.|
|*Co||Texts that end with Co.|
|Co*||Texts that begin with Co.|
|?||One unknown character||Hans?n||Texts such as Hansen or Hanson.|
|Calculate before rest||30|(→=10&<=20)||Those with numbers 30 or with a number from 10 through 20 (the result of the calculation within the parentheses).|
|@||Ignore case (either uppercase or lowercase allowed)||@location||Texts such as LOCATION, location, and Location.|
|''||Blank||''||All records with a blank value in the field for which the criteria is used. For example, the Contact Person field on the customer card, to view all customers without a contact person.
NOTE: The criteria consists of two single quotation marks, not one double quotation.
You can also combine the various format expressions shown in the following table:
|5999|8100..8490||Include any records with the number 5999 or a number from the interval 8100 through 8490.|
|..1299|1400..||Include records with a number less than or equal to 1299 or a number equal to 1400 or greater (all numbers except 1300 through 1399).|
|>50&<100||Include records with numbers that are greater than 50 and less than 100 (numbers 51 through 99).|
|*C*&*D*||Include texts that contain both C and D.|
|@*co?*||Include texts that contain co, CO, Co, cO, such as cot, cope, and incorporated.
The text CO, cO, Co, or co must be present followed by at least one character, but there can be an indefinite number of characters before and after these, and case is unimportant.
Enter only meaningful filters. For example, you can specify an interval that does not exist, and Microsoft Dynamics NAV 2016 cannot check this for you. You must know the sorting rules followed by this program to enter meaningful filters.