VLookup Validating 1 Boolean/Toggle field is “yes”

[Teresa Harden]

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

[Alexander Bautz]

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

[Teresa Harden]

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!!

[Author]

Posts