How to pass a sys_id or any other parameter to transform map?

[jamesgb]

Hello,

I have two tables that they are classical master-detail tables. These are invoice tables that details table rows hold the detail information for the invoice items.

I have import set, data source and transform map that I import data to details table from CSV files. The method I use is:

* I have a form of master table that I get the CSV file as attach

* There is a button tied to a UI action that has a script* UI action script gets the attachment from this form and copies it to a data source I created in advance

* Then this script executes the transform using GlideImportSetLoader, GlideImportSetRun, GlideImportSetTransformer

This method works that I am able to import the data to details table. Only thing I need is that I want to update a particular reference field for all rows in the detail table with the sys_id of the master table. So that the master and the detail tables will be connected automatically when I import data to details table.

If I find a way to pass this sys_id to transform map, rest is not big deal since I have scripts in transform map that manipulates data before being imported to rows. So, once I pass this sys_id to transform map, I can update the transforming row's corresponding field with this sys_id.

I tried to do that in the lines in between

//=== This section doesn't work START

//=== This section doesn't work END

comments in below code but that didn't work as I expected because transform map gets executed afterwards, that may be changing the importing data.

How can I do that? Or do you have other way/experience for similar needs that you used and worked. Any advice is welcome!

Here is my script that I have executed in the UI action in master table's form:

gs.addInfoMessage('Importing data from the attached CSV file...');
// This is the data source sys_id in the related import set
var dataSourceSysId = '5dba57e90fcc8f0064ab0eece1050ed1';
var dataSourceRecord = new GlideRecord("sys_data_source");
dataSourceRecord.get(dataSourceSysId);
var sourceTable = current.getTableName(); 
var sourceID = current.sys_id.toString(); 
var targetTable = dataSourceRecord.getTableName(); 
var targetID = dataSourceRecord.sys_id.toString(); 
// Get attachments of source table 
var gr = new GlideRecord("sys_attachment"); 
gr.addQuery("table_name", sourceTable); 
gr.addQuery("table_sys_id", sourceID);
gr.orderByDesc('sys_updated_on');
gr.query();
if (gr.getRowCount() > 0) {
       gs.addInfoMessage("Found "+gr.getRowCount()+
                                               " attachment on Supplier Invoice Table.");
       while (gr.next()) {       
               var attachment = GlideSysAttachment();
               attachment.deleteAll(dataSourceRecord); 
               gr.table_name = targetTable; 
               gr.table_sys_id = targetID; 
               var oldid = gr.sys_id.toString();               
               var newid = gr.insert();
               var doc = new GlideRecord("sys_attachment_doc"); 
               doc.setWorkflow(false); // Do not run any business rules
               doc.addQuery("sys_attachment", oldid); 
               doc.query(); 
               while (doc.next()) { 
                       doc.setValue("sys_attachment", newid); 
                       doc.insert(); 
               }               
       } 
       // Lets execute transform map for corresponding import set now
       if(dataSourceRecord.get("import_set_table_name", "u_test_demo_124")) {
               //=== This section doesn't work START
               var transFormMapId = 'b14955b30f737e0064ab0eece1050e95';
               var transformMapRecord = new GlideRecord("sys_transform_map");
               transformMapRecord.get(transFormMapId);       
               transformMapRecord.target_table.u_supplier_invoice = current.sys_id.toString();
               var res = transformMapRecord.target_table.update();
               gs.addInfoMessage(typeof(transformMapRecord)+'Executed so far 1: ' + 
                                                   transformMapRecord.target_table.u_supplier_invoice);
               //=== This section doesn't work END
               var loader = new GlideImportSetLoader();
               var importSetGr = loader.getImportSetGr(dataSourceRecord);
               loader.loadImportSetTable(importSetGr, dataSourceRecord);
               var importSetRun = new GlideImportSetRun(importSetGr.getUniqueValue());
               //TODO: Check out logs that produced by below line
               //var importLog = new GlideImportLog(importSetRun, importSetGr.data_source.name);
               var ist = new GlideImportSetTransformer();
               ist.setImportSetRun(importSetRun);
               ist.transformAllMaps(importSetGr);               
       }
       gs.addInfoMessage('Executed so far.');
}// If there is attached files end

Mehmet.

---

[Sebastian R_]

I don ´t know wether this is possible with SN or not. Maybe you can modify the attachment after it is added to the master or before it is imported so that you add a new column in the csv file.

Another dirty workaround: Download file, add column, upload file.

[jamesgb]

Hi Sebastian,

It is one of the options to modify the attachment, I have been thinking of the implement this option.

Because, as you say, it is unknown that if what I askes is possible in ServiceNow.

Thank you for sharing your advice.

Mehmet.

---

[karthiknagaramu]

Hi,

Would it be possible add a unique value that relates the 2 table and import that in the target table.

I understand you don;t have any unique value and you are looking at options to modifying the source CSV file to add a unique value.

Instead can you append multiple columns in the source to form a unique value in transform and import that to a field in the target table.

And later use the same append logic to find the Sys_id of the master table and update in the child table.

Regards,

Karthik Nagaramu

[Andy M1]

@jamesgbltd

what did you end up doing?