Show/hide items in dropdown column based on another dropdown

Question

I have two drop down columns A- State B-City.

When Minnesota is selected in A-State, I only want B-City to show the cities in Minnesota.

I know I could do it by creating a MN drop down and using J query if A=MN show MN City Column, but there are a ton of states on here and even more cities.

I have seen some jquery out there but I think I need a dummies version I cant figure out how they are pulling from A to get the B option.

Answer 1

I would create a list called States and add States as items. Then create a list called Cities and have a lookup field (getting Title from States list). After that, add a script editor web part with following code on the form itself than handles Cascade dropdown:

$(document).ready(function() {

    var Dropdownlist = new Array();
    
    Dropdownlist.push({
        rootlistname: "RequestType", //1st (root) list Display name
        leaflistname: "RequestAsset", //2nd (leaf) List name
        leaflookupcolumn: "Title", //Internal field name in leaf List used in lookup
        mainformcolumnleafref: "RequestAsset", //main form (4th list)2nd column Display name
        mainformcolumnrootref: "RequestType", // main form (4th list) 1st column Display name
        defaultddwn: "< Select a Asset Type >"
    });
    
    Dropdownlist.push({
        rootlistname: "RequestAsset", //2nd (root) list Display name
        leaflistname: "AssetNames", //3rd (leaf) List name.
        leaflookupcolumn: "Title", //Internal field name in leaf List used in lookup
        mainformcolumnleafref: "AssetNames", //main form (4 th list) 3rd column Display name
        mainformcolumnrootref: "RequestAsset", // main form (4 th list) 2nd column Display name
        defaultddwn: "< Select a Asset Name >"
    });
    
    
    
    
    
    
    $().methoddropdown(Dropdownlist);
    
    
        
});

$.fn.methoddropdown= function (dropdownvalueArray){var ddwndropdownvalue = new Array();

var mainform = getParameterByName("ID") == null;

$.fn.methoddropdown.Cascade = function(root,ddwnindex)
{
    if (ddwnindex!= null && ddwnindex+1 > ddwndropdownvalue.length)
    {
        return;
    } else if(ddwnindex== null) {
        ddwnindex= $(root).attr("methoddropdownIndex");
    }
    
    var params = ddwndropdownvalue[ddwnindex];
    var rootID = $(root).val();
    if (root == null)
    {   
        rootID = $("select[Title='"+params.rootlistname+"'], select[Title='"+
            params.rootlistname+" Required Field']").val();
    }
    if (rootID == undefined)
    {
        rootID = 0;
    }
    
    var leaf = $("select[Title='"+params.mainformcolumnleafref+"'], select[Title='"+
        params.mainformcolumnleafref+" Required Field']," +
        "select[Title='"+params.mainformcolumnleafref+" possible values']");
    
    var selectedoption = params.selectedoptionue;
    ddwndropdownvalue[ddwnindex].selectedoptionue = 0;
            
    $(leaf).empty();

    var dropdownvalue = "<option value='0'>"+params.defaultddwn+"</option>";

    var varstore = $.ajax({
        url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('"+params.leaflistname+
            "')/items?$select=Id,"+params.leaflookupcolumn+","+params.mainformcolumnrootref+
            "/Id&$expand="+params.mainformcolumnrootref+"/Id&$filter="+params.mainformcolumnrootref+
            "/Id eq "+ rootID+"&$orderby=" + params.leaflookupcolumn,
        type: "GET",
        dataType: "json",
        headers: {
            Accept: "application/json;odata=verbose"
        }
   
    });
    varstore.done(function (data,textStatus, jqXHR){
        for (index in data.d.results)
        {
            dropdownvalue += "<option value='"+ data.d.results[index].Id +"'>"+
                data.d.results[index][params.leaflookupcolumn]+"</option>";
        }
        $(leaf).append(dropdownvalue);
        if(!mainform)$(leaf).val(selectedoption);
        $().methoddropdown.Cascade(null,Number(ddwnindex)+1);
    });
    varstore.fail(function (jqXHR,textStatus,errorThrown){
        alert("Error retrieving information from list: " + params.leaflistname + jqXHR.responseText);
        $(leaf).append(dropdownvalue);
    });
}

for (index in dropdownvalueArray)
{
    var thisCascade = dropdownvalueArray[index];
    
    if(thisCascade.rootlistname != null)
    {
        var root = $("select[Title='"+thisCascade.rootlistname+"'], select[Title='"+
            thisCascade.rootlistname+" Required Field']");
        
        $(root).attr("methoddropdownIndex",index);
        
        $(root).change(function(){
            $().methoddropdown.Cascade(this,null);        
        });            
    } 
    thisCascade.selectedoptionue = $("select[Title='"+thisCascade.mainformcolumnleafref+"'], select[Title='"+
        thisCascade.mainformcolumnleafref+" Required Field']," +
        "select[Title='"+thisCascade.mainformcolumnleafref+" possible values']").val();
        
    ddwndropdownvalue.push(thisCascade);
}

$().methoddropdown.Cascade(null,0);        


function getParameterByName(key) {
        key = key.replace(/[*+?^$.\[\]{}()|\\\/]/g, "\\$&"); // escape RegEx meta chars
        var match = location.search.match(new RegExp("[?&]"+key+"=([^&]+)(&|$)"));
        return match && decodeURIComponent(match[1].replace(/\+/g, " "));
    }

}

Please refer https://www.c-sharpcorner.com/article/three-level-cascading-dropdown-sharepoint-list-by-using-javascript-and-script-ed4/ The hyperlink also has the source code that you can download.

Answer 2

If you are using sharepoint online modern experience, we suggest you use PowerApps to implement cascading dropdowns in sharepoint custom forms:

https://www.techmikael.com/2019/01/a-no-nonsense-solution-to-implement.html