Announcement

**Stan Mathews** · 08-02-2007, 05:40 PM

Re: Simple filter problem

Code:

dim tbl as P
tbl = table.open("archive")
query.options = "I"
query.description = "None"
query.order = ""
[COLOR="Red"]query.filter = "archive->cust_id = Trans->cust_id .and. archive->year=Trans->year"[/COLOR]
'[COLOR="Blue"]query.filter = "archive->cust_id = "+s_quote(Trans->cust_id)+" .and. archive->year="+s_quote(Trans->year)[/COLOR]
'query.filter = ("archive->cust_id = Trans->cust_id")
'query.filter = ("archive->year=Trans->year")
qry = tbl.query_create()
tbl.change_begin()
tbl.total_orders = tbl.total_orders - Trans->orders
tbl.change_end()
qry.drop()
tbl.close()

The red filter is the proper concatenation of what you indicated. the blue filter is what I think you need.

FYI, in the section

query.filter = ("archive->cust_id = Trans->cust_id")
query.filter = ("archive->year=Trans->year")

The second line merely overwrites what happened in the first line.

**Walter** · 08-02-2007, 05:47 PM

Re: Simple filter problem

Hello David,

Sorry, Stan got in while I was typing this code.
This is another approach with the same result.

You might want to reconsider your filters.
In both cases it will return multiple records if there are more than one "cust_id" in the archive tbl. Same goes for the year filter.

If I understand correctly, you want to update a SINGLE record in the "Archive" tbl?

Your filter would be something like the transaction number (unique number) that also exist in the "archive" tbl.

Code:

Dim SHARED recT as P  ' result table
Dim SHARED recCount as N 'result table find result
Dim vTransID as C 'You have to assign a value to this variable to search your archive tbl
	
	recT = table.open("archive")
	
	recT.index_primary_put("Trans_Id") 'you must have an existing index to ref here based on the field you want to search on.
	recCount = recT.fetch_find(vTrnsID)'your unique search value. vTransID is a variable you have to populate before running this code
	
	If recCount > 0 then 'record found - can update it
	    recT.change_begin()
            'update your fields
            recT.change_end(.t.)
        else
           'no record found' cannot delete the record / add the record to archive
        end if
recT.close() 'close the tbl.

Just a comment on your code.
You created a filter, but did not instruct the code to run the filter and go to that record once found to update. That is why the first record was updated all the time.

Hope this help
Walter

**DoubleShufty** · 08-03-2007, 01:54 PM

Re: Simple filter problem

Thanks Stan and Walter for your advice.

I found Stan's solution to easier to understand and it did solve the problem.

However, it appears that after using a filter, other things get upset.

I have a form showing two browses - Browse1 for customers - Browse2 for transactions. I select a customer, then select a transaction for deletion.

If I update my archive record using the filters, when I come to delete the transation using action scripting, I find that the first record is deleted and not the one selected.

After much head scratching, I came to the conclusion that the filter operation upset the selection when it came to the deletion.

As a last resort, I set up session variables and stored the data from the record to be deleted. Then I deleted it.

Following this I updated the archive record using the session variables.

Now it works fine but am I going mad?

**Stan Mathews** · 08-03-2007, 01:59 PM

Re: Simple filter problem

Not sure I understand your work flow here but I will point out that I used

Code:

[COLOR="Red"]qry[/COLOR]  = tbl.query_create()
..................
..............................
...........................
[COLOR="Red"]qry.drop()[/COLOR]

To remove the query when the change was done. This is out of habit. If you want the query to remain in effect after the code has completed, just remove the qry.drop() from the script.

Announcement

The Alpha Software Forum Participation Guidelines

Simple filter problem

Simple filter problem

Comment

Comment

Comment

Comment