How do I loop thru a file and update it as I go?

I have file A which is a child of a file B. I need to loop through file A then finding the related record in file B and save data from file B to file A.

I know I need to loop through the file but I am not sure how to go about this.

How to use a window? a query? ... can anyone explain this to me

thanks ... Dan

Message forwarded from pcsoft.us.windev

First !
Why you need to insert related records into child table. It is strange situation.
Like you describe you have only 1:1 relation 1 record in file A and 1 record in file B.
If it so then I think you need to do this. You need to do on this way. So, if you use
WHILE NOT Hout(A) then when you Hadd() new records then they will be in LOOP and will be duplicated, so you need to do that by FOR..
Try this. I am not, I am just write this right here. I hope it work.

nNumberOfRecords is int = HNbRec(A)
FOR i = 1 TO nNumberOfRecords
HRead(A,i)
myRelatedKey is string = A.KeyField
HReadSeek(B,KeyField,myRelatedKey,hIdentical)
IF HFound( THEN
HReset(A)
A.Field1 = B.Field1
A.Field2 = B.Field2
A.Field3 = B.Field3
HAdd(A)
END
END



Edited 1 time(s). Last edit at 05/26/2009 09:02AM by ICI.

Hello Dan

The situation you describe will create redundant data in the child file. There should not be any need to put data from a parent into a related child file as the data in the parent can always be retrieved from the parent record for a given child record.
One small point, in describing any situation with files, it is more usual and logical to refer to the Parent file as A and the Child file as B.


HreadFirst(ChildA,AnyKey)
While not Hout(ChildA)
HreadSeekFirst(ParentB,ParentBPrimaryKey,ChildA.ParentBPrimaryKeyAsForeignKey)
IF Hfound(Parent
ChildA.Field = ParentB.field
HModify(ChildA,Hrecnum(ChildA))
End
HreadNext(ChildA,AnyKey)
End //while not hout(ChildA)

Regards
Al

But Al,
If I understand he want to Hadd() record from child into parent.
So my function take care about redudant data and scope only record before Hadd()
He wants for sure to add records and dont use child table anymore.
In other case data into tables will be, hm, dont think about that situation.

Hello ICI

Interesting.. I interpreted the phrase "save data from file B to file A." to mean Dan wanted to transfer data from B to A, but to add records into File A, then your solution is the correct one

I guess we will have to wait and see his response

Regards
Al

I waiting becouse I never do this things like Dan want
I just hope he know what he doing

Hi All,

Thanks for your input and feedback. I think I need to explain in more detail.

I am bring data from an old system, that we need for history. In the old system, the records are linked by system generated ID#s.

File A (Parent, example Company)
Field1: File A ID# (unique ID) example Customer ID = 100

File B (Child, example Contact)
Field1: File B ID# (unique ID) example Contact ID = 14
Field2: File A ID# (not unique in this file) Customer ID = 100

So, File be is related B is related to File A. You can have multiple contacts (FILE for 1 company (FILE A).

Now the issue, the old system has these relationships in place and all works well, in the old system. When I moved the data to MySQL new IDs are created by auto increment for both files. So I also added OLD_COMPANY_ID & OLD CONTACT_ID from the old data files, to preserve the relationship and link between the files. Not sure if there is a simpler way to have done this but ... now I need to go through each Contact record, find the Customer based on the OLD_COMPANY_ID and then update Contact with new Company ID. Hope this make sense ... more sense

If there is a better or easier way to migrate this data ... I am up for all suggestions.

If not ... I still need to understand how to loop through the Contact file (File , find the related Customer (File A), then move the NEW ID in the Customer (FILE A) to the Contact (FILE , and save it for every Contact (FILE record.

Hi Dan,

A very simple solution:
Start the new file definition without autoincrement ID's
After import alter the table to use autoincrement ID's

Regards,
Piet

If it is a one time job, I think you could do it with just one query, via mysql query browser.
Something like:

UPDATE b left join a ON b.old_customer_id = a.old_customerID SET b.new_customer_ID = a.new_id

or

UPDATE b SET new_customer_ID = (SELECT new_id FROM a WHERE a.old_customer_id = b.old_customerID )

Arie

Hi Dan

If you want to retain the ID of the previous data set, you might want to look at the HAdd options

HAdd(sFileName, hForceIdAuto)

Regards
Marius

I thought about doing something like that but had not attempted it. It worked out perfectly.

Thanks ... Dan

Piet van Zanten solution worked perfectly ...

simple and saved a bunch of time

Dan