VLOOKUP

[Silvestre Kassoka]

Hi Alex,

Background
We have two lists connected through Vlookup , List A and List B. Basically List A is a leave request form and List B keeps records leave days taken and remainder etc.(print screens annexed).

Objective

a)To display 2 columns on New Form from List B which are connected to list A through
Vlookup. Remainder & Leave days to be taken

b) To prevent user from saving the forms if the remainder of leave days is not enough. I tried to create a Javascript code that will basically make calculations based on two columns from list B (leave taken & remainder) and one column from List A (days taken). Basically to say if (leave taken + days taken > remainder ) then an alert is made “no enough leave days etc and the form is not saved (submitted. However this giving an error, that bring me to another question.
It is possible to get Field Values from columns of another list connected through Vlookup.? through getFieldValue?

I hope i was clear.

Attachments:

1. 

   List-A.png
   

2. 

   JS.png
   

3. 

   PreventSave_Rule.png
   

4. 

   Vlookup_List-A.png
   

[Alexander Bautz]

Hi,
Look at this thread and see if you can use the method explained here as a starting point.

Alexander

[Silvestre Kassoka]

Hi Alex,

Thanks a lot, sure his request is pretty much similar to mine and it sure was good start.The only difference is that is my case is the number of days taken is a calculated field based on start & end date.

However, in my case the following is happening,
a). The rule that calls the JS is kicking well but nothing is happening.I console.logged the res variable(log attached) it appears its not querying that Employees list.

I am also thinking its because my number of days requested <strong/> value is a calculated column ,probably when the JS kicks that field will still not a value.I am not sure.

On a side note, I tried to use the {{var:balance}} to display balance of hours but to know success.print attached.

Thank you in advance.

Attachments:

1. 

   JS-1.png
   

2. 

   Employees-List.png
   

3. 

   log.png
   

4. 

   balance.png
   

[Alexander Bautz]

When you use console.log you must expand the “>Object” shown in the console to see the contents of it.

Also, I highly recommend that you use another browser – like Google Chrome or the new Microsoft Edge (running on Chromium engine – same as Google Chrome) – this gives you better debugging tools.

Alexander

[Silvestre Kassoka]

Hi Alex,

Thanks for the heads up.well. i spend the whole day cracking on this but cannot seem to get the hang of it.

From the logs, i can tell that the query is working fine which is is the first part of the code.

(function () {
var res = spjs.utility.queryItems({
“listName”: “Total de Ferias”,
“query”: “<Where><Eq><FieldRef Name=’Person’ LookupId=’TRUE’ /><Value Type=’Integer’>” + _spPageContextInfo.userId + “</Value></Eq></Where>”,
“viewFields”: [“ID”, “Ferias_x0020_para_x0020_Gozar”]

However , the portion below is getting an undefined.
if (res.count > 0) {
var item = res.items[0];
balance = Number(item.Ferias_x0020_para_x0020_Gozar);
}
console.log(item);

The other log errors i cannot interpret i just attached them.

Attachments:

1. 

   general.png
   

2. 

   log1.png
   

[Alexander Bautz]

You must expand the object by clicking here – it will show you if your query it right or wrong:

Alexander

[Silvestre Kassoka]

Hi Alex,

From object i can tell the query portion of the script is fine. It’s the other parts of the code that seem not to be working. find attached.

Attachments:

1. 

   object.png
   

[Alexander Bautz]

Yes, as you can see the value is returned like this:

float;#10.0000000000

You must change your code like this:

if (res.count > 0) {
var item = res.items[0];
balance = Number(item.Ferias_x0020_para_x0020_Gozar.split(";#")[1]);
}

Alexander

[Silvestre Kassoka]

Hi Alex,

Thank you so much the query came out right.How even the {{var:balance}} is now displaying right.

However, this portion of
jQuery(“#dffs_dias input”).on(“change”, function () {
dias = Number(this.value);
if (dias > balance) {
Even logging nothing it nothing is being returned. my field internal name is “dias”.

[Alexander Bautz]

It’s hard to tell without looking at it, but if you insert a console.log here you can see what the values of your dial and balance variables are:

dias = Number(this.value);
console.log(dias);
console.log(balance);
if (dias > balance) {

Please note that the output is shown in the developer console (hit F12 > Console).

Alexander

• This reply was modified 3 years, 12 months ago by Alexander Bautz. Reason: Explained F12

[Silvestre Kassoka]

Hi Alex,

You are right! Apparently after the jQuery nothing is logged , however they are some errors that come up . I have attached for your reference

jQuery(“#dffs_dias”).on(“change”, function () {
dias = Number(this.value);
console.log(dias);
console.log(balance);
if (dias > balance) {
spjs.dffs.toggleSaveBtnByRule(false);
spjs.dffs.alert({
“title”: “To many hours requested”,
“msg”: “You only have ” + balance + ” hours available.”
});

Attachments:

1. 

   JQ.png
   

2. 

   JQ1.png
   

3. 

   jq2.png
   

[Alexander Bautz]

Ah, sorry I didn’t see this before – you are now actually triggering on change on the table row and not on the input – change your first line like this:

jQuery("#dffs_dias input").on("change", function () {

Alexander

[Silvestre Kassoka]

No Alex, I am triggering on the change of calculated field. The calculated field is
based on end date-start date which is the dias column.

Regards

[Alexander Bautz]

I don’t understand – is it a proper SharePoint calculated field or a javascript calculation in DFFS / Custom JS?

If dias is a text or number field in your form, this line will select the table row:

jQuery("#dffs_dias").on("change"...

if you want to trigger this when the input field is changed you must change it to:

jQuery("#dffs_dias input").on("change"...

Alexander

[Silvestre Kassoka]

Alex,

I am afraid not with that as well.I continue to get the same errors i posted earlier.

Regards

[Silvestre Kassoka]

Sorry forgot to add that its a normal Sharepoint calculated list.

[Alexander Bautz]

OK, a SharePoint calculated field cannot be used like this. The field only exist in DispForm and there is no way you can attach any change event to this field.

If you explain what you try to do, I’ll see if I can help you get it set up right.

Alexander

[Silvestre Kassoka]

Thanks ,

Here goes, basically I have two custom lists which are Employees and Leave Request form.
The employees list a the leave balances of the employees and the leave request is used for submitting the form. When submitting the form the number of days taken is calculated(sharepoint calculation) by end date-start date..The calculated column has a field internal name dias.

If the the days taken (dias) in greater than the balance then from should hide save and give notice you only have x days available.
In a nutshell this what we are trying to achieve it .I think the only problem is the calculated column.

v var balance = 0;
var dias = 0;
(function () {
var res = spjs.utility.queryItems({
“listName”: “Total de Ferias”,
“query”: “<Where><Eq><FieldRef Name=’Person’ LookupId=’TRUE’ /><Value Type=’Integer’>” + _spPageContextInfo.userId + “</Value></Eq></Where>”,
“viewFields”: [“ID”, “Ferias_x0020_para_x0020_Gozar”]

});

if(res.count > 0); {
var item = res.items[0];
balance = Number(item.Ferias_x0020_para_x0020_Gozar.split(“;#”)[1]);
}

})();

jQuery(“#dffs_dias input”).on(“change”, function () {
dias = Number(this.value);
console.log(dias);
console.log(balance);
if (dias > 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 (dias > balance) {
spjs.dffs.alert({
“title”: “Unable to save request”,
“msg”: “You only have ” + balance + ” hours available.”
});
return false;
}
return true;
}

[Alexander Bautz]

Yes, the problem is the calculated column as this is not a field you can interact with in NewForm or EditForm – it is only calculated server side after you have saved the item.

You need to use a function to calculate the difference from the date fields in your form directly. I have included a function below that should do this for you.

Let me know if you have any questions.

Alexander

Calculate number of days between two dates in a SharePoint form (requires DFFS to be loaded in the form):

function countDaysBetween(from, to){
    var r =  {"businessDays": 0, "weekendDays": 0};
    var f = spjs.utility.getDateFieldAsDateObject(from);
    var t = spjs.utility.getDateFieldAsDateObject(to);
    if(f === "" || t === ""){
        // from or to date is empty
        return r;
    }
    // Set time to 12:00 to ease calculation
    f.setHours(12,0,0);
    t.setHours(12,0,0);    
    while(f <= t){
        var d = f.getDay();
        if(d > 0 && d < 6){ // 1 = Monday and 5 = Friday
            r.businessDays += 1;
        }else{
            r.weekendDays += 1;
        }
        f.setDate(f.getDate() + 1);
    }
    return r;
}

// Call it like this with from and to date fields as arguments
var diff = countDaysBetween("DateColumn1", "DateColumn2");
var businessDays = diff.businessDays;
var weekendDays = diff.weekendDays;

• This reply was modified 3 years, 11 months ago by Alexander Bautz. Reason: Changed code snippet to support empty from or to dates

[Silvestre Kassoka]

Hi Alex,

Thanks a lot for the code but i continue to have some challenges.I will post the code first and explain.

// To change date format since my forms are in Portuguese.
spjs.dffs.data.lcidToDateFormat[“2070”] = “d/m/y”;
function countDaysbetween(_x0066_ku0,_x0076_yt6){
var r = {“businessDays”: 0, “weekendDays”: 0};
var f = spjs.utility.getDateFieldAsDateObject(“_x0076_yt6”);
var t = spjs.utility.getDateFieldAsDateObject(“_x0066_ku0”);
console.log(t)
console.log(f)
if(f === “” || t === “”){
// from or to date is empty
return r;
}
// Set time to 12:00 to ease calcuation
f.setHours(12,0,0);
t.setHours(12,0,0);
while(f <= t){
var d = f.getDay();
if(d > 0 && d < 6){ // 1 = Monday and 5 = Friday
r.businessDays += 1;
}else{
r.weekendDays += 1;
}
f.setDate(f.getDate() + 1);
console.log(r)
}
return r;
}

// Call it like this with from and to date fields as arguments
var diff = countDaysbetween(“_x0066_ku0″,”_x0076_yt6”);
var businessDays = diff.businessDays;
var weekendDays = diff.weekendDays;
console.log(diff)
var balance = 0;
var dias = 0;
(function () {
var res = spjs.utility.queryItems({
“listName”: “Total de Ferias”,
“query”: “<Where><Eq><FieldRef Name=’Person’ LookupId=’TRUE’ /><Value Type=’Integer’>” + _spPageContextInfo.userId + “</Value></Eq></Where>”,
“viewFields”: [“ID”, “Ferias_x0020_para_x0020_Gozar”]

});

if(res.count > 0); {
var item = res.items[0];
balance = Number(item.Ferias_x0020_para_x0020_Gozar.split(“;#”)[1]);
}

})();

function calculation () {
console.log(businessDays);
console.log(balance);
if (diff > 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 (diff > balance) {
spjs.dffs.alert({
“title”: “Unable to save request”,
“msg”: “You only have ” + balance + ” hours available.”
});
return false;
}
return true;
}

Challenges

a) Console.log (t)
b) console.log (f) cannot return value
*print provided.
c) When i call the function name ( countDaysbetween(_x0066_ku0,_x0076_yt6) in Rules i get error print provided.

d) I am also struggling with using the r returned value in the calculation functions to achieve the intended.

Thank you

Attachments:

1. 

   CountDays.png
   

2. 

   console2.png
   

3. 

   Console.png
   

[Alexander Bautz]

You are not supposed to change the function countDaysBetween. I have cleaned up your code a bit below.

Also you are not supposed to call the countDaysBetween function, but your calculation function from the rule.

Let me know how this works out.

Alexander

// To change date format since my forms are in Portuguese.
spjs.dffs.data.lcidToDateFormat["2070"] = "d/m/y";
function countDaysBetween(from, to) {
    var r = { "businessDays": 0, "weekendDays": 0 };
    var f = spjs.utility.getDateFieldAsDateObject(from);
    var t = spjs.utility.getDateFieldAsDateObject(to);
    if (f === "" || t === "") {
        // from or to date is empty
        return r;
    }
    // Set time to 12:00 to ease calculation
    f.setHours(12, 0, 0);
    t.setHours(12, 0, 0);
    while (f <= t) {
        var d = f.getDay();
        if (d > 0 && d < 6) { // 1 = Monday and 5 = Friday
            r.businessDays += 1;
        } else {
            r.weekendDays += 1;
        }
        f.setDate(f.getDate() + 1);
    }
    return r;
}

// Define these outside the function to use them globally
var balance = 0;
var businessDays = 0;
(function () {
    var res = spjs.utility.queryItems({
        "listName": "Total de Ferias",
        "query": "<Where><Eq><FieldRef Name='Person' LookupId='TRUE' /><Value Type='Integer'>" + _spPageContextInfo.userId + "</Value></Eq></Where>",
        "viewFields": ["ID", "Ferias_x0020_para_x0020_Gozar"]
    });
    if (res.count > 0); {
        var item = res.items[0];
        balance = Number(item.Ferias_x0020_para_x0020_Gozar.split(";#")[1]);
    }
})();

function calculation() {
    var diff = countDaysBetween("_x0066_ku0", "_x0076_yt6");
    businessDays = diff.businessDays;
    if (businessDays > balance) {
        spjs.dffs.toggleSaveBtnByRule(false);
        spjs.dffs.alert({
            "title": "To many days requested",
            "msg": "You only have " + balance + " days available."
        });

    } else {
        spjs.dffs.toggleSaveBtnByRule(true);
    }
}

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

• This reply was modified 3 years, 11 months ago by Alexander Bautz. Reason: Fixed typo in function name countDaysBetween (lower case b)

[Silvestre Kassoka]

Hi Alex,

Thanks for your help as usual. However calling the function i get an error

Reference error:CountDaysBetween not defined.

Attachments:

1. 

   Claculation-function.png
   

[Alexander Bautz]

I have fixed the code snippet – I had a lower case b in the countDaysBetween function name in the calculation function.

Alexander

[Silvestre Kassoka]

Hi Alex,

Thanks a billion!!!. It working like a Charm.!!!

[Alexander Bautz]

Thanks for the feedback – I’m glad it worked out.

Alexander

[Author]

Posts