Handling ";#" in lookup field query

Oct 3, 2014 at 2:29 PM
I have already posted a question, https://spservices.codeplex.com/discussions/568440, about having trouble using a query that has a lookup field in it. I have tried using a ";#" in the query to separate the two fields of the lookup, but I appear to be having no luck. Can someone help me? Thank You.
Oct 3, 2014 at 3:24 PM
Search the forums here and you will find a solution. I remember a similar request not too long ago. Matt Braemer posted the solution.
Oct 3, 2014 at 3:50 PM
Hello ptavares,

I found this:

https://spservices.codeplex.com/discussions/561334

It seems to show me how to build an array off of the results, ... maybe. I'm wanting to write a query like:
var Query = "<Query>" +
                    "<Where>" +
                    "<Eq>" +
                    "<FieldRef Name='Category_x0020_Name' />" +
                    "<Value Type='Text'>" +
                    "2;#Cat2" +
                    "</Value>" +
                    "</Eq>" +
                    "</Where>" +
                    " </Query>";
I'm actually wanting to then include an AND to the query to get it filtered down to one item, as if this query worked, I would get five results, but I know how to filter that down.

Thanks if you have anymore input
Oct 3, 2014 at 4:26 PM
Yes, that's the thread I had in my mind.
What's the name of the other column and value that you want to include in the second part of the AND clause?

Also: here is a reference to CAML - http://msdn.microsoft.com/en-us/library/office/ms467521(v=office.15).aspx
Oct 3, 2014 at 6:07 PM
Hello ptavares,

I'm thinking I can write the AND as this:
var Query = "<Query>" +
                    "<Where>" +
                    "<AND>" +
                    "<Eq>" +
                    "<FieldRef Name='Category_x0020_Name' />" +
                    "<Value Type='Text'>" +
                    "2;#Cat2" +
                    "</Value>" +
                    "</Eq>" +
                    "<Eq>" +
                    "<FieldRef Name='Amount' />" +
                    "<Value Type='Number'>" +
                    "100" +
                    "</Value>" +
                    "</Eq>" +
                    "</AND>" +
                    "</Where>" +
                    " </Query>";
That shouldn't be much of a problem, but I still need to get the query to give me the data form the list that would be filtered by the AND.

Thanks for your help.
Oct 3, 2014 at 6:19 PM
I think you are close, the only thing I can spot is your <AND> syntax seems wrong. In my experience, CAML is case sensitive. Someone correct me if I'm wrong. It's been a long time since I've used this stuff.

Also when using lookups, it's best to use the ID only during your query. You'll have to modify the CAML slightly to use the LookupId attribute. Example below...
<Query>" +
"<Where>" +
"<And>" +
"<Eq>" +
"<FieldRef Name='Category_x0020_Name' LookupId='True' />" +
"<Value Type='Lookup'>" +
"2" +
"</Value>" +
"</Eq>" +
"<Eq>" +
"<FieldRef Name='Amount' />" +
"<Value Type='Number'>" +
"100" +
"</Value>" +
"</Eq>" +
"</And>" +
"</Where>" +
" </Query>"
Cheers,
Matthew
Marked as answer by trout0525 on 10/6/2014 at 10:20 AM
Oct 6, 2014 at 5:20 PM
Thanks a lot Matthew. That did it. Your awesome.