Diese Seite mit anderen teilen ...

Informationen zum Thema:
Forum:
WinDev Forum
Beiträge im Thema:
12
Erster Beitrag:
vor 8 Jahren, 6 Monaten
Letzter Beitrag:
vor 8 Jahren, 6 Monaten
Beteiligte Autoren:
ICI, Al, Dan M, Dan, Marius, Arie, Piet van Zanten, Dan M.pcs.crosspost

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

Startbeitrag von Dan M.pcs.crosspost am 26.05.2009 06:04

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

Antworten:

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(B) THEN
HReset(A)
A.Field1 = B.Field1
A.Field2 = B.Field2
A.Field3 = B.Field3
HAdd(A)
END
END

von ICI - am 26.05.2009 07:01
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(ParentB)
ChildA.Field = ParentB.field
HModify(ChildA,Hrecnum(ChildA))
End
HreadNext(ChildA,AnyKey)
End //while not hout(ChildA)

Regards
Al

von Al - am 26.05.2009 14:34
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. :)

von ICI - am 26.05.2009 15:27
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


von Al - am 26.05.2009 15:40
I waiting becouse I never do this things like Dan want :)
I just hope he know what he doing ;)

von ICI - am 26.05.2009 20:43
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 B) 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 B), find the related Customer (File A), then move the NEW ID in the Customer (FILE A) to the Contact (FILE B), and save it for every Contact (FILE B) record.



von Dan M - am 27.05.2009 06:34
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

von Piet van Zanten - am 27.05.2009 06:59
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

von Arie - am 27.05.2009 07:13
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

von Marius - am 27.05.2009 08:11
I thought about doing something like that but had not attempted it. It worked out perfectly.

Thanks ... Dan

von Dan - am 30.05.2009 02:50
Piet van Zanten solution worked perfectly ...

simple and saved a bunch of time

Dan

von Dan M - am 30.05.2009 13:12
Zur Information:
MySnip.de hat keinen Einfluss auf die Inhalte der Beiträge. Bitte kontaktieren Sie den Administrator des Forums bei Problemen oder Löschforderungen über die Kontaktseite.
Falls die Kontaktaufnahme mit dem Administrator des Forums fehlschlägt, kontaktieren Sie uns bitte über die in unserem Impressum angegebenen Daten.