Assignments with vLookup time log: check allocated time before saving child

This example uses an Assignments list and a child list connected with vLookup to log the time used on that assignment. The code is used to ensure users doesn’t log more time than allocated to that assignment.

The example expects the vLookup connection from parent to child to pass the parent _vLookupID value to the _vLookupParentID column in the child.

The time logging in the child item must use a field named LoggedTime, and the parent list must have a field named MaxAllocatedTime that holds the max allocated time for the assignment. If this is not the case in your lists, please search and replace the field names in the code below.

Add this code to your Custom JS in your CHILD form:

function getMaxAllocatedTime() {
    var res = spjs.utility.queryItems({
        "listName": GetUrlKeyValue("parentListGuid"),
        "query": "<Where><Eq><FieldRef Name='_vLookupID' /><Value Type='Text'>" + getFieldValue("_vLookupParentID") + "</Value></Eq></Where>",
        "viewFields": ["ID", "MaxAllocatedTime"]
    });
    var num = 0;
    var max = res.items[0].MaxAllocatedTime;
    if (max.split(";#").length > 1) {
        // if MaxAllocatedTime is a calculated field the value must be split from the string 
        max = max.split(";#")[1];
    }
    if (max !== undefined) {
        num = parseInt(max, 10);
    }
    return num;
}

function getTotalLoggedTime() {
    var res = spjs.utility.queryItems({
        "listName": _spPageContextInfo.pageListId,
        "query": "<Where><Eq><FieldRef Name='_vLookupParentID' /><Value Type='Text'>" + getFieldValue("_vLookupParentID") + "</Value></Eq></Where>",
        "viewFields": ["ID", "LoggedTime"],
        "scope": "RecursiveAll"
    });
    var tot = 0;
    jQuery.each(res.items, function (i, item) {
        tot += item.LoggedTime !== null ? parseInt(item.LoggedTime, 10) : 0;
    });
    return tot;
}

function dffs_PreSaveAction() {
    var max = getMaxAllocatedTime();
    var totLogged = getTotalLoggedTime();
    var logged = getFieldValue("LoggedTime");
    if (totLogged + parseInt(logged, 10) > max) {
        spjs.dffs.alert({
            "title": "To many hours logged",
            "msg": "You have already logged " + totLogged + " hours, and with the current " + logged + " hours you exceed the max allocated time of " + max + " hours.",
            "ok": function () {
                // Close dlg
            }
        });
        // Prevent save
        return false;
    }
    // OK to save
    return true;
}

Please post any questions in the comments below.

Alexander

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.