Using database lookup to populate metadata fields with photographer information
This topic explains how you can configure Color Factory to query a database using a unique string in a metadata field and pull information from the database to populate additional metadata fields.
Scenario description
Pictures delivered from photographers to a newsdesk are often tagged with a photographer code using a prefix in the filename when the picture is written in the camera. When files are ingested into the workflow, Color Factory can isolate that prefix and populate a number of metadata fields from a database that holds information about each photographer.
This is done using a channel that isolates and stores the prefix with the unique photographer's code. Then that code is used as the basis for a database query where any additional information about the photographer can be pulled from the database and mapped to metadata fields in the picture.
How to do it
Start off by creating a channel with the Metadata feature enabled. Assuming there are only JPEG's coming into the workflow, you can set the channel's output to JPEG Copy to avoid quality loss as a consequence of recompressing the JPEG file.
Isolate the photographer's code in the filename
Expand the Metadata node and go to the File Name to Metadata tab. We will use this to isolate the photographer's prefix and store it in a separate field so that we can use that information as the basis for our database lookup in the next step.
We'll assume the files have a name of the format p00x-filename.jpg where the prefix preceding the dash is what we want to store in a separate field. As you can see in the screenshot above, we've configured Color Factory to look for a single prefix, i.e. anything preceding the dash (p00x in our case).
Configure the ODBC connection
Next you'll need to configure the ODBC connection. Within the Metadata feature, go to the ODBC tab and tick Activate ODBC connection. Then choose to Read from database, write to file, as shown above.
You will have to have a System DSN set up on the system to connect to. If you're uncertain about how to do this, you should talk to your database administrator. Choose the DSN to connect to using the Browse button and supply any username and password required to access it.
Then go on to Configure the database connection.
The screenshot above shown as example of how the fields have been mapped.
The Code field is the database field that contains the photographer's unique code. That field is set as the primary key, which is used as the search string to the database. Since it is unique, we have also chosen to search for the exact expression.
Having defined the primary key you can now map any additional fields from the found record to other metadata fields. In the example above, we'll put the photographer's full name in the Byline field, and address and phone number in their respective address fields.
Remember to save the Color Factory configuration to update the changes you've made.
That's all it takes to configure Color Factory to do ODBC lookup. Now, when pictures start pouring in to the workflow, Color Factory will find the corresponding information in the database and populate any metadata fields you want in the pictures based on the information you keep and maintain in the database.