Create a self-lookup field which only show certain items based on criteria

Question

I am working on a sharepoint online classic team site. where i added a custom list named "Pricing", which contain these main 2 fields:-

1) a column named "Type" of type drop-down list and have 2 options (General or Detailed).

2) a column named "Parent" of type lookup, which reference the same list (self-lookup field).

now the "Parent" lookup field will show all the items from the current list (Pricing), but i want the lookup field to only show the items which have "Type" = "General". so is it possible to apply filtering to the lookup columns? Thanks

You can use calculated column to filter lookup list. Check this https://sharepoint.stackexchange.com/questions/18247/how-to-make-a-filtered-lookup-field — P S, Mar 18 '19 at 14:02
@PS the user who answered the question deserve all the votes,,, simple and smart solution!! have you tried this on reality before? — John John, Mar 18 '19 at 15:32
Yes, the calculated column solution work and I used that before. — P S, Mar 19 '19 at 06:22
@PS if you add your comment as an answer so i can accept it.. thanks — John John, Mar 22 '19 at 01:38

score 1 · Answer 1 · answered Mar 19 '19 at 01:38

We can add the code below into a script editor web part in new/edit form page to remove all the items which have "MyType"="Detailed".

Note: By default the "Type" field is exists, I create a field name "MyType" to instead it.

<script src="https://code.jquery.com/jquery-1.12.4.min.js" type="text/javascript"></script>
<script type="text/javascript">
var listName="Pricing";
$(function(){
     $.ajax({
        url: _spPageContextInfo.siteAbsoluteUrl + "/_api/web/lists/getbytitle('" + listName + "')/items?$filter=MyType eq 'Detailed'",
        type: "GET",               
        headers: {
            "Accept": "application/json;odata=verbose",
        },
        success: function (data) {
            $.each(data.d.results,function(i,item){
                $("select[title='Parent'] option").each(function(){
                    if($(this).text()==item.Title){
                        $(this).remove();
                    }
                });
            });
        },
        error: function (error) {
            console.log(JSON.stringify(error));
        }
    });
});
</script>

thanks for the code and valuable help.. but in sharepoint online (where we can not be sure if the markup will change), is it advisable to write such a script? i mean this selection $("select[title='Parent'] option") might not work in the future... or this is not the case? — John John, Mar 19 '19 at 01:54

score 1 · Accepted Answer · answered Mar 22 '19 at 05:17

1

As mentioned in comments, you can use calculated column,to get values where "Type" is "General". Then use this calculated column in lookup column.

For more information: How to make a filtered lookup field

answered Mar 22 '19 at 05:17

P S

4,827
4
27
50

Create a self-lookup field which only show certain items based on criteria

2 Answers2