some advice again

again thank you for all you help on previous items. again, i have hit the
WALL!!
in your eyes i am not doing anything tough, i just cant seem to think past it.

please advise.

ok, I open a table, get data from it and place it into a new one.
Now I will open the New table, add to fields to itâ?¦(2 fields manually) no
big deal..
Open a third tableâ?¦scan it for the 2 fields, I need and place them in the
proper place. My code is below.

It keeps telling me I have no table open but I do (I thought)


clear all
set default to f:
USE f:\foxpro25\lagers\dbases\lagchk.dbf IN 0 shared
copy to f:\foxpro25\lagers\dbases\forauditors.dbf for checkdte >=
ctod('06/01/04') and checkdte <= ctod('06/01/05')
use f:\foxpro25\lagers\dbases\forauditors.dbf shared
use f:\foxpro25\lagers\dbases\fulagmas.dbf in 0 shared
select 0
set order to PADL(agency,6,"0")+PADL(dpt,3,"0")+PADL(employee,6,"0")
go top
scan
if !eof()
if fulagmas.agency = forauditors.agency and
fulagmas.dpt = forauditors and fulagmas.employee = forauditors.employee

replace forauditors.brthdt with
fulagmas.brthdt, forauditors.benprgflg with fulagmas.benprgflg

endif

endif

endscan

Paul
Tue Jun 28 10:37:28 CDT 2005


"Tom" <Tom@discussions.microsoft.com> wrote in message
news:7BD90ECC-8C75-48AD-BBA5-5B1E87C32455@microsoft.com...
> again thank you for all you help on previous items. again, i have hit the
> WALL!!
> in your eyes i am not doing anything tough, i just cant seem to think past
> it.
>
> please advise.
>
> ok, I open a table, get data from it and place it into a new one.
> Now I will open the New table, add to fields to it.(2 fields manually) no
> big deal..
> Open a third table.scan it for the 2 fields, I need and place them in the
> proper place. My code is below.
>
> It keeps telling me I have no table open but I do (I thought)
>
>
> clear all
> set default to f:
> USE f:\foxpro25\lagers\dbases\lagchk.dbf IN 0 shared
> copy to f:\foxpro25\lagers\dbases\forauditors.dbf for checkdte >=
> ctod('06/01/04') and checkdte <= ctod('06/01/05')
> use f:\foxpro25\lagers\dbases\forauditors.dbf shared
> use f:\foxpro25\lagers\dbases\fulagmas.dbf in 0 shared
> select 0
> set order to PADL(agency,6,"0")+PADL(dpt,3,"0")+PADL(employee,6,"0")

That's no good. You can't SET ORDER TO because there's no table selected
(you just selected 0, which means an empty work area). If you want to select
an alias, say SELECT <alias>. For instance, if you want to select fulagmas,
say SELECT fulagmas. Alternatively, you can use

SELECT 0
USE fulagmas

... instead of

USE fulagmas IN 0
SELECT fulagmas


BTW, don't use CTOD("06/01/04"). Use {^2004/6/1} instead, to prevent
ambiguity. Faster, too.



> go top
> scan
> if !eof()
> if fulagmas.agency = forauditors.agency and
> fulagmas.dpt = forauditors and fulagmas.employee = forauditors.employee
>
> replace forauditors.brthdt with
> fulagmas.brthdt, forauditors.benprgflg with fulagmas.benprgflg
>
> endif
>
> endif
>
> endscan
>
>
>
>
>
>
>

Tom
Tue Jun 28 10:59:02 CDT 2005

Paul, when i do this...

use f:\foxpro25\lagers\dbases\fulagmas.dbf in 0 shared

set order to PADL(agency,6,"0")+PADL(dpt,3,"0")+PADL(employee,6,"0")

i get a message index tag not found. if i set order to 'key' which is the
index tag i want to use.
i get alias forauditors not found, that is the table i want the 2 fields i
am looking for to go into.
im not trying to sound like a complete idiot.

Bernhard
Tue Jun 28 11:36:08 CDT 2005

Hi Tom

> use f:\foxpro25\lagers\dbases\fulagmas.dbf in 0 shared
>
> set order to PADL(agency,6,"0")+PADL(dpt,3,"0")+PADL(employee,6,"0")
>
> i get a message index tag not found.
This is correct.

Why?
First you
USE sometable IN 0
This means: Foxpro opens SOMETABLE in a different workarea, namely in the empty
workarea with the lowest number, but not in the actual workarea.
Then you
SET ORDER TO someorder
This means: Foxpro should set SOMEORDER in the actual workarea. If all your
previous USE commands are with a IN 0, then in the actual workarea surely no
table is open, and Foxpro surely can not find a tag in this workarea.

With
SET ORDER TO someorder
you always switch to a previously defined index tag. So your command
> set order to PADL(agency,6,"0")+PADL(dpt,3,"0")+PADL(employee,6,"0")
tries to switch order to a tag named
'PADL(agency,6,"0")+PADL(dpt,3,"0")+PADL(employee,6,"0")'
This is not a valid tag name.
If you want to create an index with this expression then you first must create
this index by
INDEX ON PADL(agency,6,"0")+PADL(dpt,3,"0")+PADL(employee,6,"0") TAG sometag
After that, you could switch to this order by
SET ORDER TO sometag

And be aware of the actual workarea. Every command without the
IN <workarea|alias> clause allways operates in the actual workarea.

Regards
Bernhard Sander

Tom
Tue Jun 28 11:56:13 CDT 2005

Bernard, thank you for your description. it makes more sense to me now. my
code looks like this now. only thing is its looking for an alias forauditors
now.. i guess i dont understand if the table is open to me, then why do i
need to create an alias.?

whenit hits the part if x.x = forauditors.x it throws alias forauditors not
found.

clear all

set default to f:

USE f:\foxpro25\lagers\dbases\lagchk.dbf IN 0 shared

copy to f:\foxpro25\lagers\dbases\forauditors.dbf for checkdte >=
{^2004/7/1} and checkdte <= {^2005/6/1}

use f:\foxpro25\lagers\dbases\fulagmas.dbf in 0 shared
set order to key
go top
scan
if !eof()
if fulagmas.agency = forauditors.agency and fulagmas.dpt =
forauditors.dpt and fulagmas.employee = forauditors.employee
replace forauditors.brthdt with fulagmas.brthdt, forauditors.benprgflg with
fulagmas.benprgflg
endif
endif
endscan

Paul
Tue Jun 28 14:10:27 CDT 2005

You are missing the big point here, which is that if you say

USE mytable IN 0

mytable will NOT be the currently selected alias. Therefore you cannot issue
commands that work on the current alias and expect it to work on mytable.
Try it this way instead:

SELECT 0
USE mytable

To help you understand what's going on, have the datasession window open
while you issue those commands in the command window.





"Tom" <Tom@discussions.microsoft.com> wrote in message
news:73DB3B99-2ED7-43A4-978A-BB86FC6E68D1@microsoft.com...
> Bernard, thank you for your description. it makes more sense to me now. my
> code looks like this now. only thing is its looking for an alias
> forauditors
> now.. i guess i dont understand if the table is open to me, then why do i
> need to create an alias.?
>
> whenit hits the part if x.x = forauditors.x it throws alias forauditors
> not
> found.
>
> clear all
>
> set default to f:
>
> USE f:\foxpro25\lagers\dbases\lagchk.dbf IN 0 shared
>
> copy to f:\foxpro25\lagers\dbases\forauditors.dbf for checkdte >=
> {^2004/7/1} and checkdte <= {^2005/6/1}
>
> use f:\foxpro25\lagers\dbases\fulagmas.dbf in 0 shared
> set order to key
> go top
> scan
> if !eof()
> if fulagmas.agency = forauditors.agency and fulagmas.dpt =
> forauditors.dpt and fulagmas.employee = forauditors.employee
> replace forauditors.brthdt with fulagmas.brthdt, forauditors.benprgflg
> with
> fulagmas.benprgflg
> endif
> endif
> endscan
>

Andrew
Wed Jun 29 03:00:49 CDT 2005

"Tom" <Tom@discussions.microsoft.com> wrote in message
news:7BD90ECC-8C75-48AD-BBA5-5B1E87C32455@microsoft.com...
> again thank you for all you help on previous items. again, i have hit the
> WALL!!
> in your eyes i am not doing anything tough, i just cant seem to think past
> it.
>
> please advise.

I think you want something like this.
Try it on a copy ;-)

************************
* this saves you placing '...SHARED' when USEing tables
SET EXCLUSIVE OFF

CLEAR ALL
USE f:\foxpro25\lagers\dbases\lagchk.dbf IN 0

* you can specify dates with {../../....} instead of using ctod()
COPY TO f:\foxpro25\lagers\dbases\forauditors.dbf ;
FOR BETWEEN(checkdte, {06/01/04}, {06/01/05})

USE f:\foxpro25\lagers\dbases\forauditors.dbf IN 0
USE f:\foxpro25\lagers\dbases\fulagmas.dbf IN 0

* I think, from your posts, this is the right tag
SET ORDER TO key IN fulagmas
SELECT forauditors

* this next command is crucial [and I hope correct]
* it relates the forauditors table to the fulagmas
* table, ie when you move through forauditors,
* fulagmas moves to the record with the same agency, dpt and employee.
SET RELATION TO PADL(agency,6,"0")+;
PADL(dpt,3,"0")+;
PADL(employee,6,"0") ;
INTO fulagmas

SCAN FOR !EOF('fulagmas')
REPLACE brthdt WITH fulagmas.brthdt, ;
benprgflg WITH fulagmas.benprgflg
ENDSCAN
SET RELATION OFF INTO fulagmas

************************

It is also possible that something like this would work.

************************
USE f:\foxpro25\lagers\dbases\lagchk IN 0
USE f:\foxpro25\lagers\dbases\fulagmas IN 0

SELECT lagchk.*, brthdt, benprgflg ;
FROM lagchk l, fulagmas f ;
WHERE l.agency=f.agency ;
AND l.dpt=f.dpt ;
AND l.employee=f.employee ;
AND BETWEEN(checkdte, {06/01/04}, {06/01/05}) ;
INTO CURSOR temp
COPY TO f:\foxpro25\lagers\dbases\forauditors

************************

--
HTH
Andrew Howell

Bernhard
Wed Jun 29 03:56:40 CDT 2005

Hi Tom,

> Bernard, thank you for your description. it makes more sense to me now. my
> code looks like this now. only thing is its looking for an alias forauditors
> now.. i guess i dont understand if the table is open to me, then why do i
> need to create an alias.?
>
> whenit hits the part if x.x = forauditors.x it throws alias forauditors not
> found.
Let's go again through your code step by step.

> clear all
Here, all tables in all workareas will be closed.
Workarea 1 becomes the active workarea.

> set default to f:
>
> USE f:\foxpro25\lagers\dbases\lagchk.dbf IN 0 shared
Here, table lagchk will be opened in the empty workarea with the lowest number,
i.e. workarea 1. This is accidentially the active workarea.

> copy to f:\foxpro25\lagers\dbases\forauditors.dbf for checkdte >=
> {^2004/7/1} and checkdte <= {^2005/6/1}
Now you copy some records from active workarea to table forauditors.
After the copy there is a new table on your disk, but this table is not USEd
automatically.
Maybe her you just place this command:
USE forauditors
This would close lagchk and open forauditors in the active workarea.

> use f:\foxpro25\lagers\dbases\fulagmas.dbf in 0 shared
Here, table fulagmas will be opened in the empty workarea with the lowest
number, i.e. workarea 2. This is not the active workarea.

> set order to key
Here you try to set order for table lagchk. Maybe this is not what you want.
All subsequent command will operate on table lagchk.
Don't name your index tag "key" since this is a built-in Foxpro function.
> go top
> scan
> if !eof()
This will never happen since SCAN loop ends automatically when the end of the
table is reached.
> if fulagmas.agency = forauditors.agency and fulagmas.dpt =
> forauditors.dpt and fulagmas.employee = forauditors.employee
> replace forauditors.brthdt with fulagmas.brthdt, forauditors.benprgflg with
> fulagmas.benprgflg
Here you try to compare fields from table fulagmas with fields from table
forauditors. But forauditors is not yet used in any of your workareas. So foxpro
cannot find this alias.
> endif
> endif
> endscan
>

hth
Bernhard Sander

Andrew
Wed Jun 29 04:22:52 CDT 2005

"Andrew Howell" <ajh@work> wrote in message
news:u9ZmoDIfFHA.3560@TK2MSFTNGP09.phx.gbl...
> ...
> SCAN FOR !EOF('fulagmas')
> REPLACE brthdt WITH fulagmas.brthdt, ;
> benprgflg WITH fulagmas.benprgflg
> ENDSCAN
> SET RELATION OFF INTO fulagmas

Actually that scan loop can be done in one command,

REPLACE ALL brthdt WITH fulagmas.brthdt, ;
benprgflg WITH fulagmas.benprgflg ;
FOR !EOF('fulagmas')
SET RELATION OFF INTO fulagmas

The thing with REPLACE ALL is that it needs to lock the whole file whilst
replacing, the scan loop doesn't. However this is only an issue when you
have a multi user environment and are wanting to replace a subset of records
in the whole table whilst other people are working on other records.

--
Regards
Andrew Howell