clearing a table filter/query

[gmeredith17]

I have a form based on a single table called item_code.

On the oninit event of the form I have the code below which should do the following;

open table lang_t that the form is not based on.
filter the table based on the WI variable.
create query
get the last field value of the identifyf field and allocate it to variable descriptionv.
set the filter to all records
create query.
close table

dim shared descriptionv as c
dim wi as c

wi="WI001"

tbl=table.open("lang_t")
query.filter=("Identifyf = '"+wi+"'")
qry=tbl.query_create()
tbl.fetch_last()
descriptionv=tbl.form_wordf
[COLOR="Red"]query.filter = ".t." ' show all records
qry=tbl.query_create()[/COLOR]
tbl.close()

I thought the code I highlighted in red should cancel the filter/query on the table. However when I check the table after closing the form the filter remains. Can anyone advise where I am going wrong?

Thanks

Geoff

[G Gabriel]

Re: clearing a table filter/query

Geoff:

dim shared descriptionv as c
dim wi as c

wi="WI001"

tbl=table.open("lang_t")
query.filter=("Identifyf = '"+wi+"'")
qry=tbl.query_create()
tbl.fetch_last()
descriptionv=tbl.form_wordf
[B][COLOR="Red"]tbl.query_detach_all()[/COLOR][/B]
query.filter = ".t." ' show all records
qry=tbl.query_create()
tbl.close()

[Tom Cone Jr]

Re: clearing a table filter/query

Geoff, permit an aside not related to the specific issue you raise: Your script does not set a query.order. Alpha will use whatever "order" was most recently defined for "query.order". Always set options and order when using tbl.query_create(). Don't just use query.filter. Why? because the "last" record is not always the most recently entered record. If query.order holds "Lastname", then "last" is "zebra" even if its record number is 1. Query.filter, query.order, query.options are all global and they persist. They are used by the tbl.create_query method each time. All of them. So, you should set all of them each time you script a query, ok?

[gmeredith17]

Re: clearing a table filter/query

G - Thanks that did the trick.

[gmeredith17]

Re: clearing a table filter/query

Tom,

Thanks for the advice. Although in this case there will only ever be one result so I could have used tbl.fetch_first() or tbl.fetch_last() I can see it would be good practice to specify the query order every time.

While I was searching for an answer to the cancel query/filter problem I came across another thread with a related problem. http://msgboard.alphasoftware.com/al...ad.php?t=67290

In this thread you advise the use of

<tbl>.fetch_find()

when only a single record is expected, which might be more suitable in this instance.

[gmeredith17]

Re: clearing a table filter/query

query.detach_all()

The above function that 'G' recommended seems to remove the query and show all records from the table when used on its own. Is there any underlying reason to also include

query.filter = ".t." ' show all records
qry=tbl.query_create()

from my original attempt at clearing the query?

[G Gabriel]

Re: clearing a table filter/query

The above function that 'G' recommended seems to remove the query and show all records

It does the first part not the second.

query.filter = ".t." ' show all records
qry=tbl.query_create()

do the second part.

[gmeredith17]

Re: clearing a table filter/query

Thats what I thought when I read the help for the query_detach_all(). In practice though it seems to also default to 'show all records'.

I have read in other posts about functions doing things or not doing things specified in the help file and then subsequent updates/versions the behavior changes. That was what was worrying me if I left out the show all records query. I guess it is better to be safe than sorry.

Maybe query_detach_all() defaults to the primary index or I am talking nonsense? Anyway home time for me.

Thanks again.

Geoff