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

Note: The below functions were designed specifically 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 database using the SQL Query Analyser tool. (Requires SQL Administrative privileges)

CODE

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.