How to get sys_id of target in a transform script?

[thomaskennedy]

I'm importing a JSON file containing inventory items, each of which has zero or more Service Requests. A service request has zero or more Letters.

{ "result": [{
  "inv": "this is inventory",
  "serviceRequests:" [{
    "sr": "zero or more service requests",
    "letters": [{
      "ltr": "zero or more letters"
    }]
  }]
]}

My target table is Inventory, and in my transform script I break out the requests[] and their letters[] into their respective tables, serviceRequest and Letter. The request table has a u_parent Reference to Inventory, and Letter has a u_parent reference to ServiceRequest.

So my transform script has to do both of these:

A. Assign the parent inventory sys_id to each serviceRequest
B. Assign the parent serviceRequest sys_id to each Letter.

(B) works fine, because by the time I'm inserting a new Letter, I have just inserted its parent serviceRequest, and the sys_id is available. But (A) doesn't work -- the Inventory sys_id is null.

(function transformRow(source, target, map, log, isUpdate) {
  var serviceRequests = source.getValue("u_servicerequests");
  serviceRequests = JSON.parse(serviceRequests);
  serviceRequests.forEach(function(req) {
    var gr_service = new GlideRecord("service_request");
    gr_service.initialize();
    ...
    gr_service.setValue("u_parent", target.getValue("sys_id")); // (A) sys_id is null
    gr_service.update();
    ...
    ... (B) not shown here
    ...
})(source, target, map, log, action === "update");

So I tried this:

gr_service.initialize();
...
target.update();
gr_service.setValue("u_parent", target.getValue("sys_id"));
gr_service.update();

And that gets me the inventory sys_id. But then my Inventory gets inserted a second time.

I could work around this by stuffing the service and letter data into a magic field on Inventory and breaking it out with a business rule. Is there a better way?

[Prasad Pagar]

Hi,

So serviec_requests records are already there in system you are inserting them using Transform script?

What is your target here?

gr_service.setValue("u_parent", target.getValue("sys_id")); // Target is service_request for you?

Thank you
Prasad

[thomaskennedy · Accepted solution]

No actually, target is the Inventory row, the top level row.

I tried setting the ignore variable to false, and that seems to work fine. That is, it did not insert a second inventory row.

            gr_letter.setValue("u_vendorname", letter.vendorname);			
            gr_letter.setValue("u_parent", gr_service.getValue("sys_id"));
	    gr_letter.update();
        });
    });
	
	// skip the second insert
	ignore = true;
})(source, target, map, log, action === "update");