Print Preview Filter Issues

[jbk]

Hello, using the attached database as an example, is there a way if the user presses any one of the "print preview" buttons and the filtered data there requesting is not available, a message box would appear tell them this.

Example: User presses "Print Preview Customer/City" and inputs "ZZ" for company name and "Dallas" for city, as it is now in the example database, a blank print preview window appears. I would like message box to appear saying "Your search for ZZ in Dallas return no records. Try Again??"

Thank you in advance... JON

[Stan Mathews]

The general method is to use a5_get_records_in_query() with the report filter first and branch accordingly.

[jbk]

Thanks Stan. Whats the chance's of you helping with the above code and placing it in my example? Newbie here...

[Stan Mathews]

Changed the customer only button.

Print Filter.zip

[jbk]

Thank you sir... I see the code for single filter, now I'm trying to figure out how to make it work for multiple filters/parameters (customer/city) button.

I would like a msg box if any or all the requested filters are not matching.

Using my example, if user enters "Z" as customer and "Dallas" as city as the print preview parameters, there is no company "Z" but there is a "Dallas" but since the user wanted to filter by both customer and city a msg box would appear. But if user inputs "M" as city and "Dallas" the print preview would show all companies beginning with "M" that are in "Dallas".

Once again thanks Stan...

[Stan Mathews]

Revised as suggested.

Print Filter.zip

[Tom Cone Jr]

jbk,

It sounds to me as though you're trying to recreate the "query by form" functionality that's already built in. Why not use QBF, and then base your report on the currently selected records?

-- tom

ps. There's a big difference between a filter that says look for a customer named "M", and one that says look for all customer's who's lastname begins with "M". You've blurred the distinction in your posts.

[jbk]

Thanks Stan for the update. After I received you first example, I went exploring and figured how to add other parameters. Now I have another issue I can't seem to make the filter by date range work. In the attached example, I have placed (2) buttons each labeled with there respective filter parameters. Top button (customer, city, date begin) works as expected, its the bottom button (customer, city, date begin & end) that's giving me the grief. Top button allows the user to choose (3) options to filter report. If user chooses to filter by just name and inputs "Mike" in name, report brings up "Mike's Accounting", if the user inputs "Dallas" in city(without Mike) report brings up I believe (4) results with customer in "Dallas". If user inputs "Mike" in name AND 05/01/11 in Date Begin, report brings up Mike's Accounting service call on 5/15/11. WORKS AS I WANT IT TO.....

Now the bottom button.... If I input the same criteria as I did on top button, I get error message. If I enter "Mike" in name, 05/01/11 in date begin and 06/01/11 in date end it works properly. If I enter 6/01/11 in date end, if gives me all records prior to 6/1/11. It's forcing me to input data in the "Date End" field and I don't understand why. I want full freedom of how it searches. Whether that be by putting info in 1 or 4 fields. Was working just fine, now this.... Please help... Thank you in advance

[Stan Mathews]

You're having problems because you have one filter defined

filter = " (left(business_name,len([varC->prms.Parameter1])) = [varC->prms.Parameter1] )  .AND. (left(city,len([varC->prms.Parameter2])) = [varC->prms.Parameter2] )  .AND. (date >= [varD->prms.Parameter3]  )  .AND. (date <= [varD->prms.Parameter4]  )"

and all of those parameters must obtain some value but you only want to supply some of them.

What do you think gets into the end date if you don't specify it? I think it is a blank date {}. So the filter excludes all records with date fields > {}..................all of them.

You're going to have to have a filter for each possible combination of parameters supplied and use the one that fits..

[jbk]

If thats the case, why is the top button (name, city, date begin) working? Why isn't the filter excluding the records if the user just place info in 1 of 3 of the the filter selections (Name, City, Date Begin)

[jbk]

Stan, attached is a workaround I done for the above issue with the bottom button (name, city, date begin & end). I still don't understand why this button is giving me issues and why the top button (name, city, date begin) allows the user to input data in just one field (ie entering "Mike" in top button brings up "Mike's Accounting" and user has the choice of then filtering reported data even further. My "workaround" uses the same filtering criteria as previously bottom button, but using Alpha Scripting, I was able to have a default date (12/31/2100). This allows the user to input "Mike" without having to input values for city or beginning date. My questions to you would be, is it possible to have this default date automatically entered (hidden in code) if user doesn't enter a date in "Date End". If it is possible, I would have the Date Begin be 1/1/1900 and the date end be 12/31/2100. This would satisfy everyone in my lifetime...

Not real crazy about the looks of the default date being shown in the dialog box.... JON

I

[Stan Mathews]

You can set defaults like this.

'Prompt for parameter values. Store results in a 'dot' variable called 'prms'
dim prms as p 
prms = ui_get_parameters(dialog_title,get_parameters_string,ok_label,cancel_label)
if prms.last_button_pressed = "OK" then 
	if prms.parameter1 = ""
		prms.parameter1 = "Default value" 'whatever text you want here
	end if
	if prms.parameter2 = ""
		prms.parameter2 = "Default value" 'whatever text you want here
	end if
	if prms.parameter3 = {}
		prms.parameter3 = {01/01/2011} 'whatever date you want here
	end if
	if prms.parameter4 = {}
		prms.parameter4 = {12/31/2011} 'whatever date you want here
	end if
	'Replace parameters in the filter with actual values
	query.filter = replace_parameters(filter,local_variables())
	
	query.order = ""
else
	end
end if

[jbk]

Thank you sir... I like that your able to hide default values such as the dates. Have another issue,that I've been trying to figure out last night. Using the "new" attached example, there's two colored buttons on the bottom left of form. Orange button has the error message inserted on around line 40 of code, the problem I'm having is, when user enters "Mike" in name the print preview report is open but the sort order that I want is wrong. It seems that by pressing orange button w/error code it is sorting by record number. Yellow button contains same error code (40) but I have commented the code out and it is sorting just fine. I have the sort order as: business name, city, date. Don't understand why the error message would have anything to do with sorting order. Also try either colored button and just press "OK" on the dialog screen which in turn displays all records.

[Stan Mathews]

I can't get it to sort descending on the date field either.

I has some trouble getting the debugger to recognize date as a field name but even changing that didn't work.

Well, finally I see. Using the a5_get_records_in_query() resets the query.order variable to "" (blank).

Used this way the sort works. (You can remove the query.order = statement earlier in the script)

if a5_get_records_in_query("tblcustomer",query.filter)> 0 
	query.order = "business_name+city+invert(cdate(date))"
:Report.Preview("Customer",query.filter,query.order)
else
ui_msg_box("Error","No records were found with your search criteria...")
end if

[jbk]

I'll give it a try.... Thanks Stan

[Stan Mathews]

Another alternative is to leave the order and filter assignments where they are and name them something different, of course.

qry.filter = ...........
qry.order = .........
if a5_get_records_in_query("tblcustomer",qry.filter)> 0
:Report.Preview("Customer",qry.filter,qry.order)
else
ui_msg_box("Error","No records were found with your search criteria...")
end if

[jbk]

Thanks Stan... I tried the solution by moving the query.order beneath a5_get_records_in_query and that seems to work great. If you have a chance, look in the thread "button color change based on data". MoGrace posted a solution to this thread in there. Kinda like the way the search dialog looks in the fields "Name" and "City". I posted and attached and example. His code was also having issues but they weren't sorting issues, the were filter issues.

Thanks for all you do, myself and the other members appreciate it... JON

[MoGrace]

His (s/b Her) code was also having issues but they weren't sorting issues, the were filter issues

LOL

My code always has filter issues...which is how I found this thread.