locked
Filtering List based on multiple values RRS feed

  • Question

  • Hi,

    There is a requirement where a list has to be filtered base on the below criteria -

    If country = A OR country = B

    AND post = 1 OR post = 2

    AND region = X

    I think this can't be achieved using Filter through Modify view. Am I right? Atleast I am struggling to get the expected results.

    Any suggestions how else can this be achieved?

    Friday, November 10, 2017 8:24 AM

Answers

  • Hi,

    With the order of precedence for AND / OR , achieving the above using List View Filter would not be possible. However with Client Side Rendering available in SP 2013 we can achieve this with minimal client side code written as JS Link. 

    -You can save the below code to a JS file and upload it to site assets. 

    (function () {
        var overrideCurrentContext = {};
        overrideCurrentContext.Templates = {};
        overrideCurrentContext.OnPostRender = Filter;
        SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCurrentContext);
    })();
    
    
    function Filter(ctx)
    {    
        var rows = ctx.ListData.Row;
        for (var i=0;i<rows.length;i++)
        {
            var Country =rows[i]["Country"];
    		var Post =rows[i]["Post"];
    		var Region =rows[i]["Region"];
            var rowId = GenerateIIDForListItem(ctx, rows[i]);
    		var bool = (Country == "A" || Country == "B") && ( Post == "1" || Post == "2") && Region == "X"
    	    if( !bool )
    		{		 
    		 var row = document.getElementById(rowId);
             row.style.display = "none";
    		}       
        }
    }

    -Open the view in edit mode in bowser by appending "?toolpaneview=2" after .aspx in the address bar. 

    - Edit the list view webpart and add the JS link relative url as :

    If you have upload to the current web's site assets ise "~site" token, if it is site collection's use "~sitecollection" in the JSLink option. You can refer this link for more details on JSLink reference

    Demo : Added list items in View :

    Filtered Result :

    Regards,

    Priyan


    Please Up Vote and Mark this as Answer if it helps.

    • Marked as answer by IMSunny Monday, November 13, 2017 1:06 PM
    Friday, November 10, 2017 10:37 AM

All replies

  • Hi,

    With the order of precedence for AND / OR , achieving the above using List View Filter would not be possible. However with Client Side Rendering available in SP 2013 we can achieve this with minimal client side code written as JS Link. 

    -You can save the below code to a JS file and upload it to site assets. 

    (function () {
        var overrideCurrentContext = {};
        overrideCurrentContext.Templates = {};
        overrideCurrentContext.OnPostRender = Filter;
        SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCurrentContext);
    })();
    
    
    function Filter(ctx)
    {    
        var rows = ctx.ListData.Row;
        for (var i=0;i<rows.length;i++)
        {
            var Country =rows[i]["Country"];
    		var Post =rows[i]["Post"];
    		var Region =rows[i]["Region"];
            var rowId = GenerateIIDForListItem(ctx, rows[i]);
    		var bool = (Country == "A" || Country == "B") && ( Post == "1" || Post == "2") && Region == "X"
    	    if( !bool )
    		{		 
    		 var row = document.getElementById(rowId);
             row.style.display = "none";
    		}       
        }
    }

    -Open the view in edit mode in bowser by appending "?toolpaneview=2" after .aspx in the address bar. 

    - Edit the list view webpart and add the JS link relative url as :

    If you have upload to the current web's site assets ise "~site" token, if it is site collection's use "~sitecollection" in the JSLink option. You can refer this link for more details on JSLink reference

    Demo : Added list items in View :

    Filtered Result :

    Regards,

    Priyan


    Please Up Vote and Mark this as Answer if it helps.

    • Marked as answer by IMSunny Monday, November 13, 2017 1:06 PM
    Friday, November 10, 2017 10:37 AM
  • Hi IMSunny,

    We can use Collaborative Application Markup Language (CAML), an XML-based language that is used in Microsoft SharePoint, to define views and filter the items.

    1. Create a view and set filter country = A Or country = B.

    2. Edit the list in SharePoint Designer and edit this view in advanced mode.

    3. Find the following CAML query:

    <Query><Where><Or><Eq><FieldRef Name="country"/><Value Type="Text">A</Value></Eq><Eq><FieldRef Name="country"/><Value Type="Text">B</Value></Eq></Or></Where></Query>


    4. Change it to the following CAML query:

    <Query><Where>
    
    <And>
    
                    <And>
    
                                    <Or>
    
                                                    <Eq><FieldRef Name="country"/><Value Type="Text">A</Value></Eq>
    
                                                    <Eq><FieldRef Name="country"/><Value Type="Text">B</Value></Eq>
    
                                    </Or>
    
                                    <Or>
    
                                                    <Eq><FieldRef Name="post"/><Value Type="Text">1</Value></Eq>
    
                                                    <Eq><FieldRef Name="post"/><Value Type="Text">2</Value></Eq>
    
                                    </Or>
    
                    </And>
    
                    <Eq><FieldRef Name="region"/><Value Type="Text">X</Value></Eq>
    
    </And>
    
    </Where></Query>



    5. Save the changes.

    Query schema of CAML:

    https://msdn.microsoft.com/en-us/library/office/ms467521.aspx

    Best Regards,

    Linda Zhang


    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com

    Monday, November 13, 2017 2:44 AM