Fetching Forms and Reports using Queries in Alpha Software

In our last exercise we looked at using queries controlled by variables to fetch and display a range of records in a form as well as using the table method to step through the child records to populate a rich text object with memo fields from the child records. In this exercise I will demonstrate fetching forms and reports via queries versus the standard filter process.

The code displayed below is a standard xBasic way of opening a form which has no additional criteria.

DIM Shared varP_MyForm as P
DIM layout_name as c 
layout_name = "MyForm"
DIM tempP as p
tempP=obj(":"+object_Name_normalize(word(layout_name,1,"@")))
if is_object(tempP) then 
	if tempP.class() = "form" .or. tempP.class() = "browse" then 
		tempP.activate()
	else
		varP_MyForm = :Form.view(layout_name)
	end if
else 
	varP_MyForm = :Form.view(layout_name)
end if

(If you are not sure how to write this, use the Menu Code xBasic Script Genie on the code editor.)  This method sets a temp pointer to the form object name. Next it checks to see if it is an actual object and whether or  not it is in memory. If so, it simply gives it focus, otherwise it loads the form then gives focus.

If you want to load a form and fetch a specific record; below is the standard method.

query.filter = "quote_num = Var->vQt_Num"
query.order = "recno()"
query.filter = convert_expression(query.filter,"VE")
DIM Shared varP_MyForm as P
DIM layout_name as c 
layout_name = "MyForm"
dim tempP as p
tempP=obj(":"+object_name_normalize(word(layout_name,1,"@")))
if is_object(tempP) then 
	if tempP.class() = "form" .or. tempP.class() = "browse" then 
		dim flagIsBaseFilter as l 
		flagIsBaseQuery = .t.
		if flagIsBaseFilter then 
			tempP.BaseQueryRun(query.filter,query.order)
		else
			tempP.QueryRun(query.filter,query.order)		
		end if 
		tempP.activate()
	else
		varP_MyForm = :Form.viewqueried(layout_name,query.filter, query.order )
	end if
else 
	varP_MyForm = :Form.viewqueried(layout_name,query.filter, query.order )
end if

Compare the two code snippets and you will see the difference is the setting of the query and order then the QueryRun function based on the form load option and finally if the form is not in memory it is loaded using the Form.viewqueried function rather then Form.View.

Now this works great when you have a small application but if the table the form is bound to is huge with many child records, the form will take a second or two to load. Doesn’t sound like much but customers will complain about the delay. So, lets look at an alternative method to load a form.

if is_object("MyForm") then
MyForm.show()
MyForm.activate()
else
:Form.view("MyForm")
end if
Var->xSearchfor = "Quote_Num"
xflt = vQT_Num
MyForm:button1.push()

Here I simply load the form. Next I set a radio choice on the form (xSearchFor) to Quote_Num and pass the calling form variable vQt_Num to my query filter variable xFLT. Then I push a button (button1) on the form to process the query needed. Since I am loading the form with no conditions the form loads immediately and when the button runs the query it happens locally rather than externally which is much faster.

Here is the code which runs on the button I push with xBasic

t = table.current()
t.query_detach_all()
xbasic_wait_for_idle()
ui_freeze(.t.)

if xSearchFor = "Invoice_Num" then
    xflt = "Invoice_No = '" + xflt + "'"
	records_found = topparent.queryrun(Var->xflt,"","","No","",.f.)
else if xSearchFor = "Quote_Num"
    xflt = "Quote_Num = '" + xflt + "'"
	records_found = topparent.queryrun(Var->xflt,"","","No","",.f.)
else if xSearchFor = "WO_Num"
    xflt = "WO_Number = '" + xflt + "'"
	records_found = topparent.queryrun(Var->xflt,"","","No","",.f.)
end if

cond1.refresh()

vQt_Num = WO_NUM0.text
xCustID = customerid0.text 
button15.Activate()
ui_freeze(.f.)

One drawback to this method is since the table is queried you cannot use the next or previous toolbar buttons because the query drills down to just one record. The next example show how to overcome that when using the external query record form load method .

DIM Shared varP_MyForm as P
DIM layout_name as c 
layout_name = "MyForm"
dim tempP as p
tempP=obj(":"+object_name_normalize(word(layout_name,1,"@")))
if is_object(tempP) then 
	if tempP.class() = "form" .or. tempP.class() = "browse" then 
		dim flagIsBaseFilter as l 
		flagIsBaseQuery = .t.
		if flagIsBaseFilter then 
			tempP.BaseQueryRun(xfLT)
			tempP.Index_SetExplicit("Invoice_No")
		else
			tempP.BaseQueryRun(xfLT)
			tempP.Index_SetExplicit("Invoice_No")		
		end if 
		tempP.activate()
	else
		varP_MyForm = :Form.viewqueried(xfLT)
		varP_MyForm.Index_SetExplicit("Invoice_No")
	end if
else 
	varP_MyForm = :Form.viewqueried(xfLT)
	varP_MyForm.Index_SetExplicit("Invoice_No")
end if

This code queries the form then uses the function Index_SetExplicit to enable record fetching while still displaying the called record. This same function can be done with our local query example.

t = table.current()
t.query_detach_all()
xbasic_wait_for_idle()
ui_freeze(.t.)

if xSearchFor = "Invoice_Num" then
xflt = "Invoice_No = '" + xflt + "'"
records_found = topparent.queryrun(Var->xflt,"","","No","",.f.)
else if xSearchFor = "Quote_Num"
xflt = "Quote_Num = '" + xflt + "'"
records_found = topparent.queryrun(Var->xflt,"","","No","",.f.)
else if xSearchFor = "WO_Num"
xflt = "WO_Number = '" + xflt + "'"
records_found = topparent.queryrun(Var->xflt,"","","No","",.f.)
end if

cond1.refresh()

vQt_Num = WO_NUM0.text
xCustID = customerid0.text 
button15.Activate()
MyForm.Index_SetExplicit("Invoice_No")
ui_freeze(.f.)

Here we simply add then index call to the end of the code.

Now lets look at using a variable query to run a report. Create a button on your form to print the selected report and set the report call as below.

	current_filter = current_filter_expn()
	current_order = current_order_expn()
	query.filter = current_filter
	query.order =  current_order	
	prompt_result = ui_get_print_or_preview("Print")
	If prompt_result = "Print" then
		:Report.Print("myreport",query.filter,query.order)
	Else if prompt_result = "Preview" then 
		:Report.Preview("myreport",query.filter,query.order)
	End if

This lets Alpha Software handle the query for you. No additional filters or code needed. That’s all for today. I hope you found this helpful and useful.

As always, if you are a programmer looking to farm out some work, a current business with an Alpha Software application which needs some TLC or someone new to programming in Alpha Software and need help just drop me a line. All work is guaranteed and prices are reasonable. Send a request to :

NLawson@cdc-TakeCharge.com

or call and leave a message at:

713 417-6831

Thank you for your time.