David's devloper Corner

Today we found a problem under Sharepoint 2007 with querrying Computed Fields.

We tried to get the LinkTitle with SPSiteDataQuery and also the FileLeafRef
within one querry for a generic List

But as result Sharepoint delivers only a Date ( Modified Date),we could not find
the Problem first, and tried the same with a differend order of the Viewfields, and it works !?

Then we tried the same for LinkFilename an found out that the CAML behavior varies with the order
of the Computed Fields in the Viewfields:

Example:

Query for a Document Library:
SPSiteData Query:
<Where><Gt><FieldRef Name=”ID”/><Value Type=”Counter”>0</Value></Gt></Where>
ViewFields:
<FieldRef Name=’LinkFilename’/>

Result:

So if we we got all results that we expect, whithout querring any other field !

Next Query:

Query for a Document Library:

SpSiteData Query:

<Where><Gt><FieldRef Name=”ID”/><Value Type=”Counter”>0</Value></Gt></Where>

ViewFields:

<FieldRef Name=’LinkFilename’/>
<FieldRef Name=’ LinkFilenameNoMenu’ />

Then we got this Result:

This is absolutely correct, so we normaly don’t need to querry all Fields on that the computed Field depends
But when we change the order of the Viewfields:

Query for a Document Library:

SpSiteData Query:

<Where><Gt><FieldRef Name=”ID”/><Value Type=”Counter”>0</Value></Gt></Where>

ViewFields:

<FieldRef Name= ‘LinkFilenameNoMenu’ />
<FieldRef Name=’LinkFilename’/>

We get a result within the Table that we did not Expect

So The Link file ame changes its Value when other Computed Fields are requestet, for example

we take a 3rd Viewfield and Change to Order:

Now again all Fields Changed Their Value, even the LinkFilenameNoMenu, LinkFilename has the same Values as

LinkFilenameNoMenu.

The solution is still not found:

Social MSDN

We have to Query the Fields :

<FieldRef ID=”{3c6303be-e21f-4366-80d7-d6d0a3b22c7a}” Name=”_EditMenuTableStart”/>
<FieldRef ID=”{2ea78cef-1bf9-4019-960a-02c41636cb47}” Name=”_EditMenuTableEnd”/>

To get the Correct result:

but this does ot work with other computed fields:

I tried to set viewfield “_EditMenuTableStart at the beginning of each query, but it does not work
the brings the columns into disorder even if i querry all the fields
that are referenced in the fields.xml( like purposed in the msdn article). So we need here a solution soon, that seems to be a
sharepoint bug, through that we get information about columns that we did not request for example a date as a title…

Do you have Problems with the SPSiteDataQuery (CAML) query  of Sharepoint
computed Fields. You are running in an Error or recive an empty DataTable ?
Normaly you think your query must be  correct because it looks  exactly like this:

<Query>
 <Where>
   <Eq>
     <FieldRef Name='MyComputed' />
     <Value Type='Computed'>111</Value>
   </Eq>
 </Where>
</Query

Haha, yes my friend, you are realy not alone with this problem on the planet.

It seems to be the logical solution for the correct caml querry,
but the value type is saved in the format it is determined
in the Computet Field, So  is the field of the Type Text, or Integer ?
Than the query must look like this:

<Query>
 <Where>
   <Eq>
     <FieldRef Name='MyComputed' />
     <Value Type='Number'>111</Value>
   </Eq>
 </Where>
</Query>

You can get the correct ValueType of the field with -> ((SPFieldCalculated)field).OutputType.ToString();

Please also note this. The Value defined for the coloumn has to match the ValueType that is expected with the formula !

For Example: The Result of (Column1+Column2)  is interpreted by Sharepoint as Number
so the ValueType must be Number
For Example: The Result of =Concatanate([Column1][Column2])  is interpreted by Sharepoint as Text
so the ValueType must be Text