Validating your custom drop-down list

The Customizable UDF (CUDF) feature in VISUAL allows you to create and define your own list of fields that you can integrate into the VISUAL interface and capture information particular to your business.

The drop-down list is one such field you can add.  When you create the down-down CUDF you have the option to populate the list manually or link it to data in your VISUAL database.

Validating your custom drop-down list

The drawback to these drop-down lists is that VISUAL does not validate the information entered against the available options in the list.  It’s of no fault to VISUAL.  I’ve been calling this field a drop-down list but it’s actually a ComboBox.  A combobox is a combination of a drop-down list and a single-line textbox, allowing the user to either enter data directly or choose from the list of existing items.

So basically it’s a free form field – allowing the user to enter anything.  This may be a problem if you are depending on your users to only enter values that are defined in the list.

To enforce this you will need to do this yourself via a database trigger, workflow, macro, or a big heavy stick.

So before you grab that stick – the example below may give you an idea how you can handle this using a macro.

The macro validates the data entered in a CUDF by doing a database lookup on the USER_DEF_FIELDS table which contains down-down definitions.  Apart for the database connection information – you will need to provide the following macro variables:

Variable: UDF_Caption
This is the caption (label) of the drop-down field.  In my example I’m using “Customer Service Rep”.  NOTE: I don’t use the UDF-XXXXXXX identifier because I seen VISUAL renumber these IDs.

Variable: UDF_VEModule
Specify the VISUAL program/module (VMORDENT, VMPURENT, etc)

Variable: UDF_Area
Where in the module the CUDF is contained.
Header area = “”
Grid Line Item = “tblColLineItem”
Delivery Schedule = “tblEditDelSched”

Variable: UDF_VALUE
The CUDF string (value) to validate.  In my example, I’m using udfStrCombo1 which is the CUDF field variable containing the Customer Service Rep value

In my example, I’m validating the value entered in the Customer Service Rep field which is a drop-down list.
Download the macro

 

‘********************************************
‘* DATABASE CONNECTION TO SQL SERVER

strServerName=”E N T E R    N A M E    O F    D A T A B A S E    S E R V E R”
strDatabase=”E N T E R    N A M E    O F    D A T A B A S E”
strUserName=”E N T E R    U S E R N A M E”
strPassword=”E N T E R    P A S S W O R D”

strcnn=”Driver={SQL Server};Server=” & strServerName & “;Database=” & strDatabase & “;Uid=” & strUsername & “;Pwd=” & strPassword & “;”

‘********************************************

‘Variable for caption (label) of the CUDF to validate
UDF_Caption=”Customer Service Rep”

‘ Variable to indicate where the CUDF is located in the module
‘ empty string = Header area
‘ tblColLineItem = Grid line item 
‘ tblEditDelSched = delivery schedule
UDF_Area=””

‘Variable to indicate the VISUAL program(module)
‘VMORDENT = Customer Order Entry
‘VMPURENT = Purchase Order Entry
‘ etc….
UDF_VEModule=”VMORDENT”

‘Variable for string value (user input) to validate
UDF_VALUE=udfStrCombo1

set rs = CreateObject(“ADODB.Recordset”)
set rsCHK = CreateObject(“ADODB.Recordset”)

SQL=”SELECT STRING_VAL  ” & _
“FROM USER_DEF_FIELDS UDF ” & _
“WHERE UDF.PROGRAM_ID='” & UDF_VEModule & “‘ AND UDF.LABEL = ‘” & UDF_Caption & “‘ AND UDF.DATA_TYPE = 1005 AND ISNULL(UDF.TABLE_ID,”) = ‘” & UDF_Area & “‘;”
rs.open SQL, strcnn

if not rs.eof then
 rs.movefirst
 arrList=split(rs(“STRING_VAL”),”,”)
 if ubound(arrList)>0 then

  SELECT CASE arrList(0)
  
   CASE “l=1”
    ‘*** STATIC LIST
    SQL=”SELECT UDF.STRING_VAL FROM USER_DEF_FIELDS UDF LEFT OUTER JOIN USER_DEF_FIELDS AS UDF1 ON UDF.ID = UDF1.ID WHERE (UDF.PROGRAM_ID = ‘STATIC_DATA’) AND (UDF.STRING_VAL = ‘” & CUDF_VALUE & “‘) AND (UDF1.LABEL = ‘” & UDF_Caption & “‘) AND  (ISNULL(UDF1.TABLE_ID,”) = ‘” & UDF_Area & “‘) AND (UDF1.DATA_TYPE = 1005) AND (UDF1.PROGRAM_ID = ‘” & UDF_VEModule & “‘)”

   CASE “l=2”
    ‘*** DYNAMIC LIST FROM DB
    UDF_DBTable=mid(arrList(1),3,100)
    UDF_DBCol=mid(arrList(2),3,100)
    SQL=”SELECT ” & UDF_DBCol & ” FROM ” & UDF_DBTable & ” WHERE ” & UDF_DBCol & “='” & CUDF_VALUE & “‘”

  END SELECT

  rsCHK.open SQL, strcnn
  if rsCHK.eof then
   msgbox “Error: user input does not match available values in list”
  else
   msgbox “We’re good”
  end if
  
  rsCHK.close

 end if
end if
rs.close

 

Copyright 2019 visualnuggets © All Rights Reserved