VLookup Validating 1 Boolean/Toggle field is “yes”

Forums vLooup for SharePoint VLookup Validating 1 Boolean/Toggle field is “yes”

Viewing 3 reply threads
  • Author
    Posts
    • #31801
      Teresa Harden
      Participant

      Hi, I’m new and have created my parent & child form to do 99.9% of what I want. I couldn’t find this anywhere, so it is my reason for posting.

      I have a “completed” field (boolean Yes/No) on my child. It is not required, because other fields on the child can be edited after new creation.

      I want to require in the parent, that you cannot ‘close’ the parent form, if any of the child vlookup “tasks” (rows) have that “completed” boolean field set to NO.

      I will never have a condition in my form, that 1 child task record can be “completed” = no, and I would want the parent form “closed”.

      Any guidance will be super helpful! I can do it as a rule, or even Designer workflow – I just can’t figure it out. THANK YOU

    • #31805
      Alexander Bautz
      Keymaster

      Hi,
      The best method for checking this is to use some custom js that runs a separate query to the child list and not just looking at the vLookup data object (because this requires the child table to be rendered in the parent form before it can be checked).

      Try this code in your parent form custom js (please note that it is written freehand without testing):

      function dffs_PreSaveAction(){
          // Check if parent form is closing
          var isClosed = getFieldValue("Closed"); // Ensure the field internal name Closed is correct - this code expects the field to be a yes/no checkbox (boolean)
          if(isClosed){
              var res = spjs.utility.queryItems({
                  "listName":"The_List_Display_Name_or_GUID",
                  "query":"<Where><Eq><FieldRef Name='Title' /><Value Type='Text'>TEST 123</Value></Eq></Where>", // Change this to match the CAML query in your vLookup settings
                  "viewFields": ["ID","completed"] // ensure the internal name "completed" is correct
              });
              if(res.count > 0){
                  var allCompleted = true;
                  jQuery.each(res.items, function(i, item){
                      // ensure the internal name "completed" is correct
                      if(item.completed !== "1"){
                          allCompleted = false;
                      }
                  });
                  if(!allCompleted){
                      spjs.dffs.alert({
                          "title": "Not able to close",
                          "msg": "You cannot close this form before all child items are completed."
                      });
                  }else{
                      // OK to save
                      return true;
                  }
              }else{
                  // Does not have any child items - return true to save item
                  return true;
              }
          }else{
              // OK to save
              return true;
          }
      }

      Read through the code and correct as needed.

      Alexander

      • #31820
        Teresa Harden
        Participant

        Alexander, thank you so much! With a few tweaks (adding the parent ID, so it only returns the vlookup records associated with ParentID) it works beautifully! THANK YOU!!

    • #33501
      William Ellis
      Participant

      I am attempting a similar solution, except that I am not using Boolean, I am checking Choice fields, so I made some changes. Each Initiative has several Assignments as children. If any children have Status != ‘Completed’, then I want the alert to show and not allow them to save the Initiative Status on the parent record as ‘Completed’. I have placed this in my parent Edit form CustomJS tab, but nothing happens when I save and have the Initiative Status set to ‘Completed’. I’m sure thee is something wrong with my script but I cannot see what that might be.

      function dffs_PreSaveAction(){
      // Check if parent form is closing
      var initStatus = getFieldValue(“InitiativeStatus”); // Get Initiative Status value
      if(initStatus=”Completed”){
      var res = spjs.utility.queryItems({
      “listName”:”Assignments”,
      “query”:”<Where><Eq><FieldRef Name=’_vLookupParentID’ /><Value Type=’Text’>[currentItem:_vLookupID]</Value></Eq></Where>”, // Lookup parent ID
      “viewFields”: [“ID”,”Status”] // return ID and Status of assignment records
      });

      if(res.count > 0){
      var allStatus = true;
      jQuery.each(res.items, function(i, item){
      if(item.Status != “Completed”){
      allStatus = false;
      }
      });
      if(!allStatus){
      spjs.dffs.alert({
      “title”: “Not able to close”,
      “msg”: “You cannot set the Initiative Status to Completed until all Assignments are Complete.”
      });
      }else{
      // OK to save
      return true;
      }
      }else{
      // Does not have any child items – return true to save item
      return true;
      }
      }else{
      // OK to save
      return true;
      }

      • #33506
        Alexander Bautz
        Keymaster

        In your CAML query you use [currentItem:_vLookupID] to get the value from the _vLookupID field:

        "<Where><Eq><FieldRef Name='_vLookupParentID' /><Value Type='Text'>[currentItem:_vLookupID]</Value></Eq></Where>"

        This only works in the vLookup config – when used in Custom JS you must use getFieldValue like this:

        "<Where><Eq><FieldRef Name='_vLookupParentID' /><Value Type='Text'>"+getFieldValue("_vLookupID")+"</Value></Eq></Where>"

        Alexander

    • #33508
      William Ellis
      Participant

      Thank you! Got it working. Also had to set the initial field comparison to == rather than =. Slowly getting the hang of this, thank you for all of your assistance!

      • #33513
        Alexander Bautz
        Keymaster

        I’m glad it worked – I didn’t notice the “=” issue so I’m glad you figured it out.

        Alexander

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