---snipped --
>
>
CREATE CURSOR apprequest (requestno c(10), xdate d)
>
> Now my problem is that append from seems to fail. Below are the commands in the click event of the grid for department/branch:
>
>
* Populate cursor
> Select apprequest
> ZAP
> Append From mrequest.dbf FIELDS requestno, xdate FOR(mrequest.location = brndept.location)
> * .And. mrequest.rdate # {} .And. mrequest.podate = {})
> Locate && Go Top
> Thisform.grid3.Refresh
>
>
> Notice that I turned the .And. plus related entries as remarks because I am trying to find the problem. The Approved requisitions grid is supposed to show only one record; but instead it shows all records I am playing with. Accounts department, at the moment, has only one entry in mrequest.dbf which is the source of the Append From.
>
> I tried filtering it straight with:
>
Append From mrequest.dbf FIELDS requestno, xdate FOR(mrequest.location = 1)
> just to see how it will react, and it is still the same. Accounts Department's location is 1 (integer, autoinc). Mrequest.location is likewise Integer (non-autoinc).
>
> Anybody have an idea what is going wrong? I stopped working until I knew what is happening because of the temptation to just use direct linking plus filtering. I can easily solve the problem using another way but I don't want to leave the abovementioned without knowing why it fails..
>
> Thanks
Hi Jun
You've been battling with one of the most tricky and deceptive commands in Xbase, namely APPEND FROM
.
Look at the result of this simple example and see how the innocent are fooled every time:
CLOSE TABLES ALL
CREATE TABLE source (name char(10))
INDEX on name TAG name
INSERT INTO Source VALUES ('Tushar')
INSERT INTO Source VALUES ('Christian')
INSERT INTO Source VALUES ('Akram')
SET FILTER TO name<'T'
SET EXACT OFF
BROWSE LAST NOWAIT
CREATE CURSOR Target(name Char(10))
SELECT Target
APPEND FROM Source
BROWSE LAST NOWAIT
GO BOTTOM In Source
SELECT Target
APPEND FROM Source FOR Source.name ='A'
The cursor result looks like this:
Tushar
Christian
Akram
Why aren't the names in alphbetical order. The table Source has an active index on name!!!
Why wasn't the filter setting respected?
Why did the second APPEND FROM not add any records at all. There's is a name ='A', namely 'Akram'
Here's the explanation:
APPEND FROM does NOT pay any attention whatsoever to the currently opened source table by the same name, or its indexes or its filters.
The first APPEND FROM reads directly from the original file, straight from the hard disk, whether this file is already open in some workarea or not. That's why ALL the names where appended, and in the original order, ignoring any filter or active index on the opened instance of the same table.
Now then, why did the second APPEND FROM fail?
Well that was because the FOR clause looks in the opened Source alias, at the current record in 'Source', in workarea 1, and the current record is 'Christian', which is NOT ='A', so False is returned and no append takes place.
Please note the the record pointer in Source in workarea 1, never moves, so the FOR clause returns False for every record the APPEND FROM reads.
The APPEND FROM command and the FOR clause look at different instances of the same file: APPEND FROM reads the original on disk, the FOR clause reads the current record from the open Source alias.
My considered advice: do NOT EVER use APPEND FROM. Instead use what Tushar told you, without making a big pont of it:
INSERT INTO Target (column list) SELECT <column list> FROM Source WHERE <condition>.
This will do exectly what you expect, and it will do it like the Danes say about their famous beer. 'Hvaer gang', i.e. 'Every time'.
-Anders