Copy a Record in a Set
by Scott Emerick

One night I ran across a post on the Alpha forum asking for help in duplicating a record in a form based on a set. The user asking for help referenced a script "Duplicate a set's entries", written by Dr. Peter Wayne, that was posted on learnalpha.com. He was unable to get Dr. Wayne's script to work, so I decided to take a look.

I had never tried to do this myself, so my interest was partly to be helpful and also to learn a new trick. I studied Dr. Wayne's script and tried it out on one of my own applications and it worked as advertised, so why didn't it work for this user?

I received a copy of the user's database and began my investigation. The sample application the user supplied had the following set:		  

''XBasic script by Peter Wayne
dim hdr as p
dim items as p
dim old_parent as b
dim count_items as n
dim i as n
hdr = table.current(1)
items = table.current(2)
count_items = count(Inv_Items->Inv_No,GRP->INV_HDR)
if count_items > 0 then
dim records[count_items] as b
i = 0
items.fetch_first()
while i < count_items
i = i + 1
records[i] = items.record_data_get()
items.fetch_next()
end while
end if 'count_items > 0
old_parent = hdr.record_data_get()
hdr.enter_begin()
'' note, in this table the inv_no is
' an autoincrement field
' a new inv_no is assigned by the field rule,
' which overrides the Xbasic record_data_set()
hdr.record_data_set(old_parent)
hdr.enter_end(.t.)
if count_items > 0 then
for i = 1 to count_items
items.enter_begin()
items.record_data_set(records[i])
items.inv_no = hdr.inv_no
items.enter_end(.t.)
next
end if ' count_items > 0
parent.resynch()
end
As you can see, the set has 1 child table with a 1:M link and 4 grandchild tables, 3 with a 1:M link and 1 with a 1:1 link all on the child table. Dr. Wayne's script only copied the parent and any child records, so I needed to modify his script to also include the grandchild records. I began by creating pointers for table.current(3), (4), (5) and (6). I then used the code that copied the child records and modified it to copy the grandchild records and placed them in the same loop. Well, this attempt failed poorly. Not only did the script not copy the grandchild records, it also went into an endless loop duplicating the first child record over and over again until I shut A5 completely down. It seemed to me like the FETCH_NEXT() was failing, but why? I was obviously doing something wrong. I posted a message on the board with the assumption that the fetch_next function was failing. Two responses from the board suggested I used the "outline level" for the fetch_next(). Outline level? Huh? After reading the docs on it, I came to the conclusion that this is not what I need. At this point I decided to contact Dr. Wayne and ask him for his help.
I explained to him I was trying to help another user on the board and I am having a problem too. I explained to him that I thought that the fetch_next() function was failing and attached a very descriptive example that he could see that I was right. He replied to my request for help and corrected my script which included the outline level. OH NO!! (not the exact reaction, but .. ) I realized that I posted what I see as a very uneducated answer on the message board back to people that obviously knew more then I did. Talk about embarrassing! Well my understanding of the outline level for the fetch_next() is still not 100%, so I will save that for another article written by someone with a better understanding then me. Anyway, back to the issue at hand. After some trial and error, plus another email to Dr. Wayne, I got a working script that would do the job and do it well.
To make it easier to follow the script I created a new set shown to the right. The only change besides the table names I made from the original set structure was placing the 1:1 linked grandchild table first. This is not absolutely necessary, however, it needed to be the first grandchild record copied in the script so I structured the set the same way. The linking field between the parent(header) and child(mychild) tables is called "Transno" which is an autoincremented field. The
linking field between the child and grandchild tables is called "Ptransno" which is also an autoincremented field. "Transno" is incremented when a new parent record is created and "Ptransno" is incremented when a new child record is created. Below is the working script with comments to better understand how it works.
dim mm as p
dim mc as p
dim i as n
dim old_mm as b

''Set up table pointers for parent and child tables
mm = table.current(1)
mc = table.current(2)
g1 = table.get("mygrandchild1")
g2 = table.get("mygrandchild2")
g3 = table.get("mygrandchild3")
g4 = table.get("mygrandchild4")

mm2 = table.open(mm.name_get())
mc2 = table.open(mc.name_get())
g11 = table.open(g1.name_get())
g22 = table.open(g2.name_get())
g33 = table.open(g3.name_get())
g44 = table.open(g4.name_get())

''First, the header record gets copied
old_mm = mm.record_data_get()
mm2.enter_begin()
''note: in the table the transno is an autoincrement field. A new transno is assigned
'' by the field rule which overrides the Xbasic record_data_set()
mm2.record_data_set(old_mm)
mm2.enter_end(.t.)

'Check to make sure a child record exists. The exist() function could also be used.
kount = count(mychild->transno,GRP->header)

''Now we copy the child and grandchild records
'IF kount > 0 THEN
mc.fetch_first()
WHILE .not. mc.fetch_eof()
mc_rec=mc.record_data_get()
mc2.enter_begin()
mc2.record_data_set(mc_rec)
mc2.transno = mm2.transno 'Overwrite to the new incremented value
mc2.enter_end(.t.)

' Grandchild1
'Since this table has the 1:1 link there is no need to fetch_first() or use While Next
g1_rec=g1.record_data_get()
g11.enter_begin()
g11.record_data_set(g1_rec)
g11.ptransno=mc2.ptransno 'Overwrite to the new incremented value
g11.enter_end(.t.)

' Grandchild2
g2.fetch_first()
WHILE .not. g2.fetch_eof()
g2_rec=g2.record_data_get()
g22.enter_begin()
g22.record_data_set(g2_rec)
g22.ptransno=mc2.ptransno 'Overwrite to the new incremented value
g22.enter_end(.t.)
g2.fetch_next(1)
END WHILE

' Grandchild3
g3.fetch_first()
WHILE .not. g3.fetch_eof()
g3_rec=g3.record_data_get()
g33.enter_begin()
g33.record_data_set(g3_rec)
g33.ptransno=mc2.ptransno 'Overwrite to the new incremented value
g33.enter_end(.t.)
g3.fetch_next(1)
END WHILE

' Grandchild4
g4.fetch_first()
WHILE .not. g4.fetch_eof()
g4_rec=g4.record_data_get()
g44.enter_begin()
g44.record_data_set(g4_rec)
g44.ptransno=mc2.ptransno 'Overwrite to the new incremented value
g44.enter_end(.t.)
g4.fetch_next(1)
END WHILE
mc.fetch_next(1) 'Get the next child record
END WHILE
'END IF

'Go to the new record that was just created
mm.fetch_goto(mm2.recno())

'Close Opened tables
mm2.close()
mc2.close()
g11.close()
g22.close()
g33.close()
g44.close()
END

If you would like a working demo of this article, feel free to contact me at mailto:keytech@keytech2000.com?Subject=Copy a record in a set.

Many thanks to Dr. Peter Wayne for his help and guidance.