Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I Have a custom sharepoint page with more than 12 textboxes. I need to perform a search opration based on user selections.What the requirement is: asked to perform search with out more no of "if-else" statements and getting data from Sharepoint list. present am working on Sharepoint using webservices(SPAPI_Core,Lists.js ) and Javascript.How to wite code to achieve above task...

This is exactly like CAML Query Builder...

Please help me..

share|improve this question

2 Answers 2

My sincere apologies for above solution, it worked for only two or three text boxes only.

Let look at possibilities of Dynamic CAML Builder.

  1. We need to prepare two queries for AND and OR's selected by the user

  2. We need pass "FieldRef" and "Value Type" values when constructing the query

Note:If you observe the CAML query generated by CAML Builder: If number of AND's is less that is added last like:<or><or><or><Fileref=...><FieldRef...></or><Fileref=...></or><FieldRef...></or><And><Fileref=...><FieldRef...></And>

The following example have six Dropdowns fallowed by filter options

var siteurl = "url";
var oLists=new SPAPI_Lists(siteurl);
var output='';

// Varibles to hold And's and Or's query selected by user
var Flag = 0;
var sFlag = 0;
var singleQuery;
var dynamicAndquery ='';
var dynamicOrquery ='';


// function to get all details like Category, Pillar
function getDetails()   
{
    var cat_id=document.getElementById('category');
    var category=cat_id.options[cat_id.selectedIndex].innerHTML;
    var pid=document.getElementById('pillar');
    var Pillar=pid.options[pid.selectedIndex].innerHTML;
    var sch_id=document.getElementById('school');
    var School=sch_id.options[sch_id.selectedIndex].innerHTML;
    var hid=document.getElementById('house');
    var House=hid.options[hid.selectedIndex].innerHTML;
    var off_id=document.getElementById('office');
    var Office=off_id.options[off_id.selectedIndex].innerHTML;
    var loc_id=document.getElementById('location');
    var Location=loc_id.options[loc_id.selectedIndex].innerHTML;

    var s1=document.getElementById('select0');
    var se1=s1.options[s1.selectedIndex].innerHTML; 

        if(se1 == "And")
        {
        var ct="category";
          Flag++;
          dynamicAndquery += constrcutSubQuery(se1,ct,category);
        }
        else if(se1 == "Or")
        {
            var ct="category";
            sFlag++;
            dynamicOrquery += constrcutSubQuery(se1,ct,category);

        }
        var s2=document.getElementById('select1');
        var se2=s2.options[s2.selectedIndex].innerHTML;
        if(se2 == "And")
        {
        var ct="Pillar";
          Flag++;
          dynamicAndquery += constrcutSubQuery(se2,ct,Pillar);
        }
        else if(se2 == "Or")
        {
            var ct="Pillar";
            sFlag++;
            dynamicOrquery += constrcutSubQuery(se2,ct,Pillar);
        }

        var s3=document.getElementById('select2');
        var se3=s3.options[s3.selectedIndex].innerHTML;
        if(se3 == "And")
        {
        var ct="School";
          Flag++;
          dynamicAndquery += constrcutSubQuery(se3,ct,School);
        }
        else if(se3 == "Or")
        {
            var ct="School";
            sFlag++;
            dynamicOrquery += constrcutSubQuery(se3,ct,School);
        }

        var s4=document.getElementById('select3');
        var se4=s4.options[s4.selectedIndex].innerHTML;
        if(se4 == "And")
        {
        var ct="House";
          Flag++;
          dynamicAndquery += constrcutSubQuery(se4,ct,House);
        }
        else if(se4 == "Or")
        {
            var ct="House";
            sFlag++;
            dynamicOrquery += constrcutSubQuery(se4,ct,House);
        }

        var s5=document.getElementById('select4');
        var se5=s5.options[s5.selectedIndex].innerHTML;
        if(se5 == "And")
        {
        var ct="Office1";
          Flag++;
          dynamicAndquery += constrcutSubQuery(se5,ct,Office);
        }
        else if(se5 == "Or")
        {
            var ct="Office1";
            sFlag++;
            dynamicOrquery += constrcutSubQuery(se5,ct,Office);
        }

        var s6=document.getElementById('select5');
        var se6=s6.options[s6.selectedIndex].innerHTML;
        if(se6 == "And")
        {
        var ct="Location";
          Flag++;
          dynamicAndquery += constrcutSubQuery(se6,ct,Location);
        }
        else if(se6 == "Or")
        {
            var ct="Location";
            sFlag++;
            dynamicOrquery += constrcutSubQuery(se6,ct,Location);
        }

        if(Flag==0)
        {
            var fullquery='';
        }

        if(Flag==1)
        {
            var fullquery=dynamicAndquery+"</And>";
        }
         if(Flag==2)
        {
        var fullquery=dynamicAndquery;
        }
        if(Flag==3)
        {
        var fullquery="<And>"+dynamicAndquery;
        }
        if(Flag==4)
        {
        var fullquery="<And><And>"+dynamicAndquery;
        }
        if(Flag==5)
        {
        var fullquery="<And><And><And>"+dynamicAndquery;
        }
        if(Flag==6)
        {
        var fullquery="<And><And><And><And>"+dynamicAndquery;
        }
        if(sFlag==0)
        {    
            var rquery='';
        }

        if(sFlag==1)
        {    
            var rquery=dynamicOrquery+"</Or>" ;
        }

        if(sFlag==2)
        {  
            var rquery=dynamicOrquery ;
        }

        if(sFlag==3)
        {  
            var rquery="<Or>"+dynamicOrquery ;
        }
        if(sFlag==4)
        {
            var rquery="<Or><Or>"+dynamicOrquery ;
        }
        if(sFlag==5)
        {
            var rquery="<Or><Or><Or>"+dynamicOrquery ;
        }
        if(sFlag==6)
        {
            var rquery="<Or><Or><Or><Or>"+dynamicOrquery ;
        }
        if(Flag==1 && sFlag > 1 )
        {
            var totalquery="<Query><Where>"+rquery+fullquery+"</Where></Query>";
        }

        if(sFlag==1 && Flag > 1)
        {
            var totalquery="<Query><Where>"+fullquery+rquery+"</Where></Query>";
        }
        if(se1 == "And")
        {
            var totalquery="<Query><Where>"+fullquery+rquery+"</Where></Query>";

        }
        if(se1 == "Or")
        {
            var totalquery="<Query><Where>"+rquery+fullquery+"</Where></Query>";
        }


        if(Flag > 1 && sFlag > 1 )
        {
            var totalquery="<Query><Where>"+fullquery+rquery+"</Where></Query>";
        }

 callGrid(totalquery);
}



// function to form advanced query dynamically
function constrcutSubQuery(se1,ct,category)
{
//alert(se1);
var strSubQuery = "";
strSubQuery ='<Eq><FieldRef Name="'+ct+'" /><Value Type="Choice">'+category+'</Value></Eq>';

        if(se1 == "And" && Flag==1)
        {   
            strSubQuery = "<And>" +strSubQuery ;//+ "</And>"; 
        }
        else if(se1 == "And" && Flag==2)
        {
            strSubQuery = strSubQuery + "</And>";
        }
        else if(se1 == "And" && Flag==3)
        {
            strSubQuery = strSubQuery + "</And>";
        } 
        else if(se1 == "And" && Flag==4)
        {
            strSubQuery = strSubQuery + "</And>";
        }
        else if(se1 == "And" && Flag==5)
        {
            strSubQuery = strSubQuery + "</And>";
        } 
        else if(se1 == "And" && Flag==6)
        {
            strSubQuery = strSubQuery + "</And>";
        }

        else if (se1== "Or" && sFlag==1)
        {
            strSubQuery = "<Or>" +strSubQuery ;//+ " </Or>";
        }
        else if (se1== "Or" && sFlag==2)
        {
            strSubQuery = strSubQuery + " </Or>";

        }
        else if (se1== "Or" && sFlag==3)
        {
            strSubQuery = strSubQuery + " </Or>";
        }
        else if (se1== "Or" && sFlag==4)
        {
            strSubQuery = strSubQuery + " </Or>";
        }
        else if (se1== "Or" && sFlag==5)
        {
            strSubQuery = strSubQuery + " </Or>";
        }
        else if (se1== "Or" && sFlag==6)
        {
            strSubQuery = strSubQuery + " </Or>";
        }

           return strSubQuery;
}

// function to display data using Grid 
function callGrid(qry)
{
    var result=oLists.getListItems('lst_customSearch',"",qry,"","","","");
    var itemxml=result.responseXML.getElementsByTagName('z:row');
    if(itemxml.length !=0)
    {
        output+='<table><tr style="background-color:#b3e2ff">';
        output+='<td>Category</td>';
        output+='<td>Pillar</td>';
        output+='<td>School</td>';
        output+='<td>House</td>';
        output+='<td>Office</td>';
        output+='<td>Location</td></tr>';
        for(i=0; i<itemxml.length; i++) 
        {                           
            var category = itemxml[i].getAttribute('ows_category');                     
            var pillar = itemxml[i].getAttribute('ows_Pillar');                          
            var school = itemxml[i].getAttribute('ows_School');
            var house = itemxml[i].getAttribute('ows_House');
            var office = itemxml[i].getAttribute('ows_Office1');
            var location = itemxml[i].getAttribute('ows_Location');
            output+='<tr>'; 
            output+='<td>'+category+'</td>';    
            output+='<td>'+pillar+'</td>';
            output+='<td>'+school+'</td>';
            output+='<td>'+house+'</td>';
            output+='<td>'+office+'</td>';
            output+='<td>'+location+'</td>';                         
            output+='</tr>';
        }
        output+='</table>'
        document.getElementById('tabid').innerHTML=output;  
    }
    else
    {
    alert('No Matching Records found');
    }
}
share|improve this answer

for the above requirement i did small coding using sharepoint SPAPI_.js files(Webservices) and javascript. The fallowing code generates a query based on user selected values.It worked perfectly in my working Environment

This is for small Employee table along with two Filter conditions

var siteurl = "SiteUrl";
var oLists=new SPAPI_Lists(siteurl);
var objName='';
var objSalary='';
var objDesignation='';
var output='';


var query='';
function searchDetails()   // search button code
{

    var details=['Name','Salary','Designation'];
    var values=[document.getElementById('txtName').value,document.getElementById('txtSal').value,document.getElementById('txtDesi').value];
    query+='<Query><Where>';
    for(var i=0;i<values.length;i++)
    {
        var qq=formQuery(i+1);      
        query+=qq;

        if(values[i] !="")
        {   
        query+='<Eq><FieldRef Name="'+details[i]+'" /><Value Type="Text">'+values[i]+'</Value></Eq>';
        }

    }
    for(i=values.length-1;i>0;i--)
    {
    var cc=closeQuery(i);
    query+=cc;
    }
    query+='</Where></Query>';
    callGrid(query);
}

function formQuery(i)
{
    //for(i =1;i<3;i++)
    //{
    var obj=document.getElementById('select'+i)
    if(obj)  
        {
        if(obj.options[obj.selectedIndex].innerHTML=="AND")
        return '<And>';
        else if(obj.options[obj.selectedIndex].innerHTML=="OR")
        return '<Or>';
        else
        return '';
        }
        else
        return '';
    //}
}
function closeQuery(i)
{
    var obj=document.getElementById('select'+i)
    if(obj)  
    {
        if(obj.options[obj.selectedIndex].innerHTML=="AND")
        return '</And>';
        else if(obj.options[obj.selectedIndex].innerHTML=="OR")
        return '</Or>';
        else 
        return '';
    }
    else
    return '';
}

function callGrid(qry)
{
    var result=oLists.getListItems('lst_AndOr',"",qry,"","","","");
    var itemxml=result.responseXML.getElementsByTagName('z:row');

    output+='<table><tr>';
    output+='<td>Name</td>';
    output+='<td>Salary</td>';
    output+='<td>Designation</td><tr>';
    for(i=0; i<itemxml.length; i++) 
    {//alert(); 
        var name = itemxml[i].getAttribute('ows_Name');                     
        var sal = itemxml[i].getAttribute('ows_Salary');                             
        var desig = itemxml[i].getAttribute('ows_Designation');
        output+='<tr>'; 
        output+='<td>'+name+'</td>';    
        output+='<td>'+sal+'</td>';
        output+='<td>'+desig+'</td>';                        
        output+='</tr>';
    }
    output+='</output>'
    document.getElementById('tdid').innerHTML=output;   
}
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.