Kofax Transformation VBA Script to update field value

Question

Im not really used on Kofax technologies and I have a Kofax Transformation form with fields on 2 different tabs. Here is an abstract of this form on which I have to interact on validation process.

Among those fields, I try to update the content of some of them with a validation rule on validation stage. I simply created a multi field validation rule and mapped correctly the fields.

It was proposed a basic script to check if the fields are valid or not. Based on this script I tried some logic. The Objective is to set the content of a field (which is empty and required) based on a basic condition on the second field. My objective (later) will be to fill / update the fields based on the “Siret” field value with a database call. My validation rule is the following : I check the “Siret” string length (it should be a 14 chars string). If this is true, I set the Validation to true and set the other field a value.

Private Sub Validation_Validate(ByVal ValItems As CASCADELib.CscXDocValidationItems, ByVal pXDoc As CASCADELib.CscXDocument, ByRef ErrDescription As String, ByRef ValidField As Boolean)
   Dim strNAF As String
   Dim strSiret As String

   strNAF = ValItems.Item("NAF").Text
   strSiret = ValItems.Item("Siret").Text

   ' enter your own validation rule here
   If Len(strSiret) <= 14 Then
      pXDoc.Fields.ItemByName("NAF").Text = "GOOD JOB"
      ValidField = True
   Else
      ValidField = False
      ErrDescription = "Describe the error here"
   End If
End Sub

This validation should occurred when I press key enter on the “Siret” input field. It doesn’t seem to work actually. I wonder what is going wrong at this stage.

Answer 1

The best place to have a field's value changed depends on your use case. Kofax Transformation Modules follows a distinct pattern, and you should always try to follow it. For example, when you find yourself putting code in the AfterExtract method, you should reconsider -- there is almost always a better way. For a tl;dr just jump to Where to set values?

Natural Order

When you observe a field in Validation, here's what happens behind the scenes:

1. A locator detects information (i.e. one or more alternatives).
2. The locator is assigned to a field. The field will always hold the topmost alternative. By default, your field will be valid if there is only a single confident alternative, or if the second best alternative has a 20% lower confidence.
3. Your field may or or may not have a formatter associated with it. The formatter can make the field invalid (or even valid, if desired)
4. You field can be part of one or more validation rules. Validation rules can make fields valid or invalid, but they only fire if formatting was successful.

Example

Here's an example. Imagine you want to detect dates, and you want them in database format (yyyyMMdd). The dates on your documents are in US format (MM/dd/yyyy), and there are two, but the first one has "invoice date" as keyword besides it.

1. You configure a format locator to pick up dates, along with a keyword. The locator yields two alternatives: 01/20/2020 at 100% and 01/10/2020 at 0%.
2. Since the second alternative is not considered (below 10% threshold), the field will hold the first one.
3. Since the first alternative of the field in confident (80%), formatting is applied. The formatter changes the field's value to 20200120, and the field is still valid.
4. You configure a date validation method, and use said method for the field. You check whether the date is in the future or not, and let's say today's January 19. The field is now invalid since 20200120 is one day in the future.

This brings us back to the original question - where you you change a field's value? Changing it in a validation script is possible, but bear in mind that this breaks the natural order of things. Even worse, if formatting fails in the first place your code never executes.

Where to set values?

Depending on the use case, here are my recommendations:

• You need to set a dynamic value, get values from an external system, or anything else that does not depend on another field/value in KTM: create a script locator and link it to your field. In script, create a new alternative and set it to the desired value. You can even make this dependent on other locators (since locators execute in sequence), but not on other fields (since fields are populated AFTER all locators have fired).
• You want to normalize values. For example, days of the week should be represented by letters (SUN, MON, TUE, et cetera). You still want your users to enter numeric values (1 = SUN, 2 = MON, 3 = TUE). Create a script formatter and link it to your field. The benefit here is that you don't have to repeat yourself (for example in a script locator and later in validation).
• You want to change values based on a user's interaction. You can use any event in validation (such as ButtonClicked, AfterFieldChanged or AfterFieldConfirmed), just keep in mind that some are not supported by Thin Client Validation (such as AfterFieldChanged). Keep in mind that you want to change the pointer and not the field itself since validation methods can be used by any number of fields. In your example, pXDoc.Fields.ItemByName("NAF").Text = "GOOD JOB" would violate this principle (i.e. making validation methods reusable -- your rule is now tied to the NAF field). Change the validation object instead; e.g. ValItems.ItemByName("NAF").Text = "GOOD JOB". Also keep in mind that changing a field at this point will NOT call the formatter associated with your field automatically, so make sure to provide a value that's already formatted. You can however call formatting in script manually.

Your requirement

Now, back to your original requirement:

The Objective is to set the content of a field (which is empty and required) based on a basic condition on the second field. My objective (later) will be to fill / update the fields based on the “Siret” field value with a database call. My validation rule is the following : I check the “Siret” string length (it should be a 14 chars string). If this is true, I set the Validation to true and set the other field a value.

This depends on whether you want your users to be able to change the second field during validation. If not, go for a script locator. Note that script locators can have two subfields, and each subfield can be assigned to a different field.

If your users should be able to change it, go for multi-field script validation. Both fields should be part of the validation, and a length check should be the first thing you do. Then, if Siret has more than 14 characters, issue the database call.

A word about DRY and General Design

Not knowing your exact requirements, here are some thoughts about reusability. Let's say that Siret isn't always keyed in manually by users - in fact, a locator might pick up said text. This is where you want to create a specific method for calling the database and returning a result. Note that KTM has native support for relational databases, and you can even access this model in script.

Another alternative is to use local or remote fuzzy databases along with a database locator (again, if Siret is present on your document).

Answer 2

Thanks to Wolfgang and my team, I finally solved my issue. Here is the code used to manage this :

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

   Select Case pField.Name
         Case "FieldNameInForm"
            ' simple check if field empty
            If(pXDoc.Fields.ItemByName("FieldNameInForm").Text <> "") Then
               completeForm(pXDoc, pXDoc.Fields.ItemByName("FieldNameInForm").Text)
            End If
      End Select
End Sub


Private Sub completeForm(ByVal pXDoc As CASCADELib.CscXDocument, ByVal myString As String)

   'define required properties
   Dim rs As ADODB.Recordset
   Dim cn As ADODB.Connection
   Dim sqlRequest As String
   Dim dbHostServer As String
   Dim dbUsername As String
   Dim dbPassword As String
   Dim dbName As String
   Dim dbConnString As String

   'Retrieve information for DB Connection (in ScriptVariables.xml)
   dbHostServer = "localhost"
   dbUsername = "root"
   dbPassword = "root"
   dbName = "mydatabase"

   'build the connection string and open connection to database
   dbConnString = "Provider=MSDASQL;Driver={MySQL ODBC 5.3 Unicode Driver};
   dbConnString = dbConnString & "Server=" & dbHostServer & ";"
   dbConnString = dbConnString & "UID=" & dbUsername & ";"
   dbConnString = dbConnString & "PWD=" & dbPassword & ";"
   dbConnString = dbConnString & "database=" & dbName

   'Create recordset and set connection
   'Prapare the db connection
   Set rs = New ADODB.Recordset : : Set cn=New ADODB.Connection

   cn.ConnectionString = dbConnString : cn.Open

   'build query with concatenation
   sqlRequest = "SELECT field1, field2, field3 FROM table"
   sqlRequest = sqlRequest & " where fieldN= '" & myString & "'

   'Execute the SQL request
   Set rs = cn.Execute(sqlRequest)

   ' if the recordset returns a result
   If (rs.EOF Or rs.BOF) Then
      rs.MoveFirst

      pXDoc.Fields.ItemByName("formField1").Text = CStr(rs.Fields("field1"))
      Call ValidStrField("formField1", pXDoc.Fields.ItemByName("field1").Text, pXDoc)

      pXDoc.Fields.ItemByName("formField2").Text = CStr(rs.Fields("field2"))
      Call ValidStrField("formField2", pXDoc.Fields.ItemByName("field2").Text, pXDoc)

      pXDoc.Fields.ItemByName("formField3").Text = CStr(rs.Fields("field3"))
      Call ValidStrField("Commune", pXDoc.Fields.ItemByName("field3").Text, pXDoc)

   'ifthe recordset do not return a value, we set to Undefined
   Else
      pXDoc.Fields.ItemByName("formField1").Text = "Undefined"
      pXDoc.Fields.ItemByName("formField2").Text = "Undefined"
      pXDoc.Fields.ItemByName("formField3").Text = "Undefined"
      MsgBox("No result found in database")
   End If

   ' Close connection & recordset
   rs.Close : Set rs = Nothing
   cn.Close  : Set cn=Nothing    
End Sub

' methods To validate Fields
Private Sub ValidStrField(ByVal StrItem As String,ByVal StrVal As String,ByVal pXDoc As CASCADELib.CscXDocument)
      pXDoc.Fields.ItemByName(StrItem).Text = StrVal
      pXDoc.Fields.ItemByName(StrItem).ExtractionConfident = True
      pXDoc.Fields.ItemByName(StrItem).Confidence = 100.00
      pXDoc.Fields.ItemByName(StrItem).ForcedValid = False
      pXDoc.Fields.ItemByName(StrItem).Valid = True
End Sub

Private Sub UnValidStrField(ByVal StrItem As String,ByVal StrVal As String,ByVal pXDoc As CASCADELib.CscXDocument)
      pXDoc.Fields.ItemByName(StrItem).Text = StrVal
      pXDoc.Fields.ItemByName(StrItem).ExtractionConfident = False
      pXDoc.Fields.ItemByName(StrItem).Confidence = 0.00
      pXDoc.Fields.ItemByName(StrItem).ForcedValid = True
      pXDoc.Fields.ItemByName(StrItem).Valid = False
End Sub