Database Lookups Made Easy
Database lookups are easy with ILINX® Content Store. Recently I configured a few and thought I would share some of the features. I am assuming that you are somewhat familiar with database lookups in general, so this is not a step-by-step but just to point out some cool features.
Here is the application that we need a lookup for and we are going to base it off the SSN field. We will need to click edit on the field and enable/configure the lookup
Fill in all the information to make the connection. A couple of cool features are that you can choose to use the .NET driver (which is more current technology) or the normal OLE DB driver. Also, the port used to connect is configurable.
And it doesn’t JUST do SQL…there are lots of options
Choose the table, view or stored procedure you want to use. Then, edit the SQL query to map the fields and test. Here you can also configure when the lookup occurs. By default, there is a look ahead feature. That means that after a certain number of characters entered, in this case 4, the database lookup returns up to 10 of the relevant possible matches on the criteria that has been entered. The max of 10 is configurable as well by changed the SELECT TOP 10 in the SQL query. The lower the number, the faster you can select a lookup. Keep in mind however that for every character typed after the 4th one to narrow down the results, the query has to re-execute so use your best judgment on how many characters should be typed before doing the lookup. You can also set the check box to lookup on tab out.
So our lookup is configured, but when we test the query we have a problem. It seems that the Date field in ILINX is a ‘Date’ field, and the field in the database is text. How can we solve this and still maintain the data integrity of our ILINX system?
This is where a custom SQL Query comes in handy. Using the CAST() feature in SQL, we can make that string into a date!
We run our test again and we return results. Click Finish and your database lookup for the Capture tab is done!
Mike Peterson
Support Engineer
ImageSource, Inc.
