Open database integration is a powerful feature of SimpleIndex® and one that furthers its interoperability with custom programs. Instead of using a proprietary database, SimpleIndex allows you to map its index fields to cells in any database table. It can be configured to create new records, update existing ones or retrieve them for viewing. Using these three basic database functions, SimpleIndex is able to interface with and operate on any database.
SimpleIndex can connect to any ODBC-compliant data source in order to match existing data to documents or export data extracted from documents.
Using alternate database schemas
In SQL Server, Oracle and MySQL (among others) user logons are associated with a default “schema” that determines which tables and views they can see.
Users may have access to other tables and views that aren’t in their schema. When connecting to the database these tables will show up in the list, but when you try to get the list of fields you get an “Invalid object reference” error.
To reference these tables, use [Schema].[Table] for the table name to explicitly reference the table.
You can also change the user’s default schema on the server to the one matching the table.
A third option is to create a view under the user’s default schema that selects the necessary records.
At the present time you cannot enter [Schema].[Table] in the wizard, so if you need to use this workaround configure the job in the wizard and modify the table name in the old job options screen.
Access Database Connection String
If you would like to create a database string for an Access database that includes a password use this format:
Provider=Microsoft.Jet.OLEDB.4.0;Data Source=path\database.mdb;Jet OLEDB:Database Password=db_password;
How do I delete an image and it’s database entry?
If your Job Settings Wizard configuration’s of Database mode setting is set to “Update Existing Records”, the database record and the image will be deleted when you delete an image from a batch. The Table Name must be an updateable query or table and you must have sufficient permissions for both the database record and the file.
You cannot delete files with SimpleSearch. To enable retrieval users to change indexes, rename or delete files, use a SimpleIndex Standard license.
- Published in Database & Retrieval
How do I export index data to a database?
There are a variety of ways to connect to your database. Detailed instructions are provided in the Manual (check the Help menu). Here is a brief overview of the steps involved:
-Create a job configuration to scan and index files
-On the database tab, set the “Database Mode” to “Insert New Records”
-To use ODBC, enter the data source name or file in Data Source
-To connect directly, select your database type under “Select a Data Source” and click Start. A series of dialogs will prompt you for database connection information.
-Select destination Table or View and click Reload
-For each index field, select the corresponding database field that will receive that field value
-The “Output File Field” will receive the path to the exported file
Once you have created records in your database in “Insert” mode, you can change to “Retrieve and View Records” and use SimpleIndex or SimpleSearch to search and view the files.
- Published in Database & Retrieval, Export
Is it possible to have the scanned image itself added to a database and not just the image path?
Yes. Image files can be inserted into binary fields in Access, SQL Server, Oracle, MySQL and other databases.
Check the “Store files as binary objects” option on the Database tab and the “Output File Field” setting can be mapped to a binary field.
If using PDF, MS Office or other non-image files, use the File Type Field to store the file extension of the stored file.
SimpleSearch mode will let you view files stored using this method as well.
- Published in Database & Retrieval, Export
When exporting to a database, I get the error “Multi-step operation generated errors”
This error is usually caused when the data you are trying to insert does not match the data type of the database field. This could be a text value being inserted into a number or date field, or a text value that is longer than the maximum length of the field.
To avoid this error, be sure to use the correct Field Type and/or Template on the Index tab to force the user to enter data in the correct format. You may also need to adjust the data types or lengths for your database fields.
- Published in Database & Retrieval
How do you configure a field to select from a list of possible values?
List fields are populated using a text file or database field containing the values for that list. The text file scenario will be described first.
To configure a list field, go to the Index tab in the Job Options. Create a list field by selecting “List” for the field type and give it a name. In the “List File/Field” setting for this field, put the full path or UNC to the text file containing the list or click “Set” to browse to the file. If you have not created a file, you can put the path where you want the file stored here and click the “Edit” button. This will open the file in Notepad; new files will prompt you to create them.
On each line of the text file you will put one of the possible values that you want to be able to select from while you are indexing with SimpleIndex. You can copy this information from another source and paste it into the text file.
Save the file in Notepad and close it. The List field is now configured!
To use a database field, your configuration must be connected to a database using the settings on the Database tab. Any fields defined as “List” on the Index tab that have a corresponding field mapped on the Database tab will use the unique values from that field to populate the list.
You can check “Only allow values in list” to disallow users from entering a value not in the list.
Once the List index field is configured properly you can select the values from a drop down after scanning that appear in the main SimpleIndex window while indexing. It also makes the field autofill the closest match from the list based on the characters that you type in that field.
- Published in Indexing & UI
Is it possible to restrict user access to certain documents in Retrieval Mode?
If the database you use to store index information has security access features, you can use these to manage access to documents with SimpleIndex retrieval mode.
In your database, create user accounts and groups and assign them the appropriate permissions to records in your document database.
In SimpleIndex, check the “Require Logon” option on the Logging tab to force the user to logon to the database using the accounts you created. When they search and view documents, they will only be able to access records that the corresponding database user has been assigned access to.
- Published in Database & Retrieval
How do you configure full text searching in Retrieval mode?
On the Database tab there dropdown in the lower portion of the panel for Full Text OCR Field. Put the name of the field that will store the full-text data there. This must be configured both for Insert and Retrieval mode configurations. The database field needs to be sufficient length to store the entire text of your document.
Of course, the Insert Mode configuration must have “Enable Full Page OCR” checked to generate full text data from images. Text from MS Office documents, PDF files and existing OCR text files can be used without setting this option.
When designing your Retrieval Mode configuration, create a Text field to use for full text search queries. On the Database tab, set the corresponding “Database Field Name” to the full text database field.
When searching on your full text field, SimpleIndex finds the text you enter no matter where it appears in the document. It is able to match partial words. It does not perform boolean or natural language searches. The text entered must match the document text exactly.
- Published in Database & Retrieval, OCR
What is “Match & Attach” mode?
Match & Attach mode lets you batch update multiple records in a database using the index data from your SimpleIndex job. For example, if you have a large backfile of documents that you want to scan and link to records in an existing database, you can use Match & Attach to find the corresponding record and set the Image Path field to the newly scanned file.
For details on how to configure Match & Attach mode, please refer to the manual.
- Published in Database & Retrieval, Export
How do you configure the Autofill feature?
The Autofill feature of SimpleIndex is an easy way to associate many index fields with one document without retyping data that already exists in another application.
Autofill uses a database lookup to retrieve records that match a key value entered by the user. Blank index fields are then filled in automatically with the data from this lookup. The result is a document database with many different possible search fields, of which only one needed to be entered during scanning.
To configure the Autofill feature first go to the Index tab and define the field that will be used to perform the lookup. Each value in this field should correspond to a single record in the database, usually defined as the “Primary Key”. This field can be any type, including Barcode and OCR for full automation.
Next click “Configure Index Autofill” button. Here is where you will setup the database lookup. The “Match Data Source” is configured the same way as the “Data Source” on the Database tab. It can be the path to an MDB file, an ODBC data source or OleDB connection string. Click “Set” to browse to an Access MDB file or type the connection string.
Once the data source is set, the drop-downs for the Table and Field names will be populated. Select the table or query that contains the data, then select the field in the database that will be used for lookup. Under “SimpleIndex Key Field 1” select the field you defined in the first step.
“Key Field 2” gives you the option to use 2 field values to perform the lookup if a single unique matching value doesn’t exist.
Click OK and go back to the Index tab.
Finally set the field type to “Autofill” on every field you want to populate from the database. In the “Template” for all the Autofill fields put the name of the corresponding database field that will be used to populate it.
Now when a value is entered for the key field, the Autofill fields will be filled in automatically from the database!
Cascading List Mode
Cascading List Mode is a powerful new feature introduced in 8.2 that lets you filter each list field by the values you select. For example, you could have lists for Vendor Name and Invoice Number, and when you select a Vendor from the list, the Invoice Number list will only show invoices from the selected vendor.
- Published in Database & Retrieval, Indexing & UI
How do I connect to an existing Access database?
To connect to an existing Access database go to Options then the Database tab. There is a drop-down list next to the “Start” button. Click the arrow and select “MS Access” then click “Start”.
An Open Database dialog will be displayed. Select the Access MDB you want to use then click “Open”.
Select the table or query you want to use from the “Table Name” list and click “Reload”.
The field selectors will now be populated with the list of fields from the table/query you selected. The Image Path stores the path to the image file for each record. Each entry in Database Fields list maps the corresponding Field # from the Index tab to the selected field in the database.
Click apply and your Access Database will be configured.
The Log Field, Log Table, Revision Field/Level and the Full Text and PageCount fields on the Advanced tab are option. See the manual for a description of what these items do.
- Published in Database & Retrieval
I know nothing about databases. Can I still use the database and Retrieval Mode features?
Yes! We’ve made it simple to create a database that you can use to store index information and perform searches with SimpleIndex Retrieval Mode.
Once you have configured a SimpleIndex job to scan and store documents on your network, use these steps to create a database to store index information for searching:
1. On the database tab, make sure “Create Generic Access DB” is selected in the drop-down and click “Start”.
2. Save the Access database in a location accessible to all SimpleIndex users.
All the settings on this form will be filled in automatically after you save the database.
3. Change the “Database Mode” to “Insert New Records”. This means that when the job is run, new records will be added to the database as new files are processed.
4. Save the this configuration.
5. To create a configuration for searching and viewing, simply change the “Database Mode” setting to “Retrieve and View Records”.
You may wish to change field types on the Index tab to “List”. This will let the users select from a list of possible values for that field when searching.
Save this configuration to a different file and use it when searching. That’s it!
- Published in Database & Retrieval
Database Integration
Unprecedented Control of Database Interactions
Open database integration is a powerful feature of SimpleIndex® and one that furthers its interoperability with custom programs. Instead of using a proprietary database, SimpleIndex allows you to map its index fields to cells in any database table. It can be configured to create new records, update existing ones or retrieve them for viewing. Using these three basic database functions, SimpleIndex is able to interface with and operate on any database.
Automatic Indexing Using Existing Data
The Autofill feature of SimpleIndex is an easy way to associate many index fields with one document without retyping data that already exists in another database. Autofill uses a database lookup to retrieve records that match a key value entered by the user. Blank index fields are then filled in automatically with the data from this lookup. The result is a document database with many different possible search fields, of which only one needed to be entered during scanning.
The key field may be typed by the user, or it may be read from the document automatically using barcode recognition or OCR. The lookup is performed either when the user changes this field or when the index values are saved. If the lookup finds multiple matching records, the user will be notified and the first set of values will be used by default.
When used with pre-index batches, key information can be read automatically from barcodes or OCR and matched to database records with a single click. Search on up to 99 index fields without a single keystroke!
Output to XML or Any Text Format
SimpleIndex 8 offers export to XML files, as well as the SimpleExport module. SimpleExport makes it possible to integrate with any third party database, application, or document management systems (DMS) that accepts an XML or text file import.
SimpleExport converts the index values from a SimpleIndex job to any output format through XSL Transformations (XSLT). You may use one of the built-in scripts to export to a pre-defined system or design your own. SimpleExport runs automatically after each batch is exported, or it can run as an unattended Windows service.
SimpleExport can also be used as a standalone application for automating XSLT transformations, converting CSV or TSV (delimited text files) to XML, converting images to base64 or integrating other scanning applications with third party software.
Match and Attach Mode
SimpleIndex features “Match and Attach” mode, which combines the power of the Autofill feature with the ability to update existing records in your database. This lets you “attach” images to existing database records by “matching” them with values from SimpleIndex.
With other systems you must create a temporary database and write a custom import to match the scanned images to existing records. When indexing with OCR or barcode recognition, the entire process can be accomplished with our 1-click interface.
KB Articles for Database Integration
- Oracle database is slow to respond
- What is Document Imaging?
- Using alternate database schemas
- Multiple Sort Fields on Search
- Access Database Connection String
- How do I delete an image and it's database entry?
- Is it possible to search for and retrieve documents with Windows desktop search?
- Will your SimpleQB allow me to scan in old invoices or bank statements directly into QuickBooks?
- How do I use the Media Wizard to create searchable DVDs or thumb drives?
- How do I export index data to a database?