If you create a Select script for a table that calls a stored procedure, you may have to prompt the user for the value of a parameter to pass to the procedure. This method allows you to do that. It's similar to GetValuesForParameter, except the parameter is associated with a field; that is, it displays the caption for the field and the user can click the Values button (if allowed for that field) to see a list of the distinct values available.

Syntax

GetValuesForField(FieldName as String, Operator as String
  [, Values as Collection]) as Object

Parameters
FieldName
The name of the field (aliased with the table name) to get the value for.

Operator
The operator to use; one of the following:

• equals
  
• not equals
  
• begins with
  
• not begins with
  
• contains
  
• not contains
  
• greater than
  
• greater than or equal
  
• less than
  
• less than or equal
  
• between
  
• not between
  
• is one of
  
• not is one of
  

Values
A collection of values used as the default values for the dialog. Use the GetValuesCollection method to create the collection and its AddItem method to add the default values. Supply two values for the between and not between operators, up to ten values for the is one of and not is one of operators, and only one value for the rest of the operators. This parameter is optional; if it isn't specified, blank values are used as defaults.

Return Value
A collection of value objects. A value object has a single property, Value, which contains the value the user entered for the field value. If the user chooses Cancel rather than entering a value, the collection Count property is 0. Otherwise, the collection contains two members for the between and not between operators, up to ten members for the is one of and not is one of operators, and one member for the others.

Example
This example, used for the Select script for a table, calls a stored procedure named SalesByCategory to get the data for the table. This code calls the GetValuesForField method to get the category parameter.

Visual FoxPro

lparameters toApplication as SQApplication, toDatabase as Database, ;
  tcSelect, tcCursor
local lcField, loValues, loValue, lcCategory, lcYear, ;
  lcSelect, llReturn
lcField = 'Categories.CategoryName'
loValues = toApplication.DataEngine.GetValuesForField(lcField, ;
  'equals')
if loValues.Count > 0
  loValue = loValues.Item(1)
  lcCategory = ['] + loValue.Value + [']
  loValues = toApplication.DataEngine.GetValuesForParameter('Year', ;
    'C', 4, 0, 'equals')
  if loValues.Count > 0
    loValue = loValues.Item(1)
    lcYear = ['] + loValue.Value + [']
    lcSelect = 'exec SalesByCategory ' + lcCategory + ', ' + lcYear
    llReturn = not empty(toDatabase.ExecuteSQLStatement(lcSelect, ;
      .NULL., tcCursor))
  endif
endif
return llReturn

VBScript

function Main(Application, Database, SelectStatement, CursorName)
dim Values, Value
Main = ""
Field = "Categories.CategoryName"
set Values = Application.DataEngine.GetValuesForField(Field, _
  "equals")
if Values.Count > 0 then
  set Value = Values.Item(1)
  Category = "'" & Value.Value & "'"
  set Values = Application.DataEngine.GetValuesForParameter("Year", _
    "C", 4, 0, "equals")
  if Values.Count > 0 then
    set Value = Values.Item(1)
    Year = "'" + Value.Value + "'"
    SelectStmt = "exec SalesByCategory " & Category & ", " & Year
    Main = Database.ExecuteSQLStatement(SelectStmt)
  end if
end if
end function

JavaScript

function Main(Application, Database, SelectStatement, CursorName) {
var XMLResult, Field, Values, Value, Category, SelectStmt, Year ;
XMLResult = "" ;
Field = 'Categories.CategoryName' ;
Values = Application.DataEngine.GetValuesForField(Field, 
  'equals') ;
if (Values.Count > 0) {
  Value = Values.Item(1) ;
  Category = "'" + Value.Value + "'" ;
  Values = Application.DataEngine.GetValuesForParameter('Year', 
    'C', 4, 0, 'equals') ;
  if (Values.Count > 0) {
    Value = Values.Item(1) ;
    Year = "'" + Value.Value + "'" ;
    SelectSmt = 'exec SalesByCategory ' + Category + ', ' + Year ;
    XMLResult = Database.ExecuteSQLStatement(SelectStmt +
      Category + ', ' + Year) ;
  }
}
return XMLResult ;
}

The code starts by calling the GetValuesForField method of the DataEngine object of the passed Application object. This method displays a dialog from which the user can enter a filter value for a field; this value is passed to the stored procedure this code calls. GetValuesForField returns a values collection, so if the user entered any values, the first item in the collection is retrieved. Next, the GetValuesForParameter method is called. This method is similar to GetValuesForField, except it prompts the user for a parameter value that isn't associated with a field. If the user chose a value, the first item in the values collection is retrieved, and the two parameter values are passed to the SalesByCategory stored procedure by calling the ExecuteSQLStatement method of the passed Database object. The VBScript and JavaScript code returns the result set as XML and the Visual FoxPro code creates a cursor.

See Also
DataEngine Object | GetValuesForParameter | Select