CAML query for nested and / or
-
Hi All, First time poster here so I hope I haven't messed my post up... I am currently using Sharepoint at work with a third party web part that utilises CAML for queries. things so far have been going well until I ran into my latest problem which I cannot solve and hope that someone out there could lend some assistance. Requirement is: "Action 1 due date" to be less than "Today" AND the Status not equal to "Completed" OR "Action 2 due date" to be less than "Today" AND the Status not equal to "Completed" This pattern will carry on for all four actions. I can get it to partially work but it seems to disregard the Or clause. Thank you all in adavance.
<Query>
<Where><And><Or><And><Or><And><Or><And><Lt><FieldRef Name="Action One Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt>
<Neq><FieldRef Name="Action One Status" />
<Value Type="Text">Completed</Value></Neq>
</And><Lt><FieldRef Name="Action Two Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt></Or><Neq><FieldRef Name="Action Two Status" />
<Value Type="Text">Completed</Value></Neq></And><Lt><FieldRef Name="Action Three Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt></Or><Neq><FieldRef Name="Action Three Status" />
<Value Type="Text">Completed</Value></Neq></And><Lt><FieldRef Name="Action Four Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt></Or><Neq><FieldRef Name="Action Four Status" />
<Value Type="Text">Completed</Value></Neq></And></Where></Query> -
Hi All, First time poster here so I hope I haven't messed my post up... I am currently using Sharepoint at work with a third party web part that utilises CAML for queries. things so far have been going well until I ran into my latest problem which I cannot solve and hope that someone out there could lend some assistance. Requirement is: "Action 1 due date" to be less than "Today" AND the Status not equal to "Completed" OR "Action 2 due date" to be less than "Today" AND the Status not equal to "Completed" This pattern will carry on for all four actions. I can get it to partially work but it seems to disregard the Or clause. Thank you all in adavance.
<Query>
<Where><And><Or><And><Or><And><Or><And><Lt><FieldRef Name="Action One Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt>
<Neq><FieldRef Name="Action One Status" />
<Value Type="Text">Completed</Value></Neq>
</And><Lt><FieldRef Name="Action Two Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt></Or><Neq><FieldRef Name="Action Two Status" />
<Value Type="Text">Completed</Value></Neq></And><Lt><FieldRef Name="Action Three Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt></Or><Neq><FieldRef Name="Action Three Status" />
<Value Type="Text">Completed</Value></Neq></And><Lt><FieldRef Name="Action Four Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt></Or><Neq><FieldRef Name="Action Four Status" />
<Value Type="Text">Completed</Value></Neq></And></Where></Query> -
This could be a good starting point: CAML Query Builder[^]
- Manohar P
Hi, I downloaded the CAML tool but am unable to use it because the CAML is referencing a list aggregation via a third party plugin (Bamboo Solutions Alerts Plus). There is a CAML builder within the plugin which I use but it doesn't like the nested operators. I do like the builder though for standard lists :)
-
Hi All, First time poster here so I hope I haven't messed my post up... I am currently using Sharepoint at work with a third party web part that utilises CAML for queries. things so far have been going well until I ran into my latest problem which I cannot solve and hope that someone out there could lend some assistance. Requirement is: "Action 1 due date" to be less than "Today" AND the Status not equal to "Completed" OR "Action 2 due date" to be less than "Today" AND the Status not equal to "Completed" This pattern will carry on for all four actions. I can get it to partially work but it seems to disregard the Or clause. Thank you all in adavance.
<Query>
<Where><And><Or><And><Or><And><Or><And><Lt><FieldRef Name="Action One Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt>
<Neq><FieldRef Name="Action One Status" />
<Value Type="Text">Completed</Value></Neq>
</And><Lt><FieldRef Name="Action Two Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt></Or><Neq><FieldRef Name="Action Two Status" />
<Value Type="Text">Completed</Value></Neq></And><Lt><FieldRef Name="Action Three Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt></Or><Neq><FieldRef Name="Action Three Status" />
<Value Type="Text">Completed</Value></Neq></And><Lt><FieldRef Name="Action Four Due Date" />
<Value Type="DateTime">
<Today/></Value></Lt></Or><Neq><FieldRef Name="Action Four Status" />
<Value Type="Text">Completed</Value></Neq></And></Where></Query> -
Hi Manohar and others, I ended up utilising the CAML builder and cleaned the query up in Notepad++. I tested it and it works well but unfortunately I ran into another problem :( If "Action One Due Date" is greater than today and status = "completed" it stops and doesn't continue on to the next actions to apply the rule. Is there a different way to go about this?
<Query>
<Where>
<And>
<Lt>
<FieldRef Name="Action One Due Date" />
<Value Type="DateTime"><Today/></Value>
</Lt>
<Or>
<Neq>
<FieldRef Name="Action One Status" />
<Value Type="Text">Completed</Value>
</Neq>
<And>
<Lt>
<FieldRef Name="Action Two Due Date" />
<Value Type="DateTime"><Today/></Value>
</Lt>
<Or>
<Neq>
<FieldRef Name="Action Two Status" />
<Value Type="Text">Completed</Value>
</Neq>
<And>
<Lt>
<FieldRef Name="Action Three Due Date" />
<Value Type="DateTime"><Today/></Value>
</Lt>
<Or>
<Neq>
<FieldRef Name="Action Three Status" />
<Value Type="Text">Completed</Value>
</Neq>
<And>
<Lt>
<FieldRef Name="Action Four Due Date" />
<Value Type="DateTime"><Today/></Value>
</Lt>
<Neq>
<FieldRef Name="Action Four Status" />
<Value Type="Text">Completed</Value>
</Ne