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