All posts by Alexander Bautz

Keep track of assigned and unassigned keys for office locations

I got a request for a solution to solve the following question:

I break down the columns in the two lists below. What I’m trying to accomplish is to get the “key designation” field in the key log list to drive the number of assigned, unassigned and total keys in the inventory. For example, if “assign” is chosen in the key designation field of the key log, it automatically increases the number of keys assigned by one key, while decreasing the number of keys unassigned. If “return” is selected, it automatically decreases the number of assigned keys and increases the number of unassigned keys. The inventory holds information detailing keys across two different locations/offices. The columns in the inventory are:

  • Site (Boston, New York, etc.) 
  • Protected Area (the area to which the keys provide access)
  • Identifier (corresponding symbol on the key that ties that key to the protected area [identifier x = broom closet protected area for example])
  • Number of Keys Assigned (5)
  • Number of Keys Unassigned (4)
  • Total Keys (sum of the number of assigned and unassigned keys [9])

The Key Log tracks when a key is assigned, returned, added or deleted and who assigned the key (with 2 person or group fields to account for dual control). The columns in the log are:

  • Site
  • Protected Area
  • Identifier
  • Key number
  • key designation (if we’re assigning, returning, adding or deleting a key)
  • person or group #1 (to show who assigned the key)
  • person or group #2 (to show dual control for 2nd person assigning the key)

I have created two lists with the essential fields to make the relationship like this:

inventory list

The TotKeys is a calculated column that sums the NumberAssigned and NumberUnassigned.

keylog list

The KeyDesignation field has these choices:

assigning
returning
adding
deleting

Now install DFFS in the keylog list and set up a cascading dropdown like this:

Cascading dropdown config

Then add this in your Custom JS of both NewForm and EditForm in the keylog list:

When adding or editing a keylog item the corresponding item in the inventory will be updated with the correct count.

// Reset KeyDesignation field on load
setFieldValue("KeyDesignation", "");

function dffs_PreSaveAction() {
    var site = getFieldValue("Site");
    var protectedArea = getFieldValue("ProtectedArea");
    var inventoryItem = spjs.utility.queryItems({
        "listName": "inventory",
        "query": "<Where><And><Eq><FieldRef Name='Site' /><Value Type='Text'>" + site + "</Value></Eq><Eq><FieldRef Name='ProtectedArea' /><Value Type='Text'>" + protectedArea + "</Value></Eq></And></Where>",
        "viewFields": ["ID", "NumberAssigned", "NumberUnassigned"]
    });
    var pass = true;
    if (inventoryItem.count > 0) {
        var item = inventoryItem.items[0];
        var assigned = item.NumberAssigned !== null ? parseInt(item.NumberAssigned, 10) : 0;
        var unassigned = item.NumberUnassigned !== null ? parseInt(item.NumberUnassigned, 10) : 0;
        var keyDesignation = getFieldValue("KeyDesignation");
        switch (keyDesignation) {
            case "assigning":
                if (unassigned > 0) {
                    assigned += 1;
                    unassigned -= 1;
                } else {
                    pass = false;
                    spjs.dffs.alert({
                        "title": "No keys available",
                        "msg": "You are trying to assign a key, but no unassigned keys are available.",
                        "ok": function() {
                            // Close dlg
                        }
                    });
                }
                break;
            case "returning":
                if (assigned > 0) {
                    assigned -= 1;
                    unassigned += 1;
                } else {
                    pass = false;
                    spjs.dffs.alert({
                        "title": "No keys assigned",
                        "msg": "You are trying to unassign a key, but no keys are assigned.",
                        "ok": function() {
                            // Close dlg
                        }
                    });
                }
                break;
            case "adding":
                unassigned += 1;
                break;
            case "deleting":
                unassigned -= 1;
                break;
        }
        // Update list item with new count
        spjs.utility.updateItem({
            "listName": "inventory",
            "id": item.ID,
            "data": {
                "NumberAssigned": assigned,
                "NumberUnassigned": unassigned
            }
        });
    }
    if (pass) {
        return true;
    } else {
        return false;
    }
}
keylog list NewForm

Let me know in the comments below if you have any questions.

Alexander

Create a folder in a custom list with REST

Here is a snippet that helps create a folder in a custom list (not a document library) using the SharePoint REST API (and jQuery).

This is the main function – no need to change anything in this one:

function createFolderInList(arg) {
    var deferred = jQuery.Deferred();
    var folder = {
        "__metadata": { "type": "SP.ListItem" },
        "ContentTypeId": "0x0120",
        "Title": arg.folderName
    };
    var useGUID = arg.listId.charAt(0) === "{";
    jQuery.ajax({
        "url": _spPageContextInfo.webServerRelativeUrl + "/_api/web/lists/" + (useGUID ? "getById('" + arg.listId + "')" : "getByTitle('" + arg.listId + "')") + "/items",
        "type": "POST",
        "contentType": "application/json;odata=verbose",
        "data": JSON.stringify(folder),
        "headers": {
            "Accept": "application/json;odata=verbose",
            "X-RequestDigest": jQuery("#__REQUESTDIGEST").val()
        },
        "success": function (data) {
            // Successfully created folder, but needs to update FileLeafRef
            var updData = { "__metadata": { "type": data.d.__metadata.type }, "Title": arg.folderName.split("/").pop(), "FileLeafRef": arg.folderName };
            jQuery.ajax({
                "url": _spPageContextInfo.webServerRelativeUrl + "/_api/web/lists/" + (useGUID ? "getById('" + arg.listId + "')" : "getByTitle('" + arg.listId + "')") + "/items(" + data.d.Id + ")",
                "type": "POST",
                "data": JSON.stringify(updData),
                "headers": {
                    "IF-MATCH": "*",
                    "X-HTTP-Method": "MERGE",
                    "accept": "application/json;odata=verbose",
                    "content-type": "application/json;odata=verbose",
                    "X-RequestDigest": jQuery("#__REQUESTDIGEST").val(),
                },
                "success": function () {
                    // Done creating and renaming folder
                    deferred.resolve();
                },
                "error": function (err) {
                    deferred.reject(err);
                }
            });
        },
        "error": function (err) {
            deferred.reject(err);
        }
    });
    return deferred.promise();
}

This is how you call it from your code:

createFolderInList({
    "folderName": "Created from code",
    "listId": "MyTestList", // List GUID or display name - if you use GUID it must include the curly braces around the GUID like this: {c5c44b98-34f1-4ade-87d3-ed292eee0d84}
}).done(function () {
    alert("Folder created");
}).fail(function (err) {
    alert("Failed to create folder:\n" + JSON.stringify(err));
});

You can create a subfolder by specifying the folderName like this:

"folderName": "Created from code/subfolder 1"

Alexander

Add a note to self textarea in a DFFS form

I got a request:

Hi Alexander,
I hope you are doing well. I’m wondering if you’ve done anything like this before: a field or area on an item where someone can add their own notes not visible to anyone except the logged in user. I thought maybe a rule but what if one person has their notes and someone else has their own? Thanks in advance for any advice on how you’d handle it.

This can be done with a few lines of Custom JS as shown below. This method stores the text in localStorage in the browser. Please note that this requires the localStorage to be activated (it is by default, but it can be turned off).

You must also add a field named _DFFSID to your form to hold the automatically generated unique itemID used to identify the values.

Add this to a table row added in your DFFS tab:

<textarea id="dffs_note_to_self" style="height:75px;width:100%;box-sizing:border-box;" onkeyup="saveToLocalStorage(this)"></textarea>

Then add this to your Custom JS:

function saveToLocalStorage(elm){
    var val = jQuery(elm).val();
    var id = getFieldValue("_DFFSID");
    localStorage.setItem(id,escape(val));
}

function dffs_ready(){
    var id = getFieldValue("_DFFSID");
    var noteToSelf = localStorage.getItem(id);
    if(noteToSelf !== null){
        jQuery("#dffs_note_to_self").val(unescape(noteToSelf));
    }
}

You can add this to NewForm, DispForm and EditForm and the text you enter will show every time you open it – like this:

The text is automatically saved when you type the value in the textarea, but because it uses the localStorage in the browser to store the text it is not saved in the list item and is only available if you open the item in the same browser on the same computer as you used to type the text in.

Let me know in the comments how this works out.

Best regards,
Alexander