Transform script for Bulk upload of participants

Poomathy09
Tera Contributor

‎07-23-2024 03:04 AM

We have to write script for bulk upload participant using transform script and check whether the request stage is in Execution Team, currently assigned to person of exceution team and excel uploading person is same or else to throw error message stating that ',Only assigned to person should be able to modify the excel' and also to check the state of batch is initiated or inprogress else throw an error message stating that 'State of batch should be initiated or inprogress else modification of participant state is not allowed'. 

 

We tried writing these lines but we couldn't get the expected result as per the conditions.

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

        // Add your code here
        var assignedTo;
        var activity_code_sys_id;
        var state_number;
        var current_status;
        var excel_status;


        var task_Req = new GlideRecord('x_gmss_academyreq_skill_tracker');
        task_Req.addQuery('number', source.u_request_id);
        task_Req.query();
        var sysid;
        if (task_Req.next()) {
            sysid = task_Req.sys_id;
        }
        var task_E = new GlideRecord('x_gmss_academyreq_skill_tracker_task');
        task_E.addQuery('parent', sysid);
        task_E.query();

        while (task_E.next()) {
            if (task_E.stage == 'LD Execution Team') {
                assignedTo = task_E.getValue('assigned_to');
            }
        }
        if (assignedTo == gs.getUserID) {
            log.info("OnBefore Row 1");
        } else {
            log.error('Assigned to person of execution team only should upload the Excel file');
        }


        var parent_sys_id;
        if (source.parent_activity == "") {

            var activity_sys_id_gr = new GlideRecord("x_gmss_academyreq_ext_lms_activity_catalogchange");
            activity_sys_id_gr.addQuery("code", source.u_activity_code);
            activity_sys_id_gr.query();

            if (activity_sys_id_gr.next()) {
                activity_code_sys_id = activity_sys_id_gr.sys_id;
            }

            var parent_sys_id_gr = new GlideRecord("x_gmss_academyreq_skill_tracker");
            parent_sys_id_gr.addQuery("number", source.u_parent);
            parent_sys_id_gr.query();

            if (parent_sys_id_gr.next()) {
                parent_sys_id = parent_sys_id_gr.sys_id;

            }
            var batch_gr = new GlideRecord("x_gmss_academyreq_skill_tracker_activity_task");
            batch_gr.addEncodedQuery('activity_code=' + act_sysid + 'request_id=' + parent_sysid + 'batch=' + source.u_batch);
            Batch_state.query();

            if (batch_gr.state != 'initiated' && 'inprogress') {
                log.error('Batch status is not initiated. Modification of the participant state is not allowed.');
            }


            if (batch_gr.next()) {
                current_status = batch_gr.getDisplayValue("state");
                excel_status = source.u_state;



                var attendee_gr = new GlideRecord("x_gmss_academyreq_skill_tracker_activity_task_attendee");
                attendee_gr.addQuery("parent", parent_sys_id);
                attendee_gr.addQuery("activity_code", activity_code_sys_id);
                attendee_gr.addNullQuery("parent_activity");
                attendee_gr.query();

                if (excel_status == "Cancelled") {
                    state_number = 4;
                } else {
                    if (excel_status == "Initiated" && current_status == "Not Yet Started") {
                        state_number = 5;
                    } else if (current_status == "Initiated" && excel_status == "In-Progress") {
                        state_number = 2;
                    }
                }
            }
            }
        })(source, map, log, target);
 
Kindly gothrough and help us with the changes to be made to get the expected result.
0 Helpfuls
  • 269 Views
2 REPLIES 2

Satishkumar B
Giga Sage
Giga Sage

‎07-23-2024 03:28 AM

Hi @Poomathy09 
check the below code:

 

 

(function runTransformScript(source, map, log, target /*undefined onStart*/) {

    // Initialize variables
    var assignedTo;
    var activity_code_sys_id;
    var state_number;
    var current_status;
    var excel_status;

    // Query for the task request using the request ID from the source
    var task_Req = new GlideRecord('x_gmss_academyreq_skill_tracker');
    task_Req.addQuery('number', source.u_request_id);
    task_Req.query();
    var sysid;
    if (task_Req.next()) {
        sysid = task_Req.sys_id;
    }

    // Query for tasks related to the request
    var task_E = new GlideRecord('x_gmss_academyreq_skill_tracker_task');
    task_E.addQuery('parent', sysid);
    task_E.query();

    // Check if the stage is "Execution Team" and get the assigned person
    while (task_E.next()) {
        if (task_E.stage == 'LD Execution Team') {
            assignedTo = task_E.getValue('assigned_to');
        }
    }

    // Check if the current user is the assigned person
    if (assignedTo == gs.getUserID()) {
        log.info("OnBefore Row 1");
    } else {
        log.error('Only the person assigned to the execution team should upload the Excel file');
        return; // Stop further processing
    }

    var parent_sys_id;
    if (source.parent_activity == "") {

        // Get activity code sys_id
        var activity_sys_id_gr = new GlideRecord("x_gmss_academyreq_ext_lms_activity_catalogchange");
        activity_sys_id_gr.addQuery("code", source.u_activity_code);
        activity_sys_id_gr.query();

        if (activity_sys_id_gr.next()) {
            activity_code_sys_id = activity_sys_id_gr.sys_id;
        }

        // Get parent sys_id
        var parent_sys_id_gr = new GlideRecord("x_gmss_academyreq_skill_tracker");
        parent_sys_id_gr.addQuery("number", source.u_parent);
        parent_sys_id_gr.query();

        if (parent_sys_id_gr.next()) {
            parent_sys_id = parent_sys_id_gr.sys_id;
        }

        // Query batch status
        var batch_gr = new GlideRecord("x_gmss_academyreq_skill_tracker_activity_task");
        batch_gr.addEncodedQuery('activity_code=' + activity_code_sys_id + '^request_id=' + parent_sys_id + '^batch=' + source.u_batch);
        batch_gr.query();

        if (batch_gr.next()) {
            current_status = batch_gr.getValue("state");
        } else {
            log.error('Batch record not found.');
            return;
        }

        // Check if the batch state is either "initiated" or "in progress"
        if (current_status != 'initiated' && current_status != 'inprogress') {
            log.error('Batch status is not initiated or in progress. Modification of the participant state is not allowed.');
            return;
        }

        // Determine the state number based on excel_status and current_status
        excel_status = source.u_state;

        if (excel_status == "Cancelled") {
            state_number = 4;
        } else if (excel_status == "Initiated" && current_status == "Not Yet Started") {
            state_number = 5;
        } else if (current_status == "Initiated" && excel_status == "In-Progress") {
            state_number = 2;
        }
    }

})(source, map, log, target);

 

 

 

  • Double-check table names, field names, and values such as stage, state, and activity_code to ensure they match your actual instance's data model.
  • Ensure that the addEncodedQuery syntax and conditions align with your specific requirements.

……………………………………………………………………………………………………

Please Mark it helpful 👍and Accept Solution✔️!! If this helps you to understand. 

 

0 Helpfuls

Poomathy09
Tera Contributor
In response to Satishkumar B

‎07-23-2024 03:40 AM

Hereby attaching the participant excel fields for bulk upload user story, participant table screenshot and batch table screenshot

Poomathy09_0-1721731122134.png

Poomathy09_1-1721731172171.png

 

Based on this can you help us to make changes in the code

Preview file
10 KB
0 Helpfuls