VLOOKUP

Forums General discussion VLOOKUP

Viewing 24 reply threads
  • Author
    Posts
    • #29493
      Silvestre Kassoka
      Participant

      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.

    • #29525
      Alexander Bautz
      Keymaster

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

      Alexander

    • #29534
      Silvestre Kassoka
      Participant

      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.

    • #29541
      Alexander Bautz
      Keymaster

      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

    • #29546
      Silvestre Kassoka
      Participant

      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.

    • #29552
      Alexander Bautz
      Keymaster

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

      Alexander

    • #29554
      Silvestre Kassoka
      Participant

      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:
    • #29557
      Alexander Bautz
      Keymaster

      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

    • #29567
      Silvestre Kassoka
      Participant

      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”.

    • #29578
      Alexander Bautz
      Keymaster

      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 7 months, 1 week ago by Alexander Bautz. Reason: Explained F12
    • #29581
      Silvestre Kassoka
      Participant

      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.”
      });

    • #29586
      Alexander Bautz
      Keymaster

      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

    • #29588
      Silvestre Kassoka
      Participant

      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

    • #29591
      Alexander Bautz
      Keymaster

      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

    • #29611
      Silvestre Kassoka
      Participant

      Alex,

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

      Regards

    • #29613
      Silvestre Kassoka
      Participant

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

    • #29617
      Alexander Bautz
      Keymaster

      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

    • #29621
      Silvestre Kassoka
      Participant

      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;
      }

    • #29640
      Alexander Bautz
      Keymaster

      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 7 months ago by Alexander Bautz. Reason: Changed code snippet to support empty from or to dates
    • #29735
      Silvestre Kassoka
      Participant

      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

    • #29752
      Alexander Bautz
      Keymaster

      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 7 months ago by Alexander Bautz. Reason: Fixed typo in function name countDaysBetween (lower case b)
    • #29762
      Silvestre Kassoka
      Participant

      Hi Alex,

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

      Reference error:CountDaysBetween not defined.

    • #29766
      Alexander Bautz
      Keymaster

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

      Alexander

    • #29768
      Silvestre Kassoka
      Participant

      Hi Alex,

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

    • #29774
      Alexander Bautz
      Keymaster

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

      Alexander

Viewing 24 reply threads
  • You must be logged in to reply to this topic.