PDA

View Full Version : XBasic - Best and Fastest Way?


ABC123

gschepp
02-28-2007, 05:22 PM
What is the best and hopefully faster way to create code for a filter having options to select county and then year? There are 92 counties and each county has 5 years to select.




'Create an XDialog dialog box to prompt for parameters.
DIM SHARED vcCounty as C
DIM SHARED varC_result as C
auto_list_vcCounty = table.external_record_content_get("federal_elections","County -\"^\" +Year","County+Year",".t. .and. unique_key_value()")

temp_count = w_count(auto_list_vcCounty,crlf())
DELETE a_vcCounty
DIM a_vcCounty[temp_count] as c
a_vcCounty.initialize(auto_list_vcCounty)
ok_button_label = "&OK"
cancel_button_label = "&Cancel"
varC_result = ui_dlg_box("Alpha Five",<<%dlg%
{region}
Select County and Year:| [%S=BLR;D="^"%.20,11vcCounty^<a_vcCounty];
{endregion};
{line=1,0};
{region}
<*15=ok_button_label!OK> <15=cancel_button_label!CANCEL>
{endregion};
%dlg%)

'Conditional code follows. Executes only if the condition expression is True.
IF a5_eval_expression("=left(Var->vcCounty,1)=\"A\"") THEN
'Adams, Allen
DIM records_found as N
records_found = topparent.queryrun("Case(Left(Var->vcCounty,4)=\"Adam\", Case(Right(Var->vcCounty,4)=\"1998\", County- Year =\"Adams1998\", Right(Var->vcCounty,4)=\"2000\", County- Year =\"Adams2000\", Right(Var->vcCounty,4)=\"2002\", County- Year =\"Adams2002\", Right(Var->vcCounty,4)=\"2004\", County- Year =\"Adams2004\", Right(Var->vcCounty,4)=\"2006\", County- Year =\"Adams2006\"), Left(Var->vcCounty,4)=\"Alle\", Case(Right(Var->vcCounty,4)=\"1998\", County- Year =\"Allen1998\", Var->vcCounty=\"Allen^2000\", County- Year =\"Allen2000\", Var->vcCounty=\"Allen^2002\", County- Year =\"Allen2002\", Var->vcCounty=\"Allen^2004\", County- Year =\"Allen2004\", Var->vcCounty=\"Allen^2006\", County- Year =\"Allen2006\")","","","No","",.f.)
END IF

'Conditional code follows. Executes only if the condition expression is True.
IF a5_eval_expression("=left(Var->vcCounty,1)=\"B\"") THEN
'Bartholomew, Blackford, Blackford, Boone, Brown
DIM records_found as N
records_found = topparent.queryrun("Case(Left(Var->vcCounty,4)=\"Bart\", Case(Right(Var->vcCounty,4)=\"1998\", County- Year =\"Bartholomew1998\", Right(Var->vcCounty,4)=\"2000\", County- Year =\"Bartholomew2000\", Right(Var->vcCounty,4)=\"2002\", County- Year =\"Bartholomew2002\", Right(Var->vcCounty,4)=\"2004\", County- Year =\"Bartholomew2004\", Right(Var->vcCounty,4)=\"2006\", County- Year =\"Bartholomew2006\"), Left(Var->vcCounty,4)=\"Bent\", Case(Right(Var->vcCounty,4)=\"1998\", County- Year =\"Benton1998\", Right(Var->vcCounty,4)=\"2000\", County- Year =\"Benton2000\", Right(Var->vcCounty,4)=\"2002\", County- Year =\"Benton2002\", Right(Var->vcCounty,4)=\"2004\", County- Year =\"Benton2004\", Right(Var->vcCounty,4)=\"2006\", County- Year =\"Benton2006\"), Left(Var->vcCounty,4)=\"Blac\", Case(Right(Var->vcCounty,4)=\"1998\", County- Year =\"Blackford1998\", Right(Var->vcCounty,4)=\"2000\", County- Year =\"Blackford2000\", Right(Var->vcCounty,4)=\"2002\", County- Year =\"Blackford2002\", Right(Var->vcCounty,4)=\"2004\", County- Year =\"Blackford2004\", Right(Var->vcCounty,4)=\"2006\", County- Year =\"Blackford2006\"), Left(Var->vcCounty,4)=\"Boon\", Case(Right(Var->vcCounty,4)=\"1998\", County- Year =\"Boone1998\", Right(Var->vcCounty,4)=\"2000\", County- Year =\"Boone2000\", Right(Var->vcCounty,4)=\"2002\", County- Year =\"Boone2002\", Right(Var->vcCounty,4)=\"2004\", County- Year =\"Boone2004\", Right(Var->vcCounty,4)=\"2006\", County- Year =\"Boone2006\"), Left(Var->vcCounty,4)=\"Brow\", Case(Right(Var->vcCounty,4)=\"1998\", County- Year =\"Brown1998\", Right(Var->vcCounty,4)=\"2000\", County- Year =\"Brown2000\", Right(Var->vcCounty,4)=\"2002\", County- Year =\"Brown2002\", Right(Var->vcCounty,4)=\"2004\", County- Year =\"Brown2004\", Right(Var->vcCounty,4)=\"2006\", County- Year =\"Brown2006\"),)","","","No","",.f.)
END IF

Stan Mathews
02-28-2007, 05:40 PM
What is the best and hopefully faster way to create code for a filter having options to select county and then year? There are 92 counties and each county has 5 years to select.

Somewhat hard to follow without a sample of the table but it seems you are gathering the user input into one variable and then filtering the ? form ? by reconstructing the left and right parts of the variable. I would think that you could collect two values in the xdialog (county and year) and construct an appropriate filter by parsing the collected values.

JohnZaleski
02-28-2007, 06:02 PM
As far as speed goes, you can maximize it by creating a calculated field in the table which is a concatenation of the two fields, Then create an index on this field to insure LQO. Let's suppose the calc field is called "combo".
After collecting the two values build a variable which is also a concatenation of the two:the filter would be combo = var->concat. It should be almost instantaneous.

gschepp
02-28-2007, 06:23 PM
As far as fastest way I actually meant fastest way to write the code.

I originally did the filter using tree control which is why I was coding for a substr of the variable which was in the format "countyname^year".