Filter list data using SPServices, CAML Query, and dropdown list

Question

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
– Robert Lindgren, Commented Jun 17, 2014 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
– cubanGuy, Commented Jun 17, 2014 at 11:25
If you use 1.2.3. you need to indent the code by 4 more blanks ;) — Robert Lindgren
– Robert Lindgren, Commented Jun 17, 2014 at 11:26

bgmCoder · Accepted Answer · 2015-12-05 21:17:33Z

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.

edited Dec 5, 2015 at 21:17

bgmCoder

3,59116 gold badges62 silver badges104 bronze badges

answered Jun 16, 2014 at 19:25

SP Combatant

4272 silver badges18 bronze badges

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
– cubanGuy

2014-06-17 11:20:55 +00:00
Commented Jun 17, 2014 at 11:20

Add a comment |

bgmCoder · Accepted Answer · 2015-12-05 21:17:28Z

1

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

edited Dec 5, 2015 at 21:17

bgmCoder

3,59116 gold badges62 silver badges104 bronze badges

answered Jun 17, 2014 at 15:17

SP Combatant

4272 silver badges18 bronze badges

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
– cubanGuy

2014-06-17 15:28:00 +00:00
Commented Jun 17, 2014 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
– SP Combatant

2014-06-17 15:45:01 +00:00
Commented Jun 17, 2014 at 15:45

Add a comment |

Supermode · Accepted Answer · 2014-06-17 02:04:02Z

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/

answered Jun 17, 2014 at 2:04

Supermode

1,7992 gold badges16 silver badges33 bronze badges

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
– Arsalan Adam Khatri

2014-06-17 06:09:38 +00:00
Commented Jun 17, 2014 at 6:09

Add a comment |

cubanGuy · Accepted Answer · 2014-06-18 14:41:51Z

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.

answered Jun 18, 2014 at 14:41

cubanGuy

5183 gold badges12 silver badges24 bronze badges

Add a comment |

Community · Accepted Answer · 2017-04-13 12:41:30Z

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.

edited Apr 13, 2017 at 12:41

CommunityBot

1

answered Dec 5, 2015 at 23:04

bgmCoder

3,59116 gold badges62 silver badges104 bronze badges

Add a comment |

Asad Refai · Accepted Answer · 2017-06-07 06:42:11Z

<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
– Asad Refai, Commented Jun 7, 2017 at 6:42

Stack Exchange Network

Filter list data using SPServices, CAML Query, and dropdown list

6 Answers 6

Your Answer

Linked

Hot Network Questions

Filter list data using SPServices, CAML Query, and dropdown list

6 Answers 6

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions