Dynamic drop down Sharepoint online

Question

I am trying to create a data capture using a Sharepoint list and I've run into an issue.

I'd like users to be able to select high level queries, then based on those high level queries in an other drop down select the low level query.

For example: <high level query = "System Issues" Low Level = "page note loading", "framework issues", "telephony connection" etc... >

<high level query = "Customer Query" Low Level = "Policy enquiry", "chase request", "order update" etc... >

So in the example above if I select "System issues" I'll be given the low level options for system issues ONLY. and if I select "Customer query", I'll be given low level options for "customer Query" only.

This seems like basic functionality that I'd expect a lookup column to be able to do but they can't unless I use a separate drop down for each.

I have also tried using calculated columns, but you can't combine the two.

I'm using Sharepoint online with a modern site design.

Any help would be MASSIVELY appreciated.

Answer 1

You could create the cascading dropdown list to realize your design

Please follow steps:

Step1: Create the first list called "High"

Step2: Create the second list called "Low" and add a Lookup column named "High"

Step3: Create the third list called "Cascading" and add two Lookup columns named "High" and "Low"

Step4: Go to the "Cascading" list and switch to classic mode. Click on "Default New Form" under the "LIST" tab.

Step5: Click on "Edit page" >> Add a Web Part >> Script Editor ( Media and Content )

Step6: Click "EDIT SNIPPET" link and paste the below script

<script src="//code.jquery.com/jquery-1.10.1.min.js"></script>

<script type="text/javascript">
    $(document).ready(function() {
    
        HillbillyCascade({
            parentFormField: "High", //Display name on form of field from parent list
            childList: "Low", //List name of child list
            childLookupField: "Title", //Internal field name in Child List used in lookup
            childFormField: "Low", //Display name on form of the child field
            parentFieldInChildList: "High" //Internal field name in Child List of the parent field
        });
    
    });
    
    function HillbillyCascade(params)
    {

        var parent = $("select[Title='"+params.parentFormField+"'], select[Title='"+
            params.parentFormField+" Required Field']");
        
        $(parent).change(function(){
            DoHillbillyCascade(this.value,params);        
        });
        
        var currentParent = $(parent).val();
        if (currentParent != 0)        
        {
            DoHillbillyCascade(currentParent,params);
        }
        
    }
        

    function DoHillbillyCascade(parentID,params)
    {
    
        var child = $("select[Title='"+params.childFormField+"'], select[Title='"+
            params.childFormField+" Required Field']," +
           "select[Title='"+params.childFormField+" possible values']");
        
        $(child).empty();
    
        var options = "";

        var call = $.ajax({
            url: _spPageContextInfo.webAbsoluteUrl + "/_api/Web/Lists/GetByTitle('"+params.childList+
                "')/items?$select=Id,"+params.childLookupField+","+params.parentFieldInChildList+
                "/Id&$expand="+params.parentFieldInChildList+"/Id&$filter="+params.parentFieldInChildList+
                "/Id eq "+ parentID,
            type: "GET",
            dataType: "json",
            headers: {
                Accept: "application/json;odata=verbose"
            }
       
        });
        call.done(function (data,textStatus, jqXHR){
        
            for (index in data.d.results)
            {
                options += "<option value='"+ data.d.results[index].Id +"'>"+
                    data.d.results[index][params.childLookupField]+"</option>";
            }
            $(child).append(options);

        });
        call.fail(function (jqXHR,textStatus,errorThrown){
            alert("Error retrieving information from list: " + params.childList + jqXHR.responseText);
            $(child).append(options);
        });
        
    }    
</script>

Step7. Create an item.