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:
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;
}
}
Let me know in the comments below if you have any questions.
Alexander