How To Create Custom Query Form That Uses Multiple Optional Queries

[nigeldude]

Hi guys, I've been stump on this for a while and cant figure it out. I want to create my own customized QBF.
Please have a look at the attached image, it shows an embedded browse with over 24000 records.

Currently, The form is opened 1st, which takes some time over the network, and I expect this to take longer as time goes by and more records is added, approximately 500 per day.
After the form is open, the user clicks on the "Query" button which opens the dialogue shown (this also takes some time.) on the Query dialogue the user enters the desired search criteria and then hit the search button. Then all queries are run:
1st Action) Show all records
2nd Action) Invoice_Nu = Var->QueryBy_InvNumber If Isnotblank(Var->QueryBy_InvNumber)
3rd Action) (time >= Var->QueryBy_STime ) .and. (time <= Var->QueryBy_ETime ) If Isnotblank(time >= Var->QueryBy_STime)
4th Action) Invoice_Header->Custom_Id = Var->QueryBy_Customer If Isnotblank(Var->QueryBy_Customer)
And so fort until the last option "Company". Each query is set to be combined with existing queries.

The problem with this method is, it takes way too long to open the form because it has to load all the records over the network, then I have to run 7 different queries, meaning I have to search the entire database 7 times if the user should fill in all 7 options, this takes way too long. I would love to open the Query dialogue 1st, then when the user fills in the desired fields and click search, all the options are combined and the database is searched only once, then the target form is opened, displaying only the records that satisfy the query. I cant figure out how to do this, and the built in QBF is too complex for the average user, I want to create my own.

Any pointers anyone?

[Tom Cone Jr]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

You could collect the search terms in an xdialog and then have your xdialog build the final query filter expression. This is no small task and will reveal to you how much work Alpha had to do behind the scenes in order to do query by form.

-- tom

[nigeldude]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

Tom, I have absolutely no idea how to do what u just told me.

[Tom Cone Jr]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

Nigel, xdialog is powerful and flexible. But it requires study. There are tutorials and examples in the help system.

An alternative would be to use action scripting to prompt the user for the search criteria one at a time, storing each response in a separate variable. Then your script would build the filter expression from the variables.

[nigeldude]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

I was hoping there wud be a fairly easy concept that would accomplish the task with action scripting, but I guess there's no such.

[Stan Mathews]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

Maybe this will help. Two possible variables to use in a filter.

firstname = "Joe"
Lastname = "Smith"
[COLOR="#0000FF"]'both populated in this instance[/COLOR]
filt1 = "firstname = "+quote(firstname)
filt2 = "lastname = "+quote(lastname)
query.filter = if(firstname > "",filt1,"") + ".and. "+ if(lastname > "",filt2,"")

? query.filter
= firstname = "Joe".and. lastname = "Smith"


[COLOR="#0000FF"]'only first name in this instance[/COLOR]
firstname = "Sam"
Lastname = ""
filt1 = "firstname = "+quote(firstname)
filt2 = "lastname = "+quote(lastname)

query.filter = if(firstname > "",filt1,"") + ".and. "+ if(lastname > "",filt2,"")
? query.filter
= firstname = "Sam".and. 

[COLOR="#0000FF"]'but we can get to the correct filter by stripping .and. off either end it might appear.[/COLOR]
? rtrim(ltrim(query.filter," .and. ")," .and. ")
= firstname = "Sam"

[johnkoh]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

Stan,

Thank you for your brilliant Idea,

'but we can get to the correct filter by stripping .and. off either end it might appear.
? rtrim(ltrim(query.filter," .and. ")," .and. ")
= firstname = "Sam"

[Stan Mathews]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

It's surely not original in design and quite probably not original in context.

[johnkoh]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

I used Select ... Case ... end Select but still too long which I do not like and looking for better way to handle

    SELECT
        CASE A <> ""
            Filter = "A do this ...
        CASE A <> "" .and. b <> " "
            Filter = "B Case
        CASE A  = "" .and. b<>"" 
           Filter = "C case do ...
        CASE C <> "" 
           Filter = "Do d case ..
        CASE else
           Filter = "else case"
    end SELECT
           Filter = replace_Parameter(filter,local_variable())

I have not tried yet but, I think, I can build a simple filter with the Stan's idea

[Stan Mathews]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

Select...End Select

only processes the first true case, not all of the true cases, so that construct couldn't be used to build a multi variable query filter.

Unless you used something like

  SELECT
        CASE A <> "" .and. B <> "" .and. C<>"" .and. D<>"" 'etc
            Filter = use all variables
        CASE A <> "" .and. B <> " " .and. C <> " " 
            Filter = "B Case
        CASE A  = "" .and. b<>"" 
           Filter = "C case do ...
        CASE C <> "" 
           Filter = "Do d case ..
        CASE else
           Filter = "else case"
    end SELECT
           Filter = replace_Parameter(filter,local_variable())

[natn1251]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

Hello Nigel
Here is an example of a similar thing I did 10 years ago in Alpha5 v5 and will still work today. The form had variables on it which the user would fill in or use Smart Buttons to populate. This would build the list of reports available then when the report was selected and the Print Button was pushed the magic would happen. The form image is attached

Consolidtor.jpg

and here is the code.
Code

OPTION RELAX
SELECT
CASE Alltrim(var->vtable) = "Arc_Monthly_Inv"
tblrs = table.open("arc_mnthly_inv")
goto qa
CASE Alltrim(var->vtable) = "Bank"
tblrs = table.open("bank")
goto qb
CASE Alltrim(var->vtable) = "Bankout"
tblrs = table.open("bankout")
goto qb
CASE Alltrim(var->vtable) = "Custom_pnl_reportarc"
goto done
CASE Alltrim(var->vtable) = "Employee"
tblrs = table.open("employee")
goto storeonly
CASE Alltrim(var->vtable) = "InventoryControl"
goto done
CASE Alltrim(var->vtable) = "Menumkr"
goto qa
CASE Alltrim(var->vtable) = "purchasejournal"
goto done
CASE Alltrim(var->vtable) = "Pay_Wk1"
goto done
CASE Alltrim(var->vtable) = "Pay_Wk2"
goto done
CASE Alltrim(var->vtable) = "Pay_Wk3"
goto done
CASE Alltrim(var->vtable) = "Product"
tblrs = table.open("product")
goto qb
CASE Alltrim(var->vtable) = "Recipie"
goto done
CASE Alltrim(var->vtable) = "Royality"
goto done
CASE Alltrim(var->vtable) = "Salesdetail"
tblrs = table.open("salesdetail")
goto qc
CASE Alltrim(var->vtable) = "Dist_Items"
tblrs = table.open("dist_items")
goto qd
CASE ELSE
tblrs = table.open("weekly_inv")
goto qa
END SELECT
qa:
dim global rptfilter1 as c
dim global rptfilter2 as c
dim global rptfilter3 as c
dim global rptfilter4 as c
 
rptfilter1 = "DOB >= VAR->vRptStart.and.DOB <= VAR->vRptEnd " 
rptfilter2 = "DOB >= VAR->vRptStart.and.DOB <= VAR->vRptEnd.and.Store_no = VAR->vStoreno "
rptfilter3 = "DOB >= VAR->vRptStart.and.DOB <= VAR->vRptEnd.and.Store_no = VAR->vStoreno.and.VAR->vDist_nbr "
rptfilter4 = "DOB >= VAR->vRptStart.and.DOB <= VAR->vRptEnd.and.Dist_Nbr = VAR->vDist_nbr "


query.description = "ReportQuery"
query.order = "DOB"
if alltrim(var->vStoreno) = "".and.alltrim(var->vDist_Nbr) = "" then
   query.filter = VAR->rptfilter1
   goto rquery
end if
if alltrim(var->vDist_Nbr) = "" then
   query.filter = VAR->rptfilter2
   goto rquery
end if
if alltrim(var->vStoreno) = "" then
   query.filter = VAR->rptfilter4
   goto rquery
end if
query.filter = VAR->rptfilter3
END
qb:
dim global rptfilter1 as c
dim global rptfilter2 as c
dim global rptfilter3 as c
dim global rptfilter4 as c

rptfilter1 = "Date >= VAR->vRptStart.and.Date <= VAR->vRptEnd " 
rptfilter2 = "Date >= VAR->vRptStart.and.Date <= VAR->vRptEnd.and.Storeno = VAR->vStoreno "
rptfilter3 = "Date >= VAR->vRptStart.and.Date <= VAR->vRptEnd.and.Storeno = VAR->vStoreno.and.VAR->vDist_nbr "
rptfilter4 = "Date >= VAR->vRptStart.and.Date <= VAR->vRptEnd.and.Dist_Nbr = VAR->vDist_nbr "

query.description = "ReportQuery"
query.order = "Date"
if alltrim(var->vStoreno) = "".and.alltrim(var->vDist_Nbr) = "" then
   query.filter = VAR->rptfilter1
   goto rquery
end if
if alltrim(var->vDist_Nbr) = "" then
   query.filter = VAR->rptfilter2
   goto rquery
end if
if alltrim(var->vStoreno) = "" then
   query.filter = VAR->rptfilter4
   goto rquery
end if
query.filter = VAR->rptfilter3

END
qc:
dim global rptfilter1 as c
dim global rptfilter2 as c
dim global rptfilter3 as c
dim global rptfilter4 as c

rptfilter1 = "Date1 >= VAR->vRptStart.and.Date1 <= VAR->vRptEnd " 
rptfilter2 = "Date1 >= VAR->vRptStart.and.Date1 <= VAR->vRptEnd.and.Storno = VAR->vStoreno "
rptfilter3 = "Date1 >= VAR->vRptStart.and.Date1 <= VAR->vRptEnd.and.Storno = VAR->vStoreno.and.VAR->vDist_nbr "
rptfilter4 = "Date1 >= VAR->vRptStart.and.Date1 <= VAR->vRptEnd.and.Dist_Nbr = VAR->vDist_nbr "

query.description = "ReportQuery"
query.order = "Date1"
if alltrim(var->vStoreno) = "".and.alltrim(var->vDist_Nbr) = "" then
   query.filter = VAR->rptfilter1
   goto rquery
end if
if alltrim(var->vDist_Nbr) = "" then
   query.filter = VAR->rptfilter2
   goto rquery
end if
if alltrim(var->vStoreno) = "" then
   query.filter = VAR->rptfilter4
   goto rquery
end if
query.filter = VAR->rptfilter3
goto rquery
END
qd:
dim global rptfilter1 as c
dim global rptfilter2 as c
dim global rptfilter3 as c
dim global rptfilter4 as c
 
rptfilter1 = "Delv_Date >= VAR->vRptStart.and.Delv_Date <= VAR->vRptEnd " 
rptfilter2 = "Delv_Date >= VAR->vRptStart.and.Delv_Date <= VAR->vRptEnd.and.Store_nbr = VAR->vStoreno "
rptfilter3 = "Delv_Date >= VAR->vRptStart.and.Delv_Date <= VAR->vRptEnd.and.Store_nbr = VAR->vStoreno.and.VAR->vDist_nbr "
rptfilter4 = "Delv_Date >= VAR->vRptStart.and.Delv_Date <= VAR->vRptEnd.and.Dist_Nbr = VAR->vDist_nbr "


query.description = "ReportQuery"
query.order = "Delv_Date"
if alltrim(var->vStoreno) = "".and.alltrim(var->vDist_Nbr) = "" then
   query.filter = VAR->rptfilter1
   goto rquery
end if
if alltrim(var->vDist_Nbr) = "" then
   query.filter = VAR->rptfilter2
   goto rquery
end if
if alltrim(var->vStoreno) = "" then
   query.filter = VAR->rptfilter4
   goto rquery
end if
query.filter = VAR->rptfilter3
END

storeonly:
dim global rptfilter1 as c
rptfilter1 = "Store_no = VAR->vStoreno"
query.description = "ReportQuery"
query.order = ""
query.filter = VAR->rptfilter1

rquery:
query.options = ""
indx = tblrs.query_create()
rec_count = indx.records_get()
if rec_count = 0 then
	goto sall 
end if
tblrs.fetch_first()
while .not. tblrs.fetch_eof()
	tblrs.change_begin()
	tblrs.mark()
	tblrs.change_end(.t.)
	tblrs.fetch_next()
end while
tblrs.query_detach_all()
goto done
sall:
tblrs.unmark_range(".T.")
tblrs.close()
ui_msg_box("SORRY!","Your Query returned no records")
END
done:
tbl = table.current()
if vReport = "L" then
	script_play("Payroll_Report")
 	select
 		case vReport_Name = "WB Detail_weekly1"
 			if tbl.payroll = "Bi-Monthly" then
				vReport_Name = "WB Detail_weekly1"
			else
				vReport_Name = "WB Detail_weekly1"
			end if
 		case vReport_Name = "WB Detail_weekly2"
 			vReport_Name = "WB Detail_weekly2"
 		case vReport_Name = "Detail_Wk3"
 			vReport_Name = "Detail_Wk3"
 		case vReport_Name = "WB Summary_weekly1"
 			if tbl.payroll = "Bi-Monthly" then
				vReport_Name = "WB Summary_weekly1"
			else
				vReport_Name = "WB Summary_weekly1"
			end if
 		case vReport_Name = "WB Summary_weekly2"
 			vReport_Name = "WB Summary_weekly2"
		case vReport_Name = "Summary_Wk3"	
			vReport_Name = "Summary_Wk3"
		case vReport_Name = "Summary_All"
			if tbl.payroll = "Bi-Monthly" then
				vReport_Name = "Summarybimonth"
			else
				vReport_Name = "Summarybiweek"
			end if
 	end select
 elseif vReport = "E" then
	script_play("Employee Cash Results")
end if


prompt_result = ui_get_print_or_preview("Print")
If prompt_result = "Print" then
	:Report.Print(var->vReport_name)
	goto cleanup
Else if prompt_result = "Preview" then 
	:Report.Preview(var->vReport_name)
	goto cleanup
End if
cleanup:
tblrs.unmark_range(".T.")
tblrs.close()
END

Notice we built variables called rptfilter 1 thru the nbr of choices then set the values for each variable which is then passed to the report query filter.
Hope this helps

[Ray in Capetown]

Re: How To Create Custom Query Form That Uses Multiple Optional Queries

I was hoping there wud be a fairly easy concept that would accomplish the task with action scripting, but I guess there's no such.

Nigel,
Dude
Here's a simple approach I would go with.
A handful of variables on the form and two lines of code on a button - follow the example attached.

Construction of string
Start with condition for code if entered else ".t." and Ref if entered

cf=if(vcod="",".t.","code=trim(var->vcod)")+if(vref="","",".and. ref=trim(var->vref)")

and add the "betweens" and "Contains" as one string

+" .and. date>=vdfr .and. date<=vdto .and. abs(amt)>=vaml .and. abs(amt)<=vamh"+" .and. trim(vdes)$desc"
topparent.QueryRun(cf,"recno()")