Create time entries from task assignments when the user creates a new timesheet

This script creates time entries from task assignments when the user creates a new timesheet.

When the user creates a new timesheet, toggle checkbox to have it prefilled with data fetched from the current task assignments.

• Automate timesheet creation with relevant tasks an employee is working on.

• Saves a lot of time digging through pick lists, finding correct tasks.

• FUTURE: Could deploy project task afterSave script to auto-update existing open timesheets as assignments change (practical for monthly timesheets).

Follow the steps below or download the solutions file, see Creating Solutions for details.

Setup

1. Create a new Timesheet [New] form script deployment.

2. Enter a Filename and click SAVE. The extension ‘.js’ is automatically appended if not supplied.

3. Click on the script link to launch the Scripting Studio.

4. (1) Copy the Program Listing below into the editor, (2) set the After save event, and set prepopulate_ts_from_assignments as the Entrance Function.

   

5. Set up a Checkbox custom field for Timesheet. The first in each pair is a Pick List with a List source of User. The second in each pair is a Ratio. You can set the Divider text for the very first custom field to Commission to place the custom fields in their own section.

   

6. Use the Form schema to identify the correct param names for the custom fields and change the array at the top of the script accordingly.

   
   Tip:

   If the new custom field is not listed in the Form schema, navigate to Timesheets, create a new Timesheet form without saving (this with will refresh the custom field list), and then open the Scripting Studio again.

Program Listing

          var CUST_FIELD = 'ts_prefill_from_task_assignments__c'; // Use Form schema to find param name
function prepopulate_ts_from_assignments(type) {

    var ts = NSOA.form.getValue(CUST_FIELD);

    // if the checkbox is not ticked, exit
    if (ts === false) {
        return;
    }

    // retrieve the current user
    var user = NSOA.wsapi.whoami();

    //look for current assignments for this user
    var defaultPerRow = find_assignments(user.id);

    // retrieve the new object
    var newr = NSOA.form.getNewRecord();
    var timesheet = new NSOA.record.oaTimesheet();

    timesheet.id = newr.id;
    timesheet.default_per_row = defaultPerRow;

    var result = NSOA.wsapi.modify([], [timesheet]);
}


//find the assignments for this user and return a string for timesheet.default_per_row
function find_assignments(userid) {

    // fetch a list of task assignments for the current user
    var taskAssign = new NSOA.record.oaProjecttaskassign();
    taskAssign.userid = userid;

    var readTasksAssign = {
        type: "Projecttaskassign",
        method: "equal to",
        fields: "projecttaskid",
        attributes: [{
            name: "limit",
            value: "0,1000"
        }],
        objects: [taskAssign]
    };

    var CSV = {
        pt_id: [],
        cp_id: []
    };
    var resultTaskAssign = NSOA.wsapi.read(readTasksAssign);

    // iterate through all the task assignments and filter only current ones
    // retrieve all tasks that belong to user, started in the past and percent_complete < 100
    if (resultTaskAssign[0].errors === null && resultTaskAssign[0].objects) {

        for (var i = 0; i < resultTaskAssign[0].objects.length; i++) {

            var projectTask = new NSOA.record.oaProjecttask();
            projectTask.id = resultTaskAssign[0].objects[i].projecttaskid;

            var readTask = {
                type: "Projecttask",
                method: "equal to",
                fields: "calculated_starts,starts,percent_complete,customerid,id,projectid",
                attributes: [{
                    name: "limit",
                    value: "0,1000"
                }],
                objects: [projectTask]
            };

            var resultTask = NSOA.wsapi.read(readTask);

            // do we have results? 
            if (resultTask[0].errors === null && resultTask[0].objects) {
                for (var k = 0; k < resultTask[0].objects.length; k++) {

                    var ptStartDate = resultTask[0].objects[k].starts.substring(0, 10);

                    // optimization: skip blank dates 
                    if (ptStartDate === '0000-00-00') {
                        ptStartDate = resultTask[0].objects[k].calculated_starts.substring(0, 10);
                        if (ptStartDate === '0000-00-00') {
                            continue;
                        }
                    }

                    var currentDate = new Date();
                    var starts = new Date(ptStartDate);

                    // do we have a date? 
                    // NSOA.meta.alert('currentdate=' + currentDate + ' starts='+starts);

                    if (starts <= currentDate &&
                        parseInt(resultTask[0].objects[k].percent_complete, 10) < 100) {
                        CSV.pt_id.push(resultTask[0].objects[k].id);
                        CSV.cp_id.push(resultTask[0].objects[k].customerid + ":" +
                            resultTask[0].objects[k].projectid);
                    }
                }
            }
        }
    }

    var retval = "pt_id," + CSV.pt_id.join(",") + "\n";
    retval = retval + "cp_id," + CSV.cp_id.join(",");
    return retval;

}