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_]

You could add a field for the sys_id at the import table. After the import you can loop over the import set and fill in the sys_id before you transform it.

[jamesgb]

Hi Sebastian,

Have you had a look the code? Can you give a code example?

Mehmet.

---

[Greg42]

Hi Mehmet,

You're having an issue as you're trying to update a dot-walked record which is referenced by target_table value and that won't work. You will have to query that table directly if you want to update it.

Regards

Greg

[jamesgb]

Hi Greg,

Why it doesn't work that way if I update a dot walked reference object?

Yes, I can query the table directly using GlideRecord but then the problem is that I have to find out the last record that is added with this import set. For example, there is 100 records in the import set and transform map source table at the moment. When I import a new CSV file say for example that there are 30 records in the CSV file. In this case, the update must start from 101. record.

This is a subtle point. For that reason, instead of updating all records in the import set table, if I pass a parameter to transform map then I can use it while transforming the current CSV data, row by row and I can add sys_id to each row during each row gets transformed and recorded to target table.

What do you recommend?