filtering and show all on a filtered form

[Lance Gurd]

I have a form which is filtered (at startup) to show jobs that are complete.

ie status = "complete"

Users can then further filter this form to show only jobs begining with a letter of the alphabet etc. This overrides the startup filter, as does the show all button I have on the form.

Is there a way of making the startup filter permanent for this for or do I have to add it into the filtering for each button on the form?

[Tom Cone Jr]

Lance, more details and examples please.

When I try to duplicate this I don't lose the original filter, it gets refined by the second filter expression, reducing the number of records in the subset being displayed on form. Probably doing something differently than you.

Here's what I did:

1) Use Alphasports
2) create new form based on Customer table
3) embed filter to display only records where the first letter of the Lastname field is "B".
4) drop embedded browse on the form, include columns for lastname and bill_state_region

when form opens I see only Lastnames that begin with "B".
If I do a quick filter on "NY" I see only lastnames that begin with "B" and which have "NY" in the bill_state_region field.

This is working as I would expect.

-- tom

[Lance Gurd]

Tom,

My form has the a multi-state button the same as alpha-sports customer form (liked it and the browse so copied and made it work for my form).

If you filter the customer form in alpha sports at start up to say customer bill_city="Arlington" for example. it filters to the city nicely. then push any of the multi state buttons or all and the others appear. Any way around this? so that the city is still the main filter.

Another thing I have just discovered while checking this out. I put the filter in form design->form properties save form, go back into it and it works. press the buttons, it doesn't work, go back to form properties in design mode and the filter has disappeared although it was saved is this a bug? or something to do with the multistate button code

[Scott Emerick]

Lance,

Although your in good hands with Tom, I will offer my 2 cents.

I have done something similar to what you are trying to do which is retain one filter while adding another.

In my situation I had a radio button that would filter records by status and then allow me to use the multistate button to further refine my filter by letter.

Here is the code I used in the multistate button on push:

'Check radio button selection.
IF var->status = "Complete" .or. var->status = "Opened" THEN 'Radio button not "Both".
	topparent.restrict_navigation = .T.
	topparent:tables:results.filter_expression="left(lastname,1)='" + MBltr.text + "'.and. status = var->status"
	topparent:tables:results.order_expression="lastname"
	topparent:tables:results.query()
	
ELSE
	'Radio button is "Both".
	topparent.restrict_navigation = .T.
	topparent:tables:results.filter_expression="left(lastname,1)='" + MBltr.text + "'"
	topparent:tables:results.order_expression="lastname"
	topparent:tables:results.query()
END IF

parentform:browse1.Refresh_Browse()
topparent.restrict_navigation = .F.
var->ltr = "1"
MBLTR.refresh()

Hope this helps,

Scott

[Tom Cone Jr]

Lance,

Tell us how you do this:

If you filter the customer form in alpha sports at start up to say customer bill_city="Arlington" for example. it filters to the city nicely.

Unless your form is an exact match for the Customer Information form in Alpha Sports it's better if you attach a working copy of your database here so we can replicate the problem and suggest specific soluitons.

-- tom

[Tom Cone Jr]

One approach

Lance,

Suggest you make a copy of the Customer Information form to play with and then change the OnChange event script for the multi-state button object to this:

'Date Created: 19-Jul-2002 12:21:13 AM
'Last Updated: 18-Oct-2005 07:11:31 PM
'Created By  :
'Updated By  : TEC
'script for the multistate button on the Customer form in the invoice
'app

dim tbl as p 

tbl = table.current()
[COLOR="Blue"]ind = tbl.index_primary_get()    'get pointer to current index/query
vc_curr_filter = ind.filter_get()    'use pointer to get current filter expression
trace.writeln(vc_curr_filter)
[/COLOR]
[COLOR="Blue"]choice = ui_get_radio("Query Scope",1,"Start from scratch","Additive")
if choice = "Start from scratch" then
	' Filter the table, discarding existing query
	topparent:tables:customer.filter_expression = "left("+searchby.text+", 1) = '" + mulstbtn1.text + "'"
	topparent:tables:customer.order_expression = searchby.text
	topparent:tables:customer.query()
else
	' Preserve existing query, but filter further
	topparent:tables:customer.filter_expression = vc_curr_filter +".and. left("+searchby.text+", 1) = '" + mulstbtn1.text + "'"
	topparent:tables:customer.order_expression = searchby.text
	topparent:tables:customer.query()
end if			
[/COLOR]
' Get the current record count
i = tbl.index_primary_get()
count = i.records_get()

' If we found no records, then we put up a warning and show all records
if count = 0
	ui_msg_box("Warning", "No records found. Showing all records")
	tbl.order(searchby.text,searchby.text + " <> \"\"")
	browse1.refresh()
	mulstbtn1.text = "1"  '' I chose '1' because it is not in the set A-Z that we display 
end if

This script gives the user to narrow the result set by preserving the current filter, and then making the new criteria additive.

Hope this gives you some ideas.

-- tom

ps. my changes are in blue (I hope)

[Lance Gurd]

Tom, when the filter disappeared I was in Alpha sports playing around with the customer form, it does not disappear in my form. My database is getting very large. the set in question has 12 tables all linked therefore may be a bit too big to zip and put here. if your next suggestion (using current filter) does not solve my problem I will try and par it down and send a copy. In meetings today so may not have time to look but thanks for the effort so far.

Lance

[Tom Cone Jr]

Lance, a 12 table set used for data entry in a multi-user setting is asking for trouble, in my opinion. Suggest you review this article:

http://www.learn alpha.com/SimplifyY...ifyYourApp.htm

-- tom

[Lance Gurd]

Tom,

Having read the article, it didn't sound a bad idea. especially as my set makes things pretty slow over the network.

Spent all weekend re-writing all the code and re-designing all the forms, most of it works!! But got up this morning and suddenly thought about reports that are based on the set. Looks like I will have to keep the set in background for this purpose.

I have learned a lot by playing around with the code this weekend thanks for forcing me to do it the hard way.


Lance

[Tom Cone Jr]

Lance, keeping the set definition for use in a report layout is fine. In fact, it's often recommended. It's the use of complex sets to do data entry in a multi-user environment that's often problematic. -- tom

[Lance Gurd]

Tom,

Wasn't too bad as I do most of the entering at present, but we do have some people who are not sure about the on/off switch so could be potential problem, once again thank you