Filtering with Dynamics NAV SQL

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

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.