Creating new records from old records

[Pat Bremkamp]

I used to know how to do this, but I've forgotten the trick!

I need to use some records in a table to create additional new records in the same table. So, I filtered the table to get the records I need, then created two pointers to that table, one to read through the old records and one for the new records. Then, I set up a while ... end while loop to read the old records. Works fine, except that the while .not. tbl.fetch_eof() test appears to get to the end of the old records and then continue on with the new records. As a result, I get duplicate new records.

Odd thing is, I don't get an infinite number of new records...if I read one record, I get two duplicates. If I read 7 records, I get three duplicates...very strange. Any suggestions?

Pat

[Mike Wilson]

Why duplicate

Pat,

Send you script so we can see it.

On a different note, why don't you use a copy operation, copy the old records to a Temp table, and then append them back to the Master table?

Mike W

[Pat Bremkamp]

Here 'tis

Mike, thanks for responding.

The picture attached shows the form. It is a dialog form. My app includes lists of people, companies, places, and things, and this form allows the user to duplicate an existing list either to add to an existing list or to a new list. The code is:

 
'==== get the copy from list Id and name
vFromListID=left(vCopyFrom,8)
'==== get copy to list ID
if left(vCopyTo,8)="99999999"
 '==== if new list
 tbl1=table.open("[PathAlias.ADB_Path]\lists",FILE_RW_SHARED)
 tbl1.enter_begin(.t.)
 tbl1.Li_name=alltrim(vNewListName)
 tbl1.Li_entid=session.__protected__En_Id
 tbl1.Li_entname=session.En_Name
 tbl1.Li_date=date()
 tbl1.enter_end(.t.)
 vToListId=tbl1.Li_id
 vToListName=alltrim(vNewListName)
 tbl1.close()
else
 '==== if existing list
 vToListId=left(vCopyTo,8)
 vCopyFrom=alltrim(vCopyFrom)
 vToListName=right(vCopyFrom,len(vCopyFrom)-8)
end if
'==== copy user field labels <<== this part works fine
if convert_type(vCopyUDF,"L")=.t.
 '==== copy from
 tbl2=table.open("[PathAlias.ADB_Path]\lists",FILE_RW_SHARED)
 Indx2=tbl2.order("Li_Id","Li_EntId="+s_quote(session.__protected__En_Id))
 tbl2.fetch_find(vFromListID)
 '==== copy to
 tbl3=table.open("[PathAlias.ADB_Path]\lists",FILE_RW_SHARED)
 Indx3=tbl3.order("Li_Id","Li_EntId="+s_quote(session.__protected__En_Id))
 tbl3.fetch_find(vToListID)
 tbl3.change_begin()
 tbl3.Li_pe_ul01=tbl2.Li_pe_ul01
 tbl3.Li_pe_ul02=tbl2.Li_pe_ul02

 
 ...and so on...
 
 tbl3.change_end(.t.)
 tbl2.close()
 tbl3.close()
end if
'==== copy people      <<== this is where I have trouble
if convert_type(vCopyPe,"L")=.t.
 '==== copy from
 tbl2=table.open("[PathAlias.ADB_Path]\li2pe",FILE_RW_SHARED)
 Indx2=tbl2.order("","Li_Id="+s_quote(vFromListID))
 tbl2.fetch_first()
 '==== copy to
 tbl3=table.open("[PathAlias.ADB_Path]\li2pe",FILE_RW_SHARED)
 '==== copy all from tbl2
 while .not.tbl2.fetch_eof()
  tbl3.enter_begin(.t.)
  tbl3.Li_Id=vToListID
  tbl3.Li_name=vToListName
  tbl3.Pe_Id=tbl2.Pe_Id
  tbl3.Li_name=vToListName
  tbl3.Pe_Name=tbl2.Pe_Name
  tbl3.En_Id=session.__protected__En_Id
  tbl3.Li2pe_F01=tbl2.li2pe_F01
  ... and so on ...
  tbl3.enter_end(.t.)
  tbl2.fetch_next()
 end while
 tbl3.close()
 tbl2.close()
end if
'==== copy companies

and so on for the companies, places and things...

Thanks,
Pat

[Pat Bremkamp]

OK, I got it. i just got a count of the records and used a For ...Next loop instead of a While ...End While loop.

sometimes the easy things are the hardest to see!

Pat

[Edward Larrabee]

The way to use a WHILE loop when reading through a table is:

tbl = table.open("mytable")
tbl.fetch_first()

while .not. tbl.fetch_eof()
    ...do stuff
    tbl.fetch_next()
end while

tbl.close()