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

[William Ellis]

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

[Alexander Bautz]

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

[William Ellis]

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!

[Alexander Bautz]

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

Alexander

[Author]

Posts