Copying records


#1

Is it a smart way to copy records with child records?
I have a record with one child record who again has 7 child records.
There is one key element in all the records to be changed.

Trond


#2

I’ve never used this, but maybe it would do what you want. https://sterlingdata.com/copyflash/

In the past, I’ve done it by hand with a couple of functions.


#3

There are a couple of free copy templates around. I think BoTpl has one. Not sure where to find them these days however


#4

I have tried Bo template and I have downloaded a demo from Sterlingdata. I don’t think any one of them manages childs of child records.

I have hand coded the copying, but I hoped there were a smarter way to copy records.


#5

Change the Relations queue property of the RelationManager class to protected.
Derive a new relation manager class and copy the logic for the Update/UpdateSecondary methods to allow you to walk through the records and child records.


#6

Thank you for trying to help me out here, but unfortunately, I have no idea how to do this.

Are there any documentation or examples of this?


#7

Maybe my PrivateClass would be useful in lieu of changing the shipping classes. https://github.com/jslarve/PrivateClass


#8

I’m not sure if that helps me, but thanks for trying.


#9

Trond,
I am coming away with the impression that you want one of us to write the code for you.
Saying “I have no idea how to do this” doesn’t help us help you.
If you look at the code I pointed out for the relation manager in abfile.clw and have question about how it works, then we can answer those and explain the code.

Rick


#10

I’m pretty sure there’s a copy record in bo.tpl. It’s always been free. I think there is a copy here https://mega.nz/#!EFFnVAgB!V1oKSxHdNbwuzGHYykn9gWyYvN3RIFyMZ3sFevW1DJM


#11

One approach I have used is use an alias. I would suggest building your own alias in the dictionary rather than creating a “clarion alias”…i.e. to create a second table buffer for both your parent and child files in your dictionary with exactly the same format as the original table, same keys, etc. It is an extra table in your dictionary. Just leave out the relations. Then set the label for the second structure to tableALIAS and set the prefix to prefixALIAS. (Substitute the actual table name and prefix name for those). Set the same connection string and full path name in the properties for the second file. Now the alias table can be used just like a real table in your copy procedure without affecting the real table you are copying from.

Since you have both a parent and child table, this means there will be a total of 4 tables in your dictionary. A parent and child table which most procedures will use, and a parent and child alias table which are used by your copy procedure.

This will give you two buffers and two sets of indexes in your copy procedure that are independent of each other, so you won’t need to worry about saving buffers and pointers.

The logic would go something like this. For this example, I am using and invoice header / invoice detail structure pair.

  1. Generate a new auto-incrementing primary key value using your alias parent table. (Don’t use your main table for this…you want to keep it’s buffer intact because you plan to copy it in a bit.) This would be for example, maybe your invoice number or whatever you have for your primary key. Save this primary key value in a temporary variable.

  2. Use a deep assignment operator to copy the main table structure buffer to the alias buffer structure. Then overwrite the primary key value with the temporary value you saved in step 1.

  3. Add the new record to the parent “alias” table. Now you have copied the parent record over.

  4. Now start a loop and for each child record, do a deep assignment operator with the child record.

  5. Now overwrite the foreign key value (the invoice number in the child table) with the primary key value you generated in step 1.

  6. Add the new record to the parent child table. Repeat this for each main child record.

You might consider using a transaction logout and commit before and after, with rollback if something should fail.

Jerry Ray


#12

Here is some simple example code using invoice header / invoice detail structures (assuming you put the extra two alias tables into your dictionary as I described). To keep it simple so you could follow the logic, I used straight Clarion code without any ABC libraries. This code should work with any driver or any backend. You can substitute the equivalent ABC code if you like.

SHARE(InvoiceHeader)
SHARE(InvoiceDetail)
SHARE(InvoiceHeaderALIAS)
SHARE(InvoiceDetailALIAS)

LOGOUT(1,InvoiceHeader,InvoiceDetail,InvoiceHeaderALIAS,InvoiceDetailALIAS)

!-----------Generate primary auto-inc key first
CLEAR(HEADERALIAS:Record)
SET(HEADERALIAS:ByInvoiceNumber)
PREVIOUS(InvoiceHeaderALIAS)
TempInvNo# = HEADERALIAS:InvoiceNo += 1
CLEAR(HEADERALIAS:Record)

HEADERALIAS:Record :=: HEADER:Record
! NOTE: since table structures match HEADERALIAS:Record = HEADER:Record would also work

HEADERALIAS:InvoiceNo = TempInvNo#
ADD(InvoiceHeaderALIAS)
IF ERROR() THEN ROLLBACK;ASSERT(~ERRORCODE()).

! --------------- now do the child recs

CLEAR(DETAIL:Record)
DETAIL:InvoiceNumber = HEADER:InvoiceNumber
SET(DETAIL:ByInvoiceNo,DETAIL:ByInvoiceNo)
LOOP
NEXT(InvoiceDetail)
IF ERROR() THEN BREAK. ! if at eof then break out of loop
IF DETAIL:InvoiceNumber <> HEADER:InvoiceNumber Then BREAK. ! if no more children then brk

!-----------------------ok we have a child record here to copy

DETAILALIAS:Record :=: DETAIL:Record ! deep assignment here
DETAILALIAS:InvoiceNo = TempInvNo# ! store the foreign key for the child table
ADD(InvoiceDetailALIAS)
IF ERROR() THEN ROLLBACK;ASSERT(~ERRORCODE()).
END

!----all done, clean up!

COMMIT
CLOSE(InvoiceHeader)
CLOSE(InvoiceDetail)
CLOSE(InvoiceHeaderALIAS)
CLOSE(InvoiceDetailALIAS)


#13

Minor typo:

TempInvNo# = HEADERALIAS:InvoiceNo += 1

Should read:

TempInvNo# = HEADERALIAS:InvoiceNo + 1


#14

Rick,
I’m sorry if I gave the impression that I wanted someone to write the code for me. It was how and why to change the property of the RelationManager would help me I was wondering.
I have written the code for copying the records, but in each copy there are 100 - 200 (may also be more) records to be copied. It takes some time, and I wondered if there was a faster and maybe a smarter way to do it.

Trond


#15

Sean,
I have tried Bo.tpl, and I can use it to copy child records, but I could not find out how to copy childs of childs.
Thanks.

Trond


#16

Jerry,
Thanks, I have 6 childs of child tables and I only need Alias for this procedure. So I have used local Queues instead of Alias. I’ll try to use Alias and see if the copying will be faster.

Trond