RELATION QUESTION

[powerprinting]

SELE 1
USE File1 ALIAS firstfile
SELE 2
USE File2 ALIA secondfile
INDEX ON MATCHCODE TAG match
SET ORDER TO MATCHCODE
SELE 1
SET RELATION TO MATCHCODE INTO B


REPLACE ALL A.MYFIELD1 WITH B.MYFIELD2 for A.MATCHCODE= B.MATCHCODE


**** Should't this statement update all file1 FIELDS from file2 and not just one record as seems to be happening?

 

[JRB-Bldr]

When you are activating the Index on File2 you are not using the correct TAG name.

USE File1 IN 0 ALIAS firstfile
USE File2 IN 0 ALIAS secondfile

SELECT SecondFile
INDEX ON MATCHCODE TAG match
SET ORDER TO MATCHCODE && <<-- Wrong Reference

Instead, you should:
SET ORDER TO MATCH

Then (assuming that you have a field MatchCode in File1):

SELECT FirstFile
SET RELATION TO MatchCode INTO SecondFile
REPLACE ALL FirstFile.MYFIELD1 WITH SecondFile.MYFIELD2;
FOR FirstFile.MatchCode = SecondFile.MatchCode

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[powerprinting]

INDEX ON MATCHCODE TAG match
SET ORDER TO MATCHCODE && <<-- Wrong Reference

That was my typo.. the order is typed correct in the original program. I still get only one record updated.

Here is the actual code I used...

CLOSE ALL
CLEAR
SET SAFETY OFF
SELE 1
USE kerryking ALIAS kerry
SELE 2
USE automaster ALIA auto
INDEX ON match TAG match
SET ORDER TO MATCH
SELE 1
SET RELATION TO match INTO B

 

[JRB-Bldr]

Have you used SET STEP ON to 'break' the program at the point just before the REPLACE command?

If you have, you should be able to interactively BROWSE both the "Parent" (file1) table and "Child" (file2) table to see if the RELATION is working as expected when you scroll through the Parent records.

It sounds as though the RELATION is not setup and/or working correctly. Or perhaps, the Match fields are not 'matching' (check field size & type, etc.).

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[powerprinting]

Thanks for you help. I don't know what I differently, but its working now.

 

[craigsboyd]

powerprinting,

There must be something else at play here...I mean the basic premise of what you have described (your code has not been very consistent here so it is hard to tell) will work. Run this example in VFP:

SELECT 1
CREATE CURSOR kerryking (match I, test c(30))
INSERT INTO kerryking (match, test) VALUES (1, "HELLO WORLD")
INSERT INTO kerryking (match, test) VALUES (2, "HELLO WORLD")
INSERT INTO kerryking (match, test) VALUES (3, "HELLO WORLD")

SELECT 2
CREATE CURSOR automaster (match I, test c(30))
INSERT INTO automaster (match, test) VALUES (1, "GOODBYE WORLD")
INSERT INTO automaster (match, test) VALUES (2, "GOODBYE WORLD")
INSERT INTO automaster (match, test) VALUES (3, "GOODBYE WORLD")

INDEX ON match TAG match
SET ORDER TO MATCH
SELE 1
SET RELATION TO match INTO B

REPLACE ALL A.test WITH B.test for A.MATCH = B.MATCH

BROWSE && all recs in kerryking now say GOODBYE WORLD

[sub]craig1442@mchsi.com[/sub][sup]

&quot;Whom computers would destroy, they must first drive mad.&quot; - Anon​

[/sup]

 

[gentforreal]

I've been playing with this code for my own purposes and was wondering something... when table a has say 10000 records and table b has maybe 1000 records that contain the value to be replaced, why does the odometer register 10000 record replaced and not 1000?

 

[DSummZZZ]

Fox will scan all 10,000 records of table 'a' to see if there are matches in table 'b' for all of them. It doesn't know beforehand if there are 1,000 or 10,000 matches until it reads the entire file.
So the odometer is based on table 'a', not 'b'.


-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm