Problem with the query of Sharepoint Calculated Fields ?

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

3 comments to Problem with the query of Sharepoint Calculated Fields ?

  • Hello David,

    Quoting your statement:

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

    Did you find it somewhere in the SharePoint documentation? I would like to state the same, just I wonder that SharePoint is able to display such values in document library view columns… Choosing the result type Text for a different actual one shows a meaningful value; a date/time for integer shows clearly nonsense; but why am I not seeing an exception? I wanted to find some Microsoft’s explanation what is defined or not but I can’t hit it. Do you have any link to MS’s documentation about it?

    Thanks,
    Ferda

  • admin

    Hello Ferdinand,

    i have no MS- Documentation for this, it is just what
    i found out during my work with sharepoint. Also a lot
    of Sharepoint stuff is not explained.

    in my opinion(not proofed) the value itself is not stored in the database,
    it is calculated by the formula within the view that Sharepoint
    generates on your query. So this is not a Sharepoint issue.
    The Formula is still testet, if you then querry the wrong field type in your statement that you feeded
    with wrong valueTypes you won’t find a match, so this is not a issue this is made by design.

    If you find a statement please post it here :)

    regards david

  • michelle veloso

    Very good!

Leave a Reply

  

  

  


*

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Get Adobe Flash playerPlugin by wpburn.com wordpress themes