VLookup Validating 1 Boolean/Toggle field is “yes”

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

Viewing 1 reply thread
  • 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!!

Viewing 1 reply thread
  • You must be logged in to reply to this topic.