Separating records along with their attachments from one table to another

[EmilioGuaj]

Hi!

I am looking for suggestions on the best approach for separating records from one table to another. lets say from table "Case" and moving them to table "Business Case". There are currently records on the case table that have attachments. I was thinking of using an import set along with a transform map, this works perfectly because the table "Business Case" has the same fields as case since it is an extension of the "Case" table. My only roadblock is finding a way to also move along the attachments as well with the records.

What are you recommendations? Use a script on the transform map perhaps?

[Dominik Simunek]

The solution depends a bit on further context like a) Are the 2 tables in the hierarchy? E.g., Business Case being a child table of Case? If yes, are we able to preserve the sys_id of the migrated records., b) Do we need to preserve the previous record with attachment, or is it really complete move from table A to table B?

When preserving sys_ids would be an option, a Update Job could possibly do the job to update sys_attachment records from Case to Business Case table name. But if not, I would use a script to move it. In case you use a transform map to move the Case records, you might use onAfter transform script to transfer attachments from the source case to target case.

[vaishali231 · Accepted solution]

hii @EmilioGuaj 

You are on the right track using an Import Set and Transform Map. Since Business Case extends Case, the field mapping is straightforward.

For attachments, you do not need to export or reimport them. The best and supported way is to copy them in a Transform Map script.

Add this script in the onAfter section of your Transform Map so the target record already exists.

(function runTransformScript(source, map, log, target) {

var sa = new GlideSysAttachment();
sa.copy(
'sn_customerservice_case', // Case table
source.sys_id, // Case sys_id
'u_business_case', // Business Case table
target.sys_id // Business Case sys_id
);

})(source, map, log, target);

This will copy all attachments from the Case record to the newly created Business Case record automatically.

*************************************************************************************************************
If this response helps, please mark it as Accept as Solution and Helpful.
Doing so helps others in the community and encourages me to keep contributing.

Regards
Vaishali Singh

[EmilioGuaj]

Thank you @vaishali231 !! This worked perfectly.

One thing to keep in mind for future users that see this is to use target.getUniqueValue() instead of target.sys_id. For me at least it was not returning anything and getUniqueValue() fixed this issue.

I had another issue with source.sys_id, but I noticed in my case the correct variable that had the sys id was source.u_sys_id instead.

Thank you!

[EmilioGuaj]

I also used target.getUniqueValue() instead of target.sys_id since target.sys_id was not returning anything and getUniqueValue() fixed this issue.

Another problem I encountered is that I noticed the variable source.sys_id in my case was not the correct one, instead it was target.u_sys_id. Just make sure you are targeting the correct variables or else the script will simply not work.