How To: Resolve multiple Database hits with 3.0

How To: Resolve multiple Database hits with 3.0

Postby » Thu Sep 20, 2007 2:01 am

Note that this applies only to version 3.0 of Xtrata Pro, with the release of 3.1 this option is build-in!

When using a database locator for detecting e.g. vendors, it might be possible that you get back more than one result - probably because there are more vendors inside your database with the same (or a very similar) name, and so on.

Now you built up your beautiful validation mask, included a nice button that does a internal database lookup, but what now, if you have more than one hits? Just picking the first one? Doing nothing?

Well, I tried to resolve the problem like this:
If there are more than one hits, a dialog is shown, giving you the option to select which entry you'd like to pick.
This solution is not the best one, in fact you could also go for an Active X control which gives you the wonderfull flex grid, but it might give you an idea how to deal with such issues.

Our database is simple, again:
Code: Select all
apple green;fruit;10250;50
apple yellow;fruit;10260;50
apple red;fruit;10270;50
steel table;furniture;20442;20
paprika knife;tools;70050;90

On the validation screen, the user can enter the product name (or parts of it) - lets imagine he enters "apple".
The fuzzy search delivers back 3 hits, a dialog pops up, the user can select the correct entry, all other values will be copied to the fields.

This is the code:
Code: Select all
Private Sub ValidationForm_ButtonClicked(ByVal ButtonName As String, ByVal pXDoc As CASCADELib.CscXDocument)
   Select Case ButtonName
      Case "btnGo"

         ' handle button here

         ' *** The user has clicked the button, we need to re-connect to the database and fetch all the information for the fields.

         ' *** We need a collection to store the database result items in
         Dim results As CscDatabaseResItems
         ' *** thats a collection that holds the databases
         Dim db As CscDatabase
         ' *** all the fields we could use if we want to
         Dim fields(3) As String
         ' *** the IDs for the fields
         Dim fieldIDs(3) As Long
         ' *** the string array we are storing the results in
         Dim resarray() As String
         ' *** just for looping
         Dim i, h As Integer

         ' *** the collection of the result
         Dim resrowcol As String
         Dim resrow(10) As String

         ' *** we set up the database - it needs to be present in the project settings!!
         Set db = Project.Databases.ItemByName("dbdummy")
         ' *** the first field = the first field of the locator, in our case the vendor's name
         fields(0) = pXDoc.Fields.ItemByIndex(0).Text
         fields(1) = pXDoc.Fields.ItemByIndex(1).Text
         fieldIDs(0) = 0
         ' *** now we need to perform a search - that's just like the DB locator would perform a search
         Set results = db.Search(fields, fieldIDs, CscEvalMatchQuery, 10)
         ' *** we are looping now over each returned result, that's a string representing each value
         ' *** only perform the update if we have found an entry
         If results.Count > 0 Then
            For h = 0 To results.Count - 1
               ' *** now we're adding the strings returned to our array
               ' *** clear the temp string
               resrowcol = ""
               ' *** populate the array with the results
               resarray = db.GetRecordData(results(h).RecID)
               ' *** within the results..
               For i = 0 To UBound(resarray)
                  ' *** build up the string that contains a whole line
                  resrowcol = resrowcol & CStr(resarray(i)) & " | "
               Next i
               ' *** now assign the string to a new array.. this is just a workaround because the stupid dialog does not support any multi-dimensional arrays :-)
               resrow(h) = resrowcol
            Next h
         End If

   Begin Dialog UserDialog 530,203 ' %GRID:10,7,1,1
      ListBox 10,56,500,105,resrow(),.ListBox1
      OKButton 240,168,130,28
      CancelButton 380,168,130,28
      Text 10,7,500,42,"Multiple entries have been found. Select the entry you would ilke to accept and Press OK. Click on Cancel to this operation abort this operation.",.Text1
   End Dialog
   Dim dlg As UserDialog
   On Error GoTo NoDialog
   Dialog dlg

   ' *** the index of the listbox is returned by picking dlg.ListBox1
   ' *** re-fetching our results
   On Error Resume Next
   resarray = db.GetRecordData(results(dlg.ListBox1).RecID)
   For i = 0 To UBound(resarray)
      pXDoc.Fields.ItemByIndex(i).Text = resarray(i)
   Next i


   End Select
End Sub

I also attach the demo project.
Note that you'd need to adjust some paths.
AXP Database MultiResults Resolve.rar
A demo project showing you how you could resolve the multiple database - results problem.
(6.21 KiB) Downloaded 640 times
Kind Regards,
Wolfgang Radl
KOFAX Sales Engineer
Posts: 453
Joined: Sun Oct 09, 2005 10:52 pm
Location: Vienna, Austria

Return to Kofax Transformation Modules General Discussion

Who is online

Users browsing this forum: No registered users and 2 guests