I’m trying to use the results of a SQL query as the PK parameters for a search. I provide the user with a dialog form containing several fields they can search on. Some fields are 5 or 6 tables removed from the form table, which is why the SQL approach is necessary. I’d like to cascade through each field they specify and base each search on the results from the previous search fields. All are AND searches.
I’ve searched the forum extensively and have tried several variations of the code below.
var maxReturnedRows = 50000;
globals.find_HasCriteria = ‘false’
//CompanyName
if (globals.find_CompanyName != null)
{
var query =
‘SELECT tblLog.LogID ’ +
‘FROM tblLog INNER JOIN tblCompany ON tblLog.CompanyID = tblCompany.CompanyID ’ +
"WHERE tblCompany.CompanyName LIKE N’" + globals.find_CompanyName + "’";
var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), query, null, maxReturnedRows);
//Loop thru the dataset
for(var i = 1; i <= databaseManager.getFoundSetCount(dataset); i++)
{
controller.find();
logid = '= ’ + dataset.getValue(1, i)
controller.search(false,false);
}
globals.find_HasCriteria = ‘true’
}
//More search fields…
//Check that criteria has been specified. If true move focus to list view, otherwise open dialog.
if (globals.find_HasCriteria == ‘true’)
{
application.closeFormDialog();
forms.Log_List.controller.show();
}
else
{
globals.dialogs_NoFindCriteria();
}
Am I missing something or is this approach not possible? Also, is ithere a way to avoid looping through the found set that would improve performance?
There’s a much easier way. Just use “loadRecords()” command on the controller. You can use “Move Sample” from the loadRecords command on the controller object for an example.
Basically, you’re going to get the PK values for the table you’re searching on - and then you can just “load” them all at once.
Thanks for the input. The loadRecords() command works well for executing a single search. However, I’m trying to execute my search in multiple passes (one for each criteria), with each pass using the found set from the previous pass rather than the complete dataset.
For example, the Sales Log find screen in the app provides the user with the following search criteria. The user may specify one or more criteria.
Log No
Date Due From
Date Due To
Log Type
Person
Company
Site Name
Assigned To
Read
Done
Source
Log Text
Date Created From
Date Created To
Created By
Basically, I’m looking for a way to successively apply the PK data set from each pass, basing each pass on the reduced data set from the previous pass, and then display the final result when all passes have been completed. I thought this was possible within Servoy by using the reduce search function, but I haven’t been able to make this work. The app has 18 find screens like the one above and this would provide a simple, generic approach that could be used throughout the app.
Alternatively, I have three options for obtaining the final PK dataset that would be applied to the form. All which require extensive coding which I’d like to avoid:
Create a table in the SQL database to hold the user’s PK dataset from each search and create a join to this table in the SQL statement for each search criteria.
Create a view that includes all tables and fields that the user could possibly search on and dynamically build the where clause within Servoy for the multiple search criteria specified by the user
Dynamically generate a single SQL statement within Servoy that contains all joins and fields needed to support the multiple search criteria specified by the user.
#3 is THE WAY to go! You don’t really want to screw around with reducing the foundset by looping through all the find criteria and performing “finds” all over the place. That’s hard to maintain an VERY inflexible.
By generating the SQL statement - you have MUCH more flexible system that you can add addition search criteria to very easily.