Jump to content
  • 0

Trigger for Bulk Copying of records, if possible.




I'm not sure if this is possible but I need to set up a trigger to copy multiple records from one table to a) the same table and b) to another table. This is basically so a user can draw from old records for new projects without having to re-enter all the data again. The user would be doing this via Bulk Edit (selecting multiple records and updating a field for them all). 

There are a couple ways I thought this maybe could be done: 

1) having Bulk Edit update one field ('copy_me') to 'True' and then a trigger that runs 'On Update' using Where 'copy_me'= True to select the records to copy. Then run  duplicate those records (but not all fields, only some) on the same table OR into another table. After that the next step in the trigger would be to change 'copy_me' to False Where 'copy_me' was True so that the record was no longer 'triggerable'. 

2) use a separate table to store, in a new record, the Id's (integers) of each record that's been chosen via Bulk Edit and then running an 'on Insert' trigger that Joins those Id's (they're in one text field, comma separated) to the main table's Id's and pulls various fields to copy them into a new table. I currently have the first half of this scenario set up (storing multiple record Id's in a separate table's text field via Bulk Edit) but don't know how to create a 'duplicate records' trigger for it. 

If anyone knows the specifics of getting a trigger like this to work-- or a better way to copy multiple records after they've been updated via Bulk Edit-- I'd really appreciate the help. 

Link to comment
Share on other sites

4 answers to this question

Recommended Posts

  • 0

Hi AtayBalunbalunan,

Thanks for the confirmation- I was able to do this using a trigger in the table after all. Unfortunately, I have 2 issues that make it not possible to use this: 

1) there's an existing trigger on the table that runs auto-numbering (not like the autonumber field-type but a special way of doing it that gets values from other tables to create a number). When the Bulk Copy trigger runs it will run the auto-number trigger and get the correct value for the 1st new, copied record but then it will reuse that value on all additional copied records (so something like '55' is repeated multiple times instead of 55, 56,57, etc). I don't think I can incorporate the auto-number trigger into the bulk-copy trigger in a way this doesn't happen. 

2) a table's triggers won't copy a 'file-type' field's values. I use this field for images and they are key to my app and their file path needs to be copied as well as the rest of the data. Strangely, CB doesn't allow this (I don't need to duplicate the image file itself, just its path). I started using AWS for images and saving their paths in a text field, and that copies no problem. But the 10k or so images I have already in CB need to be used still, so this holds it up as well. 

So basically, the answer is that Bulk Copy can work with a table's triggers-- IF you don't have a scenario like the above happening. 

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...