The Data Query Wizard
On setting a metadata field to type Database Query, the DB Query Builder button appears above the metadata field definition grid, as shown below.
Clicking this button will bring up the Data Query Wizard which will help you create a database connection and query and then test your settings:
The Wizard will expect you to select the database server from the ones detected and displayed in the Server dropdown. Once you have selected the server from the list, enter the name of the database you wish to query in the Database field, together with a username and password for Scan2x to use to access this database. A specific user with read-only permissions is enough.
Write the SELECT query in standard SQL format in the “Select Query” window and press the “Execute” button. This will test your server, database and user settings and will try to run the SELECT query you entered, displaying any results in the grey box at bottom right.
At the top left is a list of metadata fields that you can use as parameters in the WHERE clause of your SELECT query.
SELECT <fieldname> from <tablename> WHERE <indexfield> = [Filename]
<fieldname> = the name of the field whose value we want to use to populate metadata field Test2
<tablename> = the name of the database table we are querying
<indexfield> = the field in the database table against which we are searching
[Filename] = the Scan2x metadata field whose value we will use to search the database with
NOTE: When writing the query, the <tablename> must be enclosed in single quotes, otherwise this will cause an Error.
Once we have results appearing in the grey Results box at bottom right, we can tell Scan2x to use this value by setting the Display dropdown at bottom left.