96. How to configure KFI field validation on a DM system Lookup Field

Note: The below functions were designed specificaly for validating a KFI field value against a Hummingbird 6.04 Combo fields' values list and that this may not work with other systems.

Please contact us for help with setting this up other DM systems.

Step1. Create a ODBC connection to the SQL Server on the EzeScan workstation.

Step2. Add the following two functions to the DM systems databse using the SQL Query Analyzer tool. (Requires SQL Administrative privilleges)

Function1. fnSplit - splits lookup values using the specified delimiter.

CREATE FUNCTION dbo.fnSplit

(   

@sInputList VARCHAR(8000)

  , @sDelimiter VARCHAR(8000)

)

RETURNS @List TABLE (item VARCHAR(8000))

BEGIN

DECLARE @sItem VARCHAR(8000)WHILE CHARINDEX(@sDelimiter,@sInputList,0) 0

BEGIN

SELECT  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList)))) 

IF LEN(@sItem) > 0

INSERT INTO @List SELECT @sItem

END

IF LEN(@sInputList) > 0

INSERT INTO @List SELECT @sInputList

RETURN

END

Function2. fnValidateLookupItem - returns the specified Lookup Value if a match is made, else a null value is returned.    

CREATE FUNCTION dbo.fnValidateLookupItem ( @sField VARCHAR(255), @sLookupValue VARCHAR(2000))

RETURNS VARCHAR(8000)

AS

BEGIN

  DECLARE @sValues VARCHAR(8000), @sValue VARCHAR(8000)

  select @sValues = CAST(OBJECTITEMS AS VARCHAR(8000)) from DOCSADM.DOCSCOLUMN where COLNAME = @sField

  select @sValue = item from dbo.fnSplit(@sValues,';') where item = @sLookupValue

RETURN (@sValue)

END

Step3. Open the EzeScan KFI Admin forms Fields tab and select edit on the KFI field that will be using the field validation. Then on the KFI Field Properties form that appears open the Processing tab and enable the option "Validate the input data using an ODBC lookup". Now click on the ODBC... button and do the following -

> Fill in the DSN, Userid, and Password fields

> Select the connector option "Return value based on a placeholder value

> Type in the below SQL Statement

select dbo.fnValidateLookupItem('COLUMNNAME','')

Note you will need to replace the text COLUMNAME with the SQL Column Name of the Combo Field, and replace the ? with the current KFI field number.

Now click ok, run the Job to test the new settings.