Skip to main content

 

Documentation & User Guides | FotoWare

Downloading 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.

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 Thesaurii 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

 

Province

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

 

Country

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.

odbc location relationships.PNG

 

Next, a query (view) is created to make it easier to access the data configuring the ODBC connection in FotoStation.

odbc Query - design view.PNG

 

The query produces this content, which will be used as the basis for setting up the database connection in FotoStation:

odbc Query - content view.PNG

Configure the system DSN

Make sure to save the database before proceeding.

Open the Windows Control Panel and choose Administrative Tools. Then open ODBC Data Sources (32-bit).

Create a new System DSN (make sure not to choose User DSN) and select the database file.

Note: If using another database 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). Then click on the New button to create a new connection.

odbc location setup.PNG

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.

Then click on Configure to set up the database field to metadata mapping.

In the field mapping dialog, click on the Select table dropdown list and choose the query (aka. View) that you created to display the correct fields. Then choose the XMP fields to map the database fields to, as shown in the below screenshot.

 

fsodbc - field mapping.PNG

Click 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, CityFor help on file naming conventions, have a look at this linked list sample.

Where you store it depends on the configuration type FotoStation is set up to use. This document explains the location of configuration files 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.

 

ODBC quicklist in use in FotoStation.PNG