The DataEngine.BeforeCreateSQLStatement script is called before Stonefield Query creates the SQL statement for the report, allowing you to make changes to things, such as filter conditions, that affect the SQL statement. You can, for example, display your own filter dialog, prompting the user for additional values under certain circumstances. You can also use this to automatically add filter conditions to a query that the user doesn't see.

You can access the running report object through the global variable Report.

Parameters
A reference to the Stonefield Query Application object.

Return Value
True if the result set retrieval should continue or False if not.

Example
This is similar to the example in DataEngine.FinalizeSQLStatement, but adds FilterCondition objects to the FilterConditions collection instead, since the SQL statement hasn't been created yet.

Visual FoxPro

lparameters toApplication as SQApplication
local lcSalespersonID, loCondition as FilterCondition
lcSalespersonID = toApplication.GetRegistryValue('ID', '', ;
  'Software\MyCompany\SalesApplication')
loCondition = toApplication.DataEngine.FilterConditions.AddItem()
loCondition.FieldName = 'Salesperson.SalespersonID'
loCondition.Condition = loCondition.FieldName + " = '" + ;
  lcSalespersonID + "'"
loCondition.Connection = 'and'
loCondition.IncludeInDisplay = .F.
loCondition.Operator = 'equals'
loCondition.Values.AddItem(lcSalespersonID)

VBScript

function Main(Application)
dim Condition
SalespersonID = Application.GetRegistryValue("ID", "", _
  "Software\MyCompany\SalesApplication")
set Condition = Application.DataEngine.FilterConditions.AddItem()
Condition.FieldName = "Salesperson.SalespersonID"
Condition.Condition = loCondition.FieldName & " = '" & _
  SalespersonID & "'"
Condition.Connection = "and"
Condition.IncludeInDisplay = False
Condition.Operator = "equals"
Condition.Values.AddItem(lcSalespersonID)
end function

JavaScript

function Main(Application, SelectStatement) {
var SalespersonID, Condition ;
SalespersonID = Application.GetRegistryValue('ID', ' ', 
  'Software\MyCompany\SalesApplication') ;
Condition = Application.DataEngine.FilterConditions.AddItem() ;
Condition.FieldName = 'Salesperson.SalespersonID' ;
Condition.Condition = loCondition.FieldName + " = '" + 
  SalespersonID + "'" ;
Condition.Connection = 'and' ;
Condition.IncludeInDisplay = false ;
Condition.Operator = 'equals' ;
Condition.Values.AddItem(lcSalespersonID)
}

C#

The method in this script must be named DataEngine_BeforeCreateSQLStatement.

public static bool DataEngine_BeforeCreateSQLStatement(SFQApplication sfqApplication)
{	
  string salespersonID;

  salespersonID = sfqApplication.GetRegistryValue("ID", " ",
    @"HKEY_CURRENT_USER\Software\MyCompany\SalesApplication");

  // Please note that any changes you make to this item will only be saved in
  // Stonefield Query after you call the Dispose() method or by using
  // the 'using' syntax below which calls Dispose() for you automatically

  using(FilterCondition condition = sfqApplication.DataEngine.FilterConditions.AddItem())
  {
    condition.FieldName = "Salesperson.SalespersonID";
    condition.Condition = condition.FieldName + " = '" + salespersonID + "'";
    condition.Connection = "and";
    condition.IncludeInDisplay = false;
    condition.Operator = "equals";
    condition.Values.AddItem(salespersonID);
  }

  return true;
}

VB.NET

The method in this script must be named DataEngine_BeforeCreateSQLStatement.

public shared function DataEngine_BeforeCreateSQLStatement(sfqApplication as SFQApplication) as Boolean

  Dim salespersonID as String 

  salespersonID = sfqApplication.GetRegistryValue("ID", "NOT FOUND", 
    "HKEY_CURRENT_USER\Software\MyCompany\SalesApplication")

  ' Please note that any changes you make to this item will only be saved in
  ' Stonefield Query after you call the Dispose() method or bu using
  ' the 'using' syntax below which calls Dispose() for you automatically

  Using condition as FilterCondition = sfqApplication.DataEngine.FilterConditions.AddItem()

    condition.FieldName = "Salesperson.SalespersonID"
    condition.Condition = condition.FieldName + " = '" + salespersonID + "'"
    condition.Connection = "and"
    condition.IncludeInDisplay = false
    condition.Operator = "equals"
    condition.Values.AddItem(salespersonID)
    
  End Using

  return true

End Function

See also

DataEngine.FinalizeSQLStatement | DataEngine Object | Scripts

© Stonefield Software Inc., 2023 • Updated: 06/06/16
Comment or report problem with topic