There’s one thing in Capture that I miss all the time: an usable database lookup in validation. And no, the included version is far from practical, let alone it even lacks some vital features. Imagine the following use case: you want to process order forms, delivery notes, invoices, and all you need is to assign a data record already present in your organisation to them. That could be a supplier id, an internal customer number or a social security number. Usually, this information is not present on the document, while other data such as the supplier or customer name is. Now, assuming you process a small amount of those documents only, you might not want afford the use of KTM – but instead just enter the information in validation.

So, the idea is to present the user with a dialogue where one may enter a text, search a data set for matches, and then let the user pick the corresponding one:

The great news: as we can use .NET validation scripts, that is very possible. My approach consists out of the following artefacts:

• A simple data structure for storing information in matrix form (e.g. supplier or customer data),
• helper methods for squeezing both CSV files and SQL tables into my data structure,
• a Windows Form for performing the search, presenting results, and storing the candidate.

The search itself is trivial and could be optimized with a better algorithm. As of now, I only support searching one column at a time, and I simply make use of the contains method for partial matches. Once the user enters a search string and hits the enter key, the results are filtered based on that string and presented in a listbox. The elements in the listbox are bound to the my data structure – for example, by a simple double-click or by pressing the enter key data from the id and name are copied to fields in validation.

First of all, this is the data structure. As you can see, a table may be generated out of a CSV file, or directly from a SQL table. It’s really quite simple: a table consists of one or more rows, and a row can have one or more cells. The row overrides the ToString method to present something readable to the user (remember the listbox). The cell simply holds text.

The form itself that you saw at the very beginning is trivial – I won’t bother and go into too much details. If you want to take a look, you can find the complete demo project at the end of the post.

The tricky part is to communicate with the form. Sure, the class instantiates and shows it, but we need to react to events triggered by the form itself; one of those events is the double-click on the listbox which is straight-forward, however another one is a custom event that I’d like to fire once the user has hit the enter key after selecting an element out of the listbox. That way, I’d save my users lots of key strokes: three strokes to enter a query, tab, arrow down to select a result, enter again – that’s it. No need to use the mouse at all.

That’s how we deal with it:

Public Custom Event ListboxEntrySelected As EventHandler
	AddHandler(ByVal value As EventHandler)
		Me.Events.AddHandler("ListboxEntrySelectedEvent", value)
	End AddHandler

	RemoveHandler(ByVal value As EventHandler)
		Me.Events.RemoveHandler("ListboxEntrySelectedEvent", value)
	End RemoveHandler

	RaiseEvent(ByVal sender As Object, ByVal e As System.EventArgs)
		CType(Me.Events("ListboxEntrySelectedEvent"), EventHandler).Invoke(sender, e)
	End RaiseEvent
End Event

Private Sub lbxItems_KeyDown(ByVal sender As System.Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles lbxItems.KeyDown

  If e.KeyCode = Keys.Return Then
		Dim evhandler As EventHandler = CType(Me.Events("ListboxEntrySelectedEvent"), EventHandler)
		If evhandler IsNot Nothing Then
			 AcceptResult()
			 evhandler.Invoke(Me, e)
		End If
  End If
End Sub

Some final considerations were about performance. Imagine you query a database holding tens of thousands of entries, e.g. different suppliers; you only want to query that once, and not by document. So, I decided to go for the constructor of the validation class; I ran into strange errors when using BatchLoading and BatchUnloading, that’s why I went for the constructor and the finalize event.

Public Sub New()
	' initialize form
	dbdlg = New DBLookup()
	AddHandlers()
End Sub

Protected Overrides Sub Finalize()
	' cleanup
	dbdlg.Close()
	MyBase.Finalize()
End Sub

Last and final question: when and where to call the dialogue? I decided to go for a pre-field event, show it, and then just hide it from the user’s view. Here’s the result in validation:

And, as promised, here’s the prototype. It’s just a simple forms application and you’d need to copy classes and dialogues into Kofax, but it should get you started. Download: SupplierLookupPrototype