Configuring FotoStation to download QuickList metadata from a database
Learn how to configure FotoStation to obtain predefined QuickList content from a database.
How can it be used?
By configuring FotoStation to use an ODBC connection for QuickList content, it is possible to maintain a consistent set of centrally managed keywords, categories, and other metadata relevant to your DAM system in a database and give users of FotoStation immediate access to new content as it becomes available. As with locally maintained thesaurus lists, it is possible to link fields together to allow a user to look up for example a city and have the province and country filled in automatically through database relations. (See the example below to learn how.)
How to configure it
Configuring FotoStation to download QuickList content from a database requires the following:
- A System DSN for the ODBC data source
- An ODBC connection configured in FotoStation
- A header entry in the QuickList files in the FotoStation configuration that instructs the metadata editor to use the ODBC connection for QuickList content.
Note: ODBC QuickLists cannot be updated by FotoStation users - the ODBC connection makes for a one-way, read-only connection
Regular flat-structure QuickLists (named ql_<fieldnumber>.txt) don't work with ODBC connections. Only Thesauruses and Linked Lists can be used.
Example: Country, Province and City lookup using ODBC
In this example we're using Microsoft Access 2013 to create a database with three tables: City, Province, and Country. The table setup can be seen below:
Table setup
City
|
ID |
ProvinceID |
City |
|---|---|---|
|
1 |
2 |
Oslo |
|
3 |
6 |
Bergen |
|
2 |
15 |
Trondheim |
|
4 |
16 |
Stavanger |
|
5 |
11 |
Göteborg |
|
6 |
8 |
København |
| ID | CountryID | Province |
|---|---|---|
| 2 | 1 | Oslo |
| 3 | 1 | Akershus |
| 4 | 1 | Buskerud |
| 5 | 1 | Oppland |
| 6 | 1 | Hordaland |
| 15 | 1 | Trøndelag |
| 16 | 1 | Rogaland |
| 11 | 2 | Bohuslän |
| 12 | 2 | Värmland |
| 13 | 2 | Jämtland |
| 14 | 2 | Småland |
| 7 | 3 | Jylland |
| 8 | 3 | Sjælland |
| 9 | 3 | Fyn |
| 10 | 3 | Bornholm |
| ID | Country | CCode |
|---|---|---|
| 1 | Norway | NOR |
| 2 | Sweden | SWE |
| 3 | Denmark | DEN |
Relationships
Each table has an AutoNumber ID field which ties to a field in the associated table as shown in the screenshot.
Next, a query (view) is created to make it easier to access the data configuring the ODBC connection in FotoStation.
The query produces this content, which will be used as the basis for setting up the database connection in FotoStation:
Configure the system DSN
Make sure to save the database before proceeding.
- Open the Windows Control Panel and choose Administrative Tools.
- Open ODBC Data Sources (64-bit).
- Create a new System DSN (make sure not to choose User DSN) and select the database file.
Note: If using a database other than Access, the correct database driver has to be installed on the machine. Then select the database through the driver interface.
- Start FotoStation and open the configuration (File - Configuration).
- Expand the Metadata node and click on Database connections (ODBC).
- Select New to create a new connection.
- Name the connection (this name will be used in the QuickList file later to reference the connection) and use the Browse button to choose the System DSN you just created.
- Select Configure to set up the database field for metadata mapping.
- In the field mapping dialog, choose the query (aka. View) that you created to display the correct fields from the Select table drop-down list.
- Choose the XMP fields to map the database fields to, as shown in the example below.
- Select OK to store the field mapping.
- Finally, you need to create a QuickList file in the FotoStation configuration folder that references the database connection.
In the case of this example, the file should be named 101-100_95_90.txt
The file should contain a single string:
#! ODBC_CONNECTION_NAME=<FotoStation database connection name>
In the case of this example, it reads:
#! ODBC_CONNECTION_NAME=location
The numbers in the file name (101-100_95_90.txt) refer to metadata field numbers: Country, Country Code, Province/State, City. For help on file naming conventions, see this linked list sample.
Where you store it depends on the configuration type FotoStation is set up to use. For information about the location of the files, see Where are configuration files stored in Windows?
How it works
When a user opens the metadata editor in FotoStation, accessing the QuickList of any metadata field that is linked to an ODBC source will display a Linked Fields dialog as shown below. Picking an entry then populates all the associated fields based on the information in the database.
When the database is updated with new entries, they will be shown in the FotoStation editor the next time it is opened. There is no need to restart FotoStation for this to work.
