Replace in Relationship

[psvialli]

I hope you do not mind these newbie questions!

I have set a relationship up between two tables,- one to many

But I am having trouble with the replace - it only replaces the first records in the tempitems table, but there is lots of records. How can I get it to replace all the related records in tempitems and not just the first records.

I am not sure how i change the scan to cope with this ?

Many thanks in advance


SET RELATION TO ID INTO tempitems
lnnewpk=loMain.createnewID()
lnitemID=loItems.createnewID()
SELECT tempmain
SCAN
REPLACE tempmain.pk WITH lnnewpk
REPLACE tempitems.main_fk WITH lnnewpk
REPLACE tempitems.pk WITH lnitemID
lnnewpk=loDev.createnewID()
lnitemID=loDev1.createnewID()
ENDSCAN

 

[Olaf Doschke]

You can use SET SKIP TO:

Create Cursor curOne (iOneID I)
Create Cursor curMany (iManyID I, iOneID I, cText C(1))
Select curMany
Index On iOneID Tag xOne additive
Insert into curOne VALUES (1)
Insert into curMany Values (1,1,"A")
Insert into curMany Values (2,1,"B")
Insert into curMany Values (3,1,"C")
Insert into curOne VALUES (2)
Insert into curMany Values (4,2,"A")
Insert into curMany Values (5,2,"B")

Select curOne
Set Relation To iOneID into curMany
Set Skip To curMany

Select curOne
Scan for curOne.iOneID=2
   ? curMany.iManyID, curMany.iOneID, curMany.cText 
EndScan

Although the scan loop is for curOne, this steps through the records of curMany for one of the records of curOne.
Its as if you are really scanning through the table you would get by a left join of curOne and curMany, which should help you updating primary/foreign IDs.

Bye, Olaf.

 

[Eguy]

psvialli;

Change your code to read as follows:

SET RELATION TO ID INTO tempitems
lnnewpk=loMain.createnewID()
lnitemID=loItems.createnewID()
SELECT tempmain
   SCAN
    REPLACE tempmain.pk WITH lnnewpk
        REPLACE tempitems.main_fk WITH lnnewpk , ;
        tempitems.pk WITH lnitemID ;
        FOR !EOF("tempitems")
    lnnewpk=loDev.createnewID()
    lnitemID=loDev1.createnewID()
    ENDSCAN

Ed

Please let me know if the suggestion(s) I provide are helpful to you.
Sometimes you're the windshield... Sometimes you're the bug.

 

[Eguy]

psvialli;

Sorry !
I just re-read your code and my answer will not help!

all will end up with the same lnitemID.

Need more coffee

Please let me know if the suggestion(s) I provide are helpful to you.
Sometimes you're the windshield... Sometimes you're the bug.

 

[psvialli]

Hmmmm I am a little lost on this OlafDoschke I do not really understand what is happening where would I do the replace ?

I did not think it would be this complicated to just update old the child records!

Paul

 

[Eguy]

psvialli;

After further review...

This should work for you.

SET RELATION TO ID INTO tempitems
lnnewpk=loMain.createnewID()
lnitemID=loItems.createnewID()
SELECT tempmain
SCAN
	REPLACE tempmain.pk WITH lnnewpk
	REPLACE ;
		tempitems.main_fk WITH lnnewpk , ;
		tempitems.pk WITH loDev1.createnewID() ;
		FOR !EOF("tempitems")
	lnnewpk=loDev.createnewID()
ENDSCAN

Ed

Please let me know if the suggestion(s) I provide are helpful to you.
Sometimes you're the windshield... Sometimes you're the bug.

 

[psvialli]

I think you were right the first time it now changes ALL to the same lnitemID.


Any other ideas ?