Query Definitions

It is the queries that make this tool what it is. It is through the queries that the end user can find the information he or she is looking for.

The product ships with a set of sample queries. These are developed based on feedback from customers on what they would typically like to search for.

Query Definition Files

All queries are defined in XML files located in a sub folder to the application folder. This would typically be C:\Program Files (x86)\NAV Finder\Search Definitions. Under this folder, each query has its own folder with a unique name. In these folders, you will find the actual XML file that defines the query.

XML Elements

You can see from the example below that there are a number of different elements in the XML file. Currently the only ones used are the Name and the Query elements. However, I recommend that you also use the Comment. It may be used in a later version of user interface.

SQL Statements

When you design your SQL statements there are some naming conventions for the returned fields. The NAV Finder expects certain column names in the result. Some names are only used when launching the Classic client, some are for the RTC, and others are used by both.

Columns

These fields are used when working with the Classic NAV client:

_nf_target - Contains the information of the NAV object to launch. For a search in customers, it should return 'Form 21'.

_nf_view - Contains the view information for the form specified in _nf_target.

_nf_position - Contains the position in the table to be show in the form.

These are for the Role Tailored Client:

_nf_rtc_page - Contains the page number to show when a search result is opened.

_nf_rtc_table - Contains the table number of the primary table used by the page.

_nf_rtc_key_1 - Contains the value and datatype of the first field in the primary key. The first letter of the value returned in this field indicates the datatype of the key field (C=code, O=option, I=integer). Following directly after the type letter is the actual data value of the key field. 'C10000' means that the datatype is code and the value is '10000'.

_nf_rtc_key_2 ... _nf_rtc_key_9 - These fields are used if the key consists of multiple fields. Otherwise, they are not returned in the result. In case you are mixing results from multiple tables and the keys have a different number of fields then you can return NULL as the key value.

Last, we have one for both clients:

_nf_icon - Contains the name of an icon to display for the result.

All the returned columns are shown in the result except the ones that start with _ (underscore). This means that you can have your own fields for internal use in the query if you prefix the name with an underscore.

The best way to determine what should be show in _nf_target, _nf_view, and _nf_position is to open the form in NAV classic. When the form is open, you go to the Edit menu and click Copy Link. For the customer form that will show something similar to this:

navision://client/run?servername=YourSqlServerName%26database=YourDatabaseName%26company=YourCompanyName%26target=Form%2021%26view=SORTING(Field1)%26position=Field1=0(10000)%26servertype=MSSQL

If you run that through an URL decoder such as urldecode.org then you end up with this:

navision://client/run?servername=YourSqlServerName&database=YourDatabaseName&company=YourCompanyName&target=Form 21&view=SORTING(Field1)&position=Field1=0(10000)&servertype=MSSQL

From this string, you can see that

_nf_target should return 'Form 21'.

_nf_view should return 'SORTING(Field1)'.

_nf_position should return 'Field1=0(10000)' where 10000 is the customer no. and key value.

For RTC this is a bit more complicated. However, if you are in the development environment it is fairly easy to find the page number, table number, and key fields.

Formatting Dates and Numbers

When the queries returns columns with numbers or dates, you should consider using the advanced formatting feature. The NAV Finder will use a default representation of numbers, dates, and times if you do not tell it which format to use. Using the advanced formats, you can make formats language and culture dependent or you can design your own highly customized formats.

Formats are specified in the SQL queries. If you add a column named _nf_format after a normal data column then the preceding column will be shown using the format in the _nf_format column.

This basic example selects some fixed values and specifies the format.

SELECT 1.23456789 AS [Unformatted number],

       1.23456789 AS [Format with 2 decimals], '{0:F2}' AS _nf_format,

       GETDATE() AS [Unformatted date],

       GETDATE() AS [Country specific date], '{0:d}' AS _nf_format,

       GETDATE() AS [Custom date format], 'My Date {0:yyyy-MM-dd}' AS _nf_format,

       '' AS _nf_target, '' AS _nf_view, '' AS _nf_icon, '' AS _nf_position,

       ''AS_nf_rtc_page,''AS_nf_rtc_table,''AS_nf_rtc_key_1

The syntax of the format is the same as the one used by the Microsoft.NET String.Format function. Values are inserted in the format string using the {0}. A format can be specified as {0:format}, where the actual format follows the Microsoft.NET format syntax.

Standard Numeric Format Strings

Custom Numeric Format Strings

Standard Date and Time Format Strings

Custom Date and Time Format Strings

Icons

Icons are found in the Icons subfolder under the application folder. These are normal Windows icon files. You specify a specific icon by returning the file name of the icon without the .ico extension. If the icon you specify is not found, nothing is shown.

Company Name

In NAV all table names are prefixed with the company name. When you design your SQL statement you must use the {COMPANYNAME} string as your company name. The {COMPANYNAME} is substituted with the real company name when the query is run from the NAV Finder.

Look at the example below to see how this is done.

Performance

When you design your queries, you should take the performance in to account. Your queries will generally run faster if you search less fields and tables for the information. You can also increase performance by limiting the number of records that you search. This is typically implemented with a WHERE clause in the SQL statement.

Example

This example shows a simple definition of how to search some fields in the Sales Header table.

<SearchDefinition>

  <Name>Sales Invoice Sample Query</Name>

  <Description>

    Find a sales invoice. This is a

    sample query with a two field key.

  </Description>

  <Author>

    <Name>Jacob Reinholdt Rasmussen</Name>

    <Company>More NAV</Company>

  </Author>

  <Query>

    SELECT TOP 1000

      'Form 43' AS _nf_target, '' AS _nf_view,

      'Field1=0(2),Field3=0(' + [No_] + ')' AS _nf_position,

      36 as _nf_rtc_table,

      'O'+CONVERT(VARCHAR,[Document Type]) AS _nf_rtc_key_1,

      'C'+[No_] AS _nf_rtc_key_2,

      43 AS _nf_rtc_page,

      'invoice' AS _nf_icon

      , [No_] AS [No.]

      , [Bill-to Name] AS [Bill-to Name]

    FROM [{COMPANYNAME}$Sales Header]

    WHERE [Document Type]=2

    AND UPPER([Bill-to Name]) LIKE UPPER(@SearchString)

    ORDER BY [No_]

  </Query>

</SearchDefinition>