vLookup, currentUser and rules

[Maciek Grischke]

This is my first attempt to implement vLookup, but I’m having difficulties understanding the plugin.

Here’s what I want to do:

In NewForm, check the current user. Pull value (text) from another list, specific to the current user. Then apply a rule based on the result, i.e., hide all fields if the value is (or contains) “No” or show all fields if the value is (or contains) “Yes” etc.

I will be using “enforce unique values” in the first list so only one result is returned.

I’d appreciate it if someone could guide me on how to start this.

Many thanks!

• This topic was modified 5 years ago by Maciek Grischke.

[Alexander Bautz]

I think you might have misunderstood the use of the vLookup plugin. It is used to pull data from another list and display it in a table view in the form. You cannot use the result in a rule as a trigger.

What you are trying to do is possible using some Custom JS to do the query and then call a rule for “Yes” and another for “No” when the query has finished.

If you can give some more details about the query (field names etc.) I’ll try to give you a code snippet to do it.

Alexander

[Maciek Grischke]

Hi Alexander,

here’s what I want to do.

There are two lists:
– Employees and
– Holiday Requests.

The Employees list contains their AL entitlement, balance in hours.

When the employee requests a holiday via Holiday Request list, I want the NewForm to display their AL balance pulled from the Employees list.

So, if Tony opens a NewForm in Holiday Requests list, I would like the form to locate this employee via their name in the Employees list, pull the AL allowance information and display it in the NewForm. Next, if the AL balance is less than their request, i.e., Tony is requesting to take 50 hours of holiday, but his balance is only 45 hours, in this case, I want the rule to kick in and display a warning message and hide Save button etc.

At the moment the form allows them to submit their holiday request and I have a Flow to check their AL balance and if their balance is not enough, the Flow will reject their request and send them an email. The Flow locates their record in the Employees list via their name. There are two name columns in the Employees list: User field and Text field. In Flow, I use the “Get Items” action with OData filter and OData filter doesn’t work with user fields, hence I also use a text field to store their Display Name.

Hope this makes sense.

[Alexander Bautz]

Here is a quick example of how you can achieve this. Add this to your Custom JS in the Holiday Request list:

var balance = 0;
var requestedHours = 0;
(function () {
    var res = spjs.utility.queryItems({
        "listName": "Employees",
        "query": "<Where><Eq><FieldRef Name='Employee' LookupId='TRUE' /><Value Type='Integer'>" + _spPageContextInfo.userId + "</Value></Eq></Where>",
        "viewFields": ["ID", "ALBalance"]
    });
    if (res.count > 0) {
        var item = res.items[0];
        balance = Number(item.ALBalance);
    }
})();

jQuery("#dffs_RequestedHours input").on("change", function () {
    requestedHours = Number(this.value);
    if (requestedHours > balance) {
        spjs.dffs.toggleSaveBtnByRule(false);
        spjs.dffs.alert({
            "title": "To many hours requested",
            "msg": "You only have " + balance + " hours available."
        });
    }else{
        spjs.dffs.toggleSaveBtnByRule(true);
    }
});

function dffs_PreSaveAction() {
    if (requestedHours > balance) {
        spjs.dffs.alert({
            "title": "Unable to save request",
            "msg": "You only have " + balance + " hours available."
        });
        return false;
    }
    return true;
}

Change the list name Employees to match your list name, the field Employee to match the people picker field in this list and the field ALBalance to match the field where the balance (number of hours left) is stored. Then change RequestedHours to match the field in the Holiday Request list where the user types in the requested hours.

If you like you can add a table row to your tab and use this in the “Row body” to show the balance:

{{var:balance}}

Let me know how this works out.

Alexander

[Author]

Posts