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.

Step One - Create a ODBC connection to the SQL Server on the EzeScan workstation.


Step Two 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


Step Three 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 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.
Direct link to FAQ