When the user clicks the Values button in either the Filter Condition dialog or the Data Selection step of the Report Wizard, Stonefield Query displays a list of unique values for the selected field. This is done using a SELECT DISTINCT query on that field. However, there may be occasions when, due to the way the data is stored, you need a different mechanism for gathering the values to display to the user, such as calling a stored procedure. In that case, enter an expression into the Values Method property for the field. Typically, the expression calls a user-defined script.

Another use for this is to display a list of values in the Filter Condition dialog filtered on the value of a previous filter condition. For example, you may want the user to filter on a school name, then filter on a course name, but you want the list of courses displayed to only be those for the selected school.

For example, suppose the Customers table has 100,000 records but the CustomerType field only contains one of ten different values. Performing a SELECT DISTINCT query on this field might be slow, depending on the database engine you're using. Instead, you could create a script called from the Values Method expression for the field that returns the ten different values. Assuming the name of the script is GetCustomerTypes, here's what the Values Method property for the field contains:

GetCustomerTypes()

If the field has the Display field from related table option turned on, the result set you create must have two fields, the first one being the field to display from the related table and the second being the primary key for that table.

Here's the code for the GetCustomerTypes script:

Visual FoxPro

create cursor CustomerTypes (CustomerType C(30))
insert into CustomerTypes values ('Prospect')
insert into CustomerTypes values ('Good Customer')
insert into CustomerTypes values ('Dead Beat')
* other INSERT statements go here

In the case of a Visual FoxPro script, create a cursor with a single field and fill the cursor with the desired values. You can do that using hard-coded values as in this example, by calling a stored procedure, doing a SELECT from another table, or any other mechanism. The name of the cursor is unimportant but it must be in the current work area when the script is done.

In the case of VBScript, JavaScript, C#, or VB.Net, return an XML string that contains the values to be displayed. The XML can come from hard-coded values as in this example, by calling the ExecuteSQLStatement of a Database object, or any other mechanism. The element names are unimportant.

VBScript

function Main(Application)
Main = "<customertypes>" & _
  "<customertype>" & _
  "<name>Prospect</name>" & _
  "</customertype>" & _
  "<customertype>" & _
  "<name>Good Customer</name>" & _
  "</customertype>" & _
  "<customertype>" & _
  "<name>Dead Beat</name>" & _
  "</customertype>" & _
  "</customertypes>"
end function

JavaScript

function Main(Application) {
var XMLResults ;
XMLResults = '<customertypes>' +
  '<customertype>' +
  '<name>Prospect</name>' +
  '</customertype>' +
  '<customertype>' +
  '<name>Good Customer</name>' +
  '</customertype>' +
  '<customertype>' +
  '<name>Dead Beat</name>' +
  '</customertype>' +
  '</customertypes>' ;
return XMLResults ;
}

C#

public static string GetCustomerTypes(SFQApplication sfqApplication)
{
  StringBuilder XMLSettings = new StringBuilder("");
  XMLSettings.Append("<customertypes>");
  XMLSettings.Append("<customertype>");
  XMLSettings.Append("<name>Prospect</name>");
  XMLSettings.Append("</customertype>");		
  XMLSettings.Append("<customertype>");
  XMLSettings.Append("<name>Good Customer</name>");	
  XMLSettings.Append("</customertype>");	
  XMLSettings.Append("<customertype>");
  XMLSettings.Append("<name>Dead Beat</name>");	
  XMLSettings.Append("</customertype>");	
  XMLSettings.Append("</customertypes>");	
  return XMLSettings.ToString();
}

VB.NET

public shared function GetCustomerTypes(sfqApplication as SFQApplication) as string
  Dim XMLSettings As StringBuilder
  XMLSettings = New StringBuilder("")
  XMLSettings.Append("<customertypes>")
  XMLSettings.Append("<customertype>")
  XMLSettings.Append("<name>Prospect</name>")
  XMLSettings.Append("</customertype>")
  XMLSettings.Append("<customertype>")
  XMLSettings.Append("<name>Good Customer</name>")
  XMLSettings.Append("</customertype>")
  XMLSettings.Append("<customertype>")
  XMLSettings.Append("<name>Dead Beat</name>")
  XMLSettings.Append("</customertype>")
  XMLSettings.Append("</customertypes>")
  Return XMLSettings.ToString()
End Function

Here's another example: suppose the user filters on a country and if they also filter on a city, you only want to show the cities in the selected country. Specify the following for the Values Method for the city field:

GetCitiesForCountry()

Create a script named GetCitiesForCountry with the following code:

Visual FoxPro

local lcSelect, lnI, loCondition as FilterCondition, ;
    loDatabase as Database
lcSelect = 'select distinct City from Customers order by 1'
for lnI = 1 to SQApplication.DataEngine.FilterConditions.Count
  loCondition = SQApplication.DataEngine.FilterConditions.Item(lnI)
  if loCondition.FieldName = 'Customers.Country'
    lcSelect = SQApplication.DataEngine.AddToWhere(lcSelect, ;
        loCondition.Condition)
    exit
  endif
next
loDatabase = SQApplication.DataEngine.Databases.GetMainDatabase()
loDatabase.ExecuteSQLStatement(lcSelect, , 'Cities')

C#

public static string GetCitiesForCountry()
{
  string SQLStmt = "select distinct City from Customers order by 1";
  foreach (FilterCondition filtercondition in
    SQApplication.DataEngine.FilterConditions)
  {
    if (filtercondition.FieldName == "Customers.Country")
    {
      SQLStmt = SQApplication.DataEngine.AddToWhere(SQLStmt, 
        filtercondition.Condition);
    }
  }
  Database database = SQApplication.DataEngine.Databases.GetMainDatabase();
  string xml = database.ExecuteSQLStatement(SQLStmt);
  return xml;
}

VB.NET

public shared function GetCitiesForCountry() as string
  dim SQLStmt as string = "select distinct City from Customers order by 1"
  For Each filtercondition as FilterCondition in
    SQApplication.DataEngine.FilterConditions
    If filtercondition.FieldName = "Customers.Country" Then
      SQLStmt = SQApplication.DataEngine.AddToWhere(SQLStmt, 
        filtercondition.Condition)
    End If
  Next
  dim database as Database = SQApplication.DataEngine.Databases.GetMainDatabase()
  dim xml as string = database.ExecuteSQLStatement(SQLStmt)
  Return xml
End Function

This code creates a result set with only those cities in the selected country. If there is no filter condition on country, all cities are selected.