AutoStore: Excel Data in a Konica Minolta Database Lookup Field

Sometimes, a simple data source can be quite appropriate to supply list items for a user to choose from. In this post, we'll connect a Konica Minolta Database Lookup field to a Microsoft Excel file to serve that purpose.

A recent AutoStore project involved the Konica Minolta Open API capture with route to an existing Windows folder structure. After mapping out the basics during the discovery phase, we were looking at basic entries on the touch screen.
  • Client Name
  • Document Type
  • Document Date

Based on the selected AutoStore form, the Document Types would vary. Enough was known about the scanning workflows to start building an initial configuration, but the customer still needed to compile a list of the Document Types.

Of course, it's simple to add list items into the properties of a List Field, but it means the CFG needs to be edited, saved, and the AutoStore service restarted for the new list to take effect. Relatively speaking, this would be easy for an AutoStore admin.

To keep it simple for the end user, an Excel spreadsheet was created to hold the known Document Types, and placed it in a shared folder for the user to modify over time. Instead of a traditional List field for the Konica Minolta component, we'll use a Database lookup field.

Setting up the Database Lookup field is fairly straightforward.
  1. Create an ODBC System DSN to the target spreadsheet. AutoStore uses the 32-bit version of the ODBC Data Source Administrator, which is located in the following directory:

    - C:\Windows\SysWOW64\odbcad32.exe

    The Microsoft Excel Driver (*.xls) can connect to an Excel 2003 spreadsheet. You can get the Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb) by installing Microsoft Excel 2007 and newer, or by installing the 32-bit version of the Microsoft Access Database Engine.
  2. In the Konica Minolta form, for the In the Database Lookup field, go to the field properties. To the right of the Connection String, click on the ellipsis [...] button. In the Data Source list, choose Microsoft ODBC Data Source, and click OK.
  3. Select the System DSN for the Excel file in the Use user or system data source name list, click the Test connection button, which should show Test connection succeeded, and click OK to close that message box. Click OK to save and close the Connection Properties dialog.
  4. Now in the Retrieve Data From section, when working with an Excel file, there is no table available to select from in the drop-down list. Using the spreadsheet's sheet name, enter that into the Table combo-box with a dollar-sign ($) character at the end of the sheet name.

    Example: Sheet1$

    Click the Connect button, and you should now see the column names for that sheet in the Value Column and Display Column drop-down lists. Click Test to view the results, and click OK to finish.

Save, restart, and these items should now show up in that field on the device. What's nice is much like with any other data source, the client is empowered to manage this list of items when they see fit. Make sure they have access to the file, such as a shortcut to the folder.

Comments

Post a Comment

Popular posts from this blog

VBScript: Ensure Backslash Folder Path

Image
To preface what could be regarded as a very small thing, I am reminded of a common turn of phrase concerning the amount of perspiration that should be afforded to the "stuff" belonging to a particular size-category, and pretty sure there was a book written about that topic. Not to come across as disagreeable, but rather to invite a little balance based on my own general observations over time, an increase in cost with things such as time, money, even relationships, can be avoided when the "small stuff" is given the regard that it deserves. One of these tiny details has to do with string variables that reference folder paths within a script. There are times when it is important that a defined directory for a particular folder should and must always end with a backslash. As an example: folderPath = "C:\Example"   Of course, it really is not very difficult to manually enter that backslash into the path. folderPath = "C:\Example\" ...
Read more

AutoStore: Workflow Loop Example

Image
Regardless of the programming language, a looping-statement is a common declaration for iterating through collections of things, such as the items of an array, records selected from a database, elements within an XML file, or the files inside a directory. A recent AutoStore challenge was presented where a script was used to fetch data in a process that returned either one or many items. This was the easy part. However, each return record needed to be specific to an individual Route based on one of those items returned. Example Example1@email.com Example1/URL Example2@email.com Example2/URL Example3@email.com Example3/URL Although the captured document being processed would be common for all in the return, the recipient belonging to one of these unique records should not be aware of  nor  receive any of the information that would belong to a different recipient. Because an unknown number of return records would be expected, this presents a challenge due ...
Read more

AutoStore: Create a Custom RRT

Image
Have you ever needed to dynamically create a custom RRT for an AutoStore process? In my previous post AutoCapture Default Date , I described my very first AutoStore script where the value of an  Invoice Date  field in an AutoCapture Vendor Invoices  form was assigned a default value of the day prior from the current date. This Invoice Date  needs to be part of the file name, however its format contains slash (/) characters that cannot be part of the file name. So, we'll use the AutoStore VB/JScript process component to create a new RRT that's more friendly. To do so, we'll look at how to do the following: Bring the existing Invoice Date  value into the script Reformat the Invoice Date  to a Windows-friendly file name Output the reformatted Invoice Date to a new custom RRT After adding the VB/JScript process component in between the capture component and the Send to Folder route component of the configuration file, we open the AutoCap...
Read more