Jos&#233&#59; Regal.

Hi, my problem is quite simple, I'm trying to load some items from a huge list using a SPQuery but I found the problem I don't know how / where / when to query it by a Lookup field.

I tried to create the view in MOSS with the where that I'm wanting to use and then load the items by the SPView (as a test) but the CAML query generated by MOSS for the view also doesn't work

MOSS generates for

<Eq>

<FieldRef Name="idOuter" />

<Value Type="Text">1,2</Value>

</Eq>

And I suposed it would be:

<Eq>

<FieldRef Name="idOuter" />

<Value Type="LookupMulti">1</Value>

<Value Type="LookupMulti">2</Value>

</Eq>

Either work, some blogs theorized about an structured data but nobody knows about that "structure" ... sincerely, I'm lost.

Someone knows how




Re: SharePoint - Development and Programming Querying a list by CAML

Chase M

Have you tried <Contains> instead of <Eq>

Then you shouldn't have to worry about the storage mechanism of the Lookup column.

Don't know if it works, but might be worth a shot.

Chase





Re: SharePoint - Development and Programming Querying a list by CAML

Ishai Sagi

Yes, my expirience shows that doing queries on multilookup fields is one big mess. You have to do a "contains" instead of equals, and use the text value, not the ID (otherwise, if you do "contains 1", you will get those items with id 10,11,12...)

So that means the titles have to be unique as well as the ID's. arrrg!

and before you say you can use the ID and concate the title (3;#test), this wont work, because it is not retured for the first item in the field value. strange!






Re: SharePoint - Development and Programming Querying a list by CAML

DiscGolferLarry

What is the best approach in query-ing for Yes/No fields. I have the following query but it returns and error.

string listQuery = "<OrderBy><FieldRef Name=\"Sequence\"/></OrderBy><Where><And><Eq><FieldRef Name=\"Agenda_x0020_Type\"/><Value Type=\"Text\">" + agendaType + "</Value></Eq><Eq><FieldRef Name=\"Active\"/><Value Type=\"Text\">Yes</Value></Eq></And></Where>";

SPQuery query = new SPQuery();

query.Query = listQuery;

SPListItemCollection partItems = partsList.GetItems(query);

listQuery string worked before adding the query on the Active field. The error message is below:

{"Conversion failed when converting the nvarchar value 'Yes' to data type bit."}

Any help is very appreciated.





Re: SharePoint - Development and Programming Querying a list by CAML

DiscGolferLarry

Nevermind, I had a dork moment.

I changed the listQuery string to:

string listQuery = "<OrderBy><FieldRef Name=\"Sequence\"/></OrderBy><Where><And><Eq><FieldRef Name=\"Agenda_x0020_Type\"/><Value Type=\"Text\">" + agendaType + "</Value></Eq><Eq><FieldRef Name=\"Active\"/><Value Type=\"bit\">1</Value></Eq></And></Where>";

type bit and value 1 should have been the query.

Hope this helps others having a dork moment!

Larry





Re: SharePoint - Development and Programming Querying a list by CAML

Ishai Sagi

I suggest in the future use a utility like CAMLBuilder to build your query - it will do the field types for you.

http://www.u2u.be/res/CamlQueryBuilder.aspx






Re: SharePoint - Development and Programming Querying a list by CAML

DiscGolferLarry

Thanks I will.

Larry





Re: SharePoint - Development and Programming Querying a list by CAML

KoryP

Indeed, queries on LookupMulti types seems futile! You'll get a match when doing a <Contains> for a value of "apple" against a value like:

strawberry;pineapple;orange

CAML doesn't have a <Like> tag for wildcard matching but almost provides a workaround by combining <Eq> and <BeginsWith> with <Contains>, but there isn't an <EndsWith> to complete the functionality:

Code Snippet

<Or>
<Eq>
<FieldRef Name="Favorites" />
<Value Type="LookupMulti">apple</Value>
</Eq>
<BeginsWith>
<FieldRef Name="Favorites" />
<Value Type="LookupMulti">apple;</Value>
</BeginsWith>
<Contains>
<FieldRef Name="Favorites" />
<Value Type="LookupMulti">;apple;</Value>
</Contains>
<EndsWith>
<FieldRef Name="Favorites" />
<Value Type="LookupMulti">;apple</Value>
</EndsWith>
</Or>

Anyone have a CAML workaround that could correctly match "apple" against these four potential multiLookups values and not a value containing "pineapple" :

apple

strawberry;apple;orange

apple;strawberry;orange

strawberry;orange;apple

- Kory





Re: SharePoint - Development and Programming Querying a list by CAML

Chase M

I was able to successfully (if I understand the problem correctly) query the list for items that have (among others) apple selected in a lookup-multi field named Favorites. The query does not return those items that have pineapple (and not apple) as values (among others) in the lookup-multi field. Here's the query I used:

<Where>

<Eq>

<FieldRef Name="Favorites"/>

<Value Type="Text">apple</Value>

</Eq>

</Where>

I also tried querying for items that contain apple and strawberry, and was successful:

<Where>

<And>

<Eq>

<FieldRef Name="Favorites"/>

<Value Type="Text">apple</Value>

</Eq>

<Eq>

<FieldRef Name="Favorites"/>

<Value Type="Text">strawberry</Value>

</Eq>

</And>

</Where>

Seems counter-intuitive (how can one value equal two different values), but in the database lookup multi field values are stored in AllUserDataJunctions which contains one row per selected item in the lookup-multi, so I can see why it works.





Re: SharePoint - Development and Programming Querying a list by CAML

Jits

Hi all,

You can query lookup fields (both multi and non multi) by ID in CAML.

This took me absolute ages to find out about... use the attribute "LookupId" in the FieldRef element.

eg:

<Eq>

<FieldRef Name="Category" LookupId="TRUE" />

<Value Type="Lookup">3</Value>

</Eq>


Cheers,
Jits