Scripted API to Query data before record creation

[Chad R]

Hi All! I have a good one today, I have a requirement to create a scripted API to create standard changes from another in house product. All was going well until I hit a snag. The data coming from the in house product will be giving me string values only, meaning I have to query those strings to get the sys_ids of each of those fields before I can create the standard change. The script below is where I have left off, the records get created but the reference fields like requested_by and company are left empty. Any advice is greatly appreciated as always many thanks!

(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

       this.status = '200';
    var respObj = {}; //declare the response object
    var payload = request.body.data;  //retrieving the JSON body
    var requested_by = payload.requested_by;  
    var company = payload.company;
    var business_service = payload.business_service;
    var assignment_group = payload.assignment_group;
    var change_tester = payload.u_change_tester;

(function(){

		var grinc = new GlideRecord("change_request");
    
        grinc.initialize();
        grinc.company = company;
        grinc.requested_by = requested_by;
        grinc.business_service = business_service;
        grinc.assignment_group = assignment_group;
        grinc.u_change_tester = change_tester;
		grinc.type = 'standard';
		grinc.std_change_producer_version = '3c1015d9874f911097842137cebb358f'; // Sys ID of the Standard Change Templates version
		grinc.applyTemplate('PUPS Production Monthly Patching');
        grinc.insert();
	
	var RB = new GlideRecord("sys_user");
	RB.addQuery('user_name', 'requested_by');
    RB.query();
	while(RB.next())
		{
		
		grinc.requested_by = RB;
		grinc.u_change_tester = RB;
		grinc.update();
		}
	
	

        this.status = '200';
        respObj.body = {
            "message": "Creation Success!",
            "detail": "Change " + grinc.number + " created successfully"
			
        };


    if (this.status == '200') {
        response.setBody(respObj);
        response.setStatus(this.status);
        return response;
    } else {
        var setError= new sn_ws_err.ServiceError();  //this API used to set the custom error messages
        setError.setStatus(this.status);
        setError.setMessage(respObj.body.message);
        setError.setDetail(respObj.body.detail);
        return setError;
    }
}());
})(request, response);

[Sebastian L]

When you query the sys user you have 'requested_by', that should be without the ''.

I don't see where you look up the company, so if the name differs slightly that won't take that record.

I would also maybe do the lookup of the user in a separate function and then do it before the insert of the record, so you call it and set the requester before inserting the record. Does it make sense? 

[Chad R]

Hi thanks for the reply, that does make sense, I'm only trying on the requested_by user at the moment to get something working first, let me see if I break it into its own function and get that working.

[Tony Chatfield1]

Hi, based on your description you might need to look at glidequery\lookup of reference data before (or as) you populate your change_request but would also need to allow for situations where a match is not found.
(quick example from a PDI)

var payload = {"company": "ACME Africa",}
var company = new GlideRecord('core_company');

if(company.get('name', payload.company)) {
gs.info(company.sys_id);
grinc.company = company.sys_id;
}

But rather than posting directly to target table I would use a scripted rest api solution\endpoint,
and\or post to a temp import table and then use a transform map to populate your target table.
This allows you the flexibility to map or script data as required and also allows for better data validation\management.

[Chad R]

So I tried to the below just trying to get the change tester field to show up by querying our custom u_win_id field then passing that sys_id value and it still a no go. I must not understanding how to do these glides and pass the result to the intialize portion of this scripted API.

(function process(/*RESTAPIRequest*/ request, /*RESTAPIResponse*/ response) {

    this.status = '200';
    var respObj = {}; //declare the response object
    var payload = request.body.data;  //retrieving the JSON body
    var requested_by = payload.requested_by;
    var company = payload.company;
    var business_service = payload.business_service;
    var assignment_group = payload.assignment_group;
    var change_tester = payload.u_change_tester;

 
	var tester="";
	var CT = new GlideRecord("sys_user");
	CT.addQuery('u_win_id', 'change_tester');
    CT.query();
		if(CT.next()){
			tester=CT.sys_id;
		}

		var grinc = new GlideRecord("change_request");
	
        grinc.initialize();
        grinc.company = company;
        grinc.requested_by = requested_by;
        grinc.business_service = business_service;
        grinc.assignment_group = assignment_group;
        grinc.u_change_tester = tester;
		grinc.type = 'standard';
		grinc.std_change_producer_version = '3c1015d9874f911097842137cebb358f'; // Sys ID of the Standard Change Templates version
		grinc.applyTemplate('PUPS Production Monthly Patching');
        grinc.insert();
	
	
	
	

        this.status = '200';
        respObj.body = {
            "message": "Creation Success!",
            "detail": "Change " + grinc.number + " created successfully"
			
        };


    if (this.status == '200') {
        response.setBody(respObj);
        response.setStatus(this.status);
        return response;
    } else {
        var setError= new sn_ws_err.ServiceError();  //this API used to set the custom error messages
        setError.setStatus(this.status);
        setError.setMessage(respObj.body.message);
        setError.setDetail(respObj.body.detail);
        return setError;
    }
	
})(request, response);