1

I'm trying to create a table using SPServices to fetch the data, and filter it using CAML Query and a drop down list. I would like to create three conditions:

A. If no item from the list is selected, well nothing happens (This part is working)

B. Select a a Fiscal Year from dropdown_a and Regional Office from dropdown_b. This part is working also, and this is query I'm using:

   CamlQuery = "<Query><Where><And><Eq><FieldRef Name='Regional_x0020_Office' /><Value Type='Text'>"+regionalOffice+"</Value></Eq><Eq><FieldRef Name='Fiscal_x0020_Year' /><Value Type='Text'>"+fiscalYear+"</Value></Eq></And></Where></Query>";

enter image description here

C. Now I would like to create a third option where if you select "All" in dropdown_a (Fiscal Year), it would list item from all the year for the office selected in dropdown_b. This is what I have done so far with no luck:

CamlQuery = "<OrderBy><FieldRef Name='Fiscal_x0020_Year' Ascending='TRUE'/></OrderBy><Query><Where><Eq><FieldRef Name='Regional_x0020_Office'><Value Type='Text'>"+regionalOffice+"</FieldRef></Eq></Where></Query>";

For some reason it is not showing my code, this is an screen shot:

enter image description here

Any ideas why this third option is not working? Thanks!

I apologize, but for some reason the code I pasted kept deleting themselves, so I have to add them as images.

  • It is not deleting, you must make sure to use the brackets icon in the RTE-panel to mark them as code. Simple do a couple of blank rows, paste in your code, select it and then click the "{ }" icon above the input field – Robert Lindgren Jun 17 '14 at 11:19
  • That's the way I did it when I posted the question. It showed only when I changed the conditions I listed from 1, 2, 3 to A, B, C. I don't know why. – cubanGuy Jun 17 '14 at 11:25
  • If you use 1.2.3. you need to indent the code by 4 more blanks ;) – Robert Lindgren Jun 17 '14 at 11:26
2

Without seeing the working query it's difficult to compare the two.

When troubleshooting it can be helpful to replace <Eq> with <Neq> or <Contains>

If no results are returned then it may alert you to a problem elsewhere.

  • SP Combatant. Thank you for the comments. I added the code from the beginning but it kept deleting itself. I have edited the question. Hopefully it wont go away again. – cubanGuy Jun 17 '14 at 11:20
1

Check your "regionalOffice" closing tag. It is <FieldRef> it needs to be </Value>.

  • Thanks! I missed that, but still doesn't work: CamlQuery = "<OrderBy><FieldRef Name='Fiscal_x0020_Year' Ascending='TRUE'/></OrderBy><Query><Where><Eq><FieldRef Name='Regional_x0020_Office'><Value Type='Text'>"+regionalOffice+"</Value></FieldRef></Eq></Where></Query>" – cubanGuy Jun 17 '14 at 15:28
  • 1
    By matching it to your working CAML, I think you can self-close the field ref tag, so it looks like this - <Eq><FieldRef Name='Regional_x0020_Office' /><Value Type='Text'>"+regionalOffice+"</Value></Eq> – SP Combatant Jun 17 '14 at 15:45
0

Here is some good example that hopefully help you with the query:

http://sympmarc.com/2010/09/29/real-life-example-with-spservices-telephone-list-gadget/
  • Please add more details from the link to your post, if the link breaks it will be of no help to future readers – Arsalan Adam Khatri Jun 17 '14 at 6:09
0

This actually worked with the query:

 CamlQuery = "<Query><Where><Eq><FieldRef Name='Regional_x0020_Office' /><Value Type='Text'>" + regionalOffice + "</Value></Eq></Where></Query>";

This will show all the items that match "regionalOffice".

I also had to add a:

 $("#myTable").remove();

I know all this would had been a lot easier using the Datatables jQuery plugin, but I went to a lot of pain just to be allowed to use jQuery and SPservices. Thank you all for the comments, specially @SP Combatant.

0

spservices admits the CamlQuery option but only adds your caml query as a fragment. You don't add the entire query, just the part that goes within the <Add> tags - you don't even add the <Add> tags OR specify which field - just add the innards. The spservices function takes care of setting which field for the lookup. You won't realize that unless you read the docs very carefully.

See my post here for a working example of a filter.

-1
<script type="text/javascript" src="/sites/Testmysite/Style Library/empoylee/jquery-2.2.3.min.js">
</script>
<script type="text/javascript" src="/sites/Testmysite/Style Library/empoylee/jquery.SPServices-2014.02.min.js">
</script>


<script>
    $(document).ready(function() {

        $("#btn").click(function() {
            //debugger;
            var input = $('#wt2').val();
            var input2 = $('#wt3').val();

            read(input, input2);
        });

    });

    function read(input, input2) {
        $().SPServices({
            operation: "GetListItems",
            async: false,
            webURL: "http://wfzo-pc:19684/sites/Testmysite",
            listName: "Employee",
            CAMLQuery: "<Query><Where><And><Eq><FieldRef Name='Designation' /><Value Type='Text'>" + input + "</Value></Eq><Eq><FieldRef Name='Department'/><Value Type='Text'>" + input2 + "</Value></Eq></And></Where></Query>",
            //CAMLQuery: "<Query><Where><Eq><FieldRef Name='Designation' /><Value Type='Text'>"+input+"<FieldRef Name='Department' /><Value Type='Text'>"+input2+"</Value></Eq></Where></Query>", 
            completefunc: function(xData, Status) {
                $(xData.responseXML).SPFilterNode("z:row").each(function() {

                    var Name = $(this).attr("ows_Title");
                    var Designation = $(this).attr("ows_Designation");
                    var Department = $(this).attr("ows_Department");
                    var age = $(this).attr("ows_age");
                    var tbldata = "<tr><td>" + Name + "</td><td>" + Designation + "</td><td>" + Department + "</td><td>" + age + "</td></tr>"
                    $("#tbl1").append(tbldata);


                });

            }
        });
    }
    ///////////////////////////////////////////////////////////////////////////////
    $(document).ready(function() {

        $("#btn2").click(function() {
            // debugger;
            var input3 = $('#wt4').val();
            readage(input3);
        });
    });

    function readage(input3) {
        $().SPServices({
            operation: "GetListItems",
            async: false,
            webURL: "http://wfzo-pc:19684/sites/Testmysite",
            listName: "Employee",
            CAMLQuery: "<Query><Where><Leq><FieldRef Name='age' /><Value Type='Text'>" + input3 + "</Value></Leq></Where></Query>",
            completefunc: function(xData, Status) {
                $(xData.responseXML).SPFilterNode("z:row").each(function() {

                    var Name = $(this).attr("ows_Title");
                    var Designation = $(this).attr("ows_Designation");
                    var Department = $(this).attr("ows_Department");
                    var age = $(this).attr("ows_age");
                    var tbldata = "<tr><td>" + Name + "</td><td>" + Designation + "</td><td>" + Department + "</td><td>" + age + "</td></tr>"
                    $("#tbl1").append(tbldata);


                });

            }
        });
    }
</script>

<div id="tbldatamain">
    <table id="tbl1" border="1px">
        <tr>



            <th>Name</th>
            <th>Designation</th>
            <th>Department</th>
            <th>age</th>

        </tr>
    </table>
</div>
<input id="wt2" type="text" placeholder="Designation" />
<input id="wt3" type="text" placeholder="Department" />
<input id="wt4" type="text" placeholder="age" />
<input id="btn" type="button" value="Search" />
<input id="btn2" type="button" value="check-age" />
  • Can you please add some description, and explain your answer. Just code will not be helpful. – Asad Refai Jun 7 '17 at 6:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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