COPY_RECORD (similar to DUPLICATE_RECORD)

[lecorr]

Hello,

Because I'am working on a project where it is needed to duplicate non adjacent records, I developped a COPY_RECORD procedure:

The needed things:
A block containing a 'source' record you want to duplicate and a dest record IN THE SAME BLOCK to want to populate with the data of the source.

First You HAVE TO
GO_BLOCK('BLOCK');
CREATE_RECORD;
lRecordCreated := :SYSTEM.CURSOR_RECORD; -- To know where you are
:BLOCK.Field := VALUE; -- Populate a field of the created record so form will not drop it when the COPY_RECORD navigates between records.
Then
COPY_RECORD(lSourceRecord, lRecordCreated);
...

Copy_record is :

PROCEDURE COPY_RECORD(vSource NUMBER, vDest NUMBER) IS
  lItem VARCHAR2(30);
  lValue VARCHAR2(1000);
BEGIN
  lItem := GET_BLOCK_PROPERTY(:SYSTEM.CURSOR_BLOCK, FIRST_ITEM);
  LOOP
    IF GET_ITEM_PROPERTY(:SYSTEM.CURSOR_BLOCK||'.'||lItem, ITEM_TYPE) != 'BUTTON' THEN
      GO_RECORD(vSource);
      lValue := NAME_IN(:SYSTEM.CURSOR_BLOCK||'.'||lItem);
      IF lValue IS NOT NULL THEN
        GO_RECORD(vDest);
        Synchronize;
        COPY(lValue, :SYSTEM.CURSOR_BLOCK||'.'||lItem);
      END IF;
    ELSE
      IF GET_ITEM_PROPERTY(:SYSTEM.CURSOR_BLOCK||'.'||lItem, ITEM_TYPE) != 'BUTTON' THEN
        MESSAGE('I don't know how to copy objects like ' ||GET_ITEM_PROPERTY(:SYSTEM.CURSOR_BLOCK||'.'||lItem, ITEM_TYPE));
      END IF;
    END IF;
    EXIT WHEN lItem = GET_BLOCK_PROPERTY(:SYSTEM.CURSOR_BLOCK, LAST_ITEM);
      lItem := GET_ITEM_PROPERTY(:SYSTEM.CURSOR_BLOCK||'.'||lItem, NEXTITEM);
  END LOOP;
END;

For now, it copies all fields via a varchar2 convertion, this could be changed to better manage data, bur this is
So, I hope this can help some of you.

 

[sem]

What version do you use? At least since 4.5 Forms contains DUPLICATE_RECORD built-in, did you know about it ?

Regards, Dima

 

[lecorr]

Yes, but DUPLICATE_RECORD only duplicate the record from the data just above the one you are on. I needed to duplicate any record of a block, what this function does if you give it the source and the destnation records.

If you know of another way to do it, tell me please, I didn't find anything about this in my 6.0.5 version.

Regards,

Christian

 

[sem]

But why do you need it? The order will be destroyed on the next query. So creating n-th record as a copy of k-th record is quite small improvement in comparison with creating (n+1)-th record as a copy of n-th one, isn't it? Besides this all your GO_RECORDs will cause validation to fire, thus if your block contains mandatory items, the procedure most probably fails. In this case it would be better to create recordgroup and use some record as a buffer for the whole record. What do you think about it?

Regards, Dima

 

[lecorr]

Sem,

Thank you for your advice, but:

1: It works
2: This block is not based, this is a block I use to hold criterias of queries (several queries).
3: Even for a based block, some people could like to copy records to create new ones, and those ones would be written to the database at the next commit_form.

So, I think this is an improvement. Or would you tell us that the copy/paste of an excel sheet line is unusefull?

Regards,

Christian

 

[sem]

I don't tell that it's not an improvement. I tell that it can not be widely used without some further code refining. It works in your specific case. Try to add some record-level validation and it will probably fail (or at least consumes more resources that it might). It's too inefficient to not tell about it.

Copying records to create new ones may be easily done by DUPLICATE_RECORD. After requerying of base table block the order of record will be probably lost, thus the place where the record was initially inserted (record number) in general doesn't matter.



Regards, Dima