Documentation

Folder from database query

How to define folder database query settings

With the Data Query dialog you can define settings for retrieving information from a database utilizing standard SQL syntax. Custom search forms for input can also be enabled by including custom syntax within the SQL query. To access the Data Query dialog create a new folder with Data Query toggled on. Then select Data Query from the Folder Popup context menu. Database query connections are only available with the Elite version of this application.
  • 1) The General tab is where you will enter your JDBC driver and Connection URL. Refer to your JDBC driver documentation for the Driver and Connection URL syntax. You can connect up to 5 different JDBC drivers by placing the driver .jar file you obtained from the driver manufacturer in the JDBC folder located within the IntelliCabinet installation location, the installation location can be found in your Session Log from the Help menu. The driver file name must be renamed to jdbc1.jar jdbc2.jar jdbc3.jar jdbc4.jar or jdbc5.jar to be recognized.
  • 2) The SQL Statement tab is where you will enter the SQL SELECT statement for retrieving the desired data. This field accepts any standard SQL query SELECT statement that your database driver supports.
  • 3) The Field Settings tab is where you will be able to configure custom search form inputs, such as checkboxes, drop down lists, and text boxes, if custom fields are detected within the defined SQL Statement. A custom field is enabled by including the syntax $FIELD[Label Name] within your SQL Statement text. The words Label Name surrounded by brackets can be any text you desire. Placing these within the SQL SELECT statement will automatically generate a search form for the desired inputs. The entered fields will then replace the $FIELD[Label Name] before the SQL SELECT statement is passed to the SQL database.
  • 4) The Mappings tab is where you will be able to map non-value based fields from your query columns to settings and formatting, such as background color, etc. A custom mapping is enabled by including the syntax $MAPPING[Query Field Name] within your SQL Statement text. The words Query Field Name surrounded by brackets would be the name of the result field within the query. Placing these within the SQL SELECT statement will automatically generate a list on the Mapping tab for further definition. On the Mappings tab you will need to specify the Type (e.g. Format Background, Row Height) and which column to apply/map them to within your cabinet folder. This is done by entering the cabinet folder column name in the Applied Column Name cell within the Mappings tab list.
  • 5 & 6) The Copy and Paste buttons are utilized to copy the Data Query settings from one Data Query dialog to another.
SQL Statement Example:
SELECT UID, Description, FormatFieldA AS '$MAPPING[Name Format]', FormatFieldB AS '$MAPPING[Type Format]' FROM mydatabase WHERE FirstYear = $FIELD[First Year] ORDER BY $FIELD[Sort Order] LIMIT $FIELD[Limit];
Field Settings:
Mappings:
Results:
System column translation:
Query field system_name translates to the Name column
Query field system_type translates to the Type column
Query field system_location translates to the Location column
Query field system_path translates to the Path column
Note: A folder file's Path column uniquely identifies the file. Two files specified with the same Path are handled as a replacement of the defined columns for that Path.