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.

---

[Greg42]

Hi Mehmet,

The best case scenario would be if you had any way/information between these two table records such that you could coalesce against. I don't see why you shouldn't have those. You shouldn't really rely on particular system sys_id numbers.

The way I would see it is that you have your invoice table and invoice details table with invoice number which you can coalesce against when you import the data.

Regards

Greg

[jamesgb]

Hey Greg,

That would be really great but the CSV files that we get data from does not have any record to be candidate for coalesce. If so, I even wouldn't ask this question to forum because that would be so obvious.

We have invoice records in the ServiceNow and CSV files coming from external sources. We need to import the data in CSV files and then related this data to our invoices.

The good news is I know the sys_id of invoice that the user attaches to CSV. Rest is just updating invoice item records that is being imported with this sys_id. The data will be related this way.

Let me try to clarify with classical database example: say for example I have a mysql table and I have invoices in that table. I have a web interface that I list all invoices. The scenario is that I choose a particular invoice from the list on this web interface. Then I have all info including unique id of this particular invouce. OK.

Then without leaving the particular invoice details page, I attach a file to that record. This file includes the invoice item data. Then once the import completed I should update all imported data with the unique id of this particular invoice. The field in the details table aka invoice item tables is a foreign key field. I would pass unique invoice id to importing script with an hidden form field, url parameter, session param, etc.

But when things come to ServiceNow, I need to use import set and transform map that this is the way to import data in ServiceNow.

Does this example make it a bit more clear?

[Greg42]

Hi Mehmet. I think I understand your setup.

I don't think that knowing invoice sys_id will help you much as you don't have that information at the transformation time. You could query the latest import set and rely on that doing what Sebastian suggested but if there is potential of more import sets being added at the same time you might run into wrong updates.

Otherwise I would go with a route of working with the other party to have them providing the data you can relate to. Not sure why you getting invoice data without any invoice reference, pretty odd to me to be honest.

Maybe someone else will have a better idea to help you out.

Regards

Greg

[jamesgb]

Hey Greg,

Yeah, it is a bit different situation.

I think I will end up modifying attached files that I will add them unique id or sys_id anyway.

Thank you for your answers.

Mehmet.

---

[jamesgb]

Or any other work around or dirty hack is welcome as well.