When doing database lookups in KTM validation, this is what you’ll usually see:

Most of the times, there is a separate button that will pop up the search dialogue, where you – later on – may enter some words in order to fetch the most appropriate record. Take this fictional case: we want to classify pictures of food, assigning each image a unique entry from a database. This is my – rather incomplete – database of awesome fruits:

As you can see, there’s a unique identifier for each fruit, a general classification – such as “Apple” or “Banana”, followed by the more specialized type. The picture above shows a cavendish banana, by the way. So, when using KTM like they thought us in school you’ll end up with placing a db lookup button on the validation form, having a user click that button, enter either the fruit or type, and then select the result. If you were nice, you privided them a hotkey, reducing the amount of steps required by one (hotkey – type – enter – select – enter). Even in the best case when you know exactly the fruit type, leaving you with a single 100% match, that makes five steps as you have to select that entry as well:

Here’s a different approach. Imagine the following: instead of opening a separate dialogue, you’ll just start typing and hit to issue the search. If there’s one single hit, all other fields are populated already. In the example shown below I already know that the image shows a plantain type banana, so I entered exactly that text, resulting in a single hit (note that there’s fuzziness as well).

What if there were multiple hits? No problem again, then we’ll just pop up the dialogue. In the example shown below, I could not identify the exact type of fruit, but I knew those were bananas:

This approach seems way faster one you already know what you’re looking for. Take the first example and compare it with the classical approach: instead of “hotkey – type – enter – select – enter” it’s just “type – enter”. Two steps instead of five. Even if you don’t know the exact type of fruit as shown in the second example, it’s just “type – enter – select – enter”, so no need for a hotkey.

This approach can work in many different use cases, thing about invoices where you might want to lookup a VAT id or an account number. Then, you can even enable auto complete in the search field, increasing efficiency even more.

Ah yes, and here’s some source code:

Private Sub ValidationForm_AfterFieldConfirmed(ByVal pXDoc As CASCADELib.CscXDocument, ByVal pField As CASCADELib.CscXDocField)

   If pField.Name = "Id" Then

      Dim oResult() As String
      Dim oVals(1) As String
      Dim oDB As New DatabaseDialog.DBLookupFuzzy
      Dim tmpfield As CscXDocField
      Dim lastRowId As Integer
      Dim i, h As Integer

      oVals(0) = pField.Text

      oDB.DialogCaption = "Lookup"
      oDB.GroupBoxCaption = "Results"
      oDB.QueryEvalMode = CscEvalMatchQuery
      oDB.StartColumn = 0
      oDB.SearchImmediately = True
      oDB.InitialQueryVals = oVals

      oResult = oDB.ShowDialog(Project.Databases.ItemByName("Fruit"), 100)

      ' hint: if there is a single hit, it will be returned; even if it hasn't got 100% confidence
      If UBound(oResult) <> - 1 Then
         pXDoc.Fields.ItemByName("Id").Text = oResult(0)
         pXDoc.Fields.ItemByName("Fruit").Text = oResult(1)
         pXDoc.Fields.ItemByName("Type").Text = oResult(2)

      End If

   End If

End Sub