---
title: "Filtering with Dynamics NAV SQL"
description: "Filtering with Dynamics NAV SQL"
keywords: "Filtering with NAV SQL add filters on transfers SQL operators"
url: "https://myrapidi.com/wiki/filtering_with_dynamics_nav_sql"
locale: "en"
published: "2026-01-23T14:42:27Z"
modified: "2026-01-23T14:45:54Z"
---

Filtering with MS Dynamics NAV uses NAV's native filter syntax, which is similar to SQL but with some NAV-specific conventions. NAV filters support wildcards, ranges, and logical operators to create flexible query conditions.  
  
Filter Syntax Rules

- NAV filters are **case-insensitive** for text comparisons
- Use single quotes for text values containing spaces or special characters
- **Field names with underscores, spaces, or special characters must be enclosed in square brackets** **`[Field_Name]`**  **or double quotes**  **`"Field_Name"`**
- Multiple filter criteria can be combined using `|` (OR) and `&` (AND) operators
- Date and time values should be formatted according to your NAV region settings

## Examples:

### Basic Text Filtering

| Field Name | Filter |
| --- | --- |
| No\_ | [No\_] = '1000' |
| Name | Name = 'CRONUS International Ltd.' |
| No\_ | "No\_" = '1000' |
| Order Status Text | [Order Status Text] = 'Released' |
| Order Status Text | "Order Status Text" = 'Released' |
| Description | Description = '_BICYCLE_' |
| No\_ | [No\_] = '1000\*' |
| External\_Document\_No\_ | [External\_Document\_No\_] = '' |
| Item No\_ | "Item No\_" != '' |
| Amount | Amount \> 1000 |
| Posting\_Date | [Posting\_Date] = '01-01-2024..31-12-2024' |
| Document Date | [Document Date] \> '2025-05-01' |
| Location\_Code | [Location\_Code] = 'BLUE'|'RED' |
| Document\_Type | [Document\_Type] = 'Order'|'Invoice' |
| Base Unit of Measure | [Base Unit of Measure] IN ('001','002','003','005','007','008','010','050','100','120','200','MM') |
| No\_ | [No\_] NOT IN ('','EP-00001744','1000001') |
| Document Type | [Document Type] = 0 |
| Order Status Text | "Order Status Text" != 'Open' AND "Order Status Text" != '' |
| Document No\_ | [Document No\_] IN (SELECT No\_ from [%%DATABASE%].[dbo].[%%COMPNAME%$Sales Header] WHERE [Document Date]\>='2025-05-01' AND [Sell-to Customer No\_] NOT IN ('','1005317','1005095','1006618','1001321')) |

## Important Notes:

- **Field Names with Underscores or Spaces** : Always use square brackets `[Field_Name]` or double quotes `"Field_Name"` when the field name contains underscores, spaces, or special characters.
- **Both syntaxes are valid** : `[Field Name]` and `"Field Name"` work identically - choose whichever you prefer.
- **Option Fields** : NAV option fields (like Document Type) can be filtered using their numeric values (0, 1, 2, etc.).
- **SQL Subqueries** : Advanced filtering supports SQL SELECT statements with Rapidi placeholders `%%DATABASE%%` and `%%COMPNAME%%`.
- **FlowFields** : Some NAV fields are FlowFields (calculated fields) and may have limitations on filtering.
- **Performance** : Complex filters with wildcards or multiple OR conditions can impact performance on large datasets.
- **Table Relationships** : When filtering on related tables, ensure the relationship is properly defined in your database. 

## Troubleshooting Tips:

- If a filter isn't working as expected, verify the exact field name by checking the NAV table metadata.
- Test complex filters directly in NAV using the filter pane to confirm syntax.
- Remember that NAV treats empty strings and null values differently than Salesforce.
- Check regional settings if date filters are not working correctly.
- **If you get syntax errors, ensure field names with underscores or spaces are properly enclosed in square brackets or double quotes.**
- Both `<>` and `!=` operators work for "not equal" comparisons.
- Use `&` or `AND` to combine multiple conditions on the same field.
- When using SQL subqueries, ensure proper table naming with company name suffix (e.g., `[%%COMPNAME%$Sales Header]`).
- `IN` and `NOT IN` operators are useful for filtering against multiple specific values efficiently.