2

I have a SharePoint list that I need to filter according to this:

First of all, the items must be Booked by = Travel and Meeting Support and the Category should never be visible if they are Cancelled. Beside of these conditions, I also want the following to apply:

I want to see the items which has Category = New or Category = Pending (as long as they are booked by Travel and Meeting Support).

I do not want to see items which has Category = Completed AND Application Status = Approved. This means that I want to see Category = Completed as long as they do not have Application Status = Approved and vice versa. Of course only if they are Booked by = Travel and Meeting Support. (When it comes to Application Status = Approved, the items can not be visible if they have Category = Cancelled.)

I have tried to create a filtered view, but since it is difficult to group the conditiona snd there is a limitation of maximum 10 conditions, I can't make it work. I also tried CAML Designer, but it is not flexible enough. I also tried to modify the CAML code from the CAML designer, but still... either I get errors or I don't get the desired result.

Can anyone guide me with what I'm doing wrong?

This is one of the CAML code snippets that I've tried with:

   <Where>
  <And>
     <Or>
        <Or>
           <And>       
              <And>
                 <Eq>
                    <FieldRef Name='Booked_x0020_by' />
                    <Value Type='Choice'>Travel and Meeting Support</Value>
                 </Eq>
                 <Neq>
                    <FieldRef Name='Category' />
                    <Value Type='Choice'>Cancelled</Value>
                 </Neq>
              </And>      
              <Eq>
                 <FieldRef Name='Category' />
                 <Value Type='Choice'>New</Value>
              </Eq>
           </And>      
           <Eq>
              <FieldRef Name='Category' />
              <Value Type='Choice'>Pending</Value>
           </Eq>
        </Or>   
        <Neq>
           <FieldRef Name='Category' />
           <Value Type='Choice'>Completed</Value>
        </Neq>
     </Or>
     <Neq>
        <FieldRef Name='Application_x0020_Status' />
        <Value Type='Text'>Approved</Value>
     </Neq>
  </And>

  • Can you list possible values of each of Booked by, Category and Application Status? – Aziz Kabyshev Feb 20 '16 at 17:30
  • And the query you've posted returns error, wrong items or all items? – dstarkowski Feb 20 '16 at 19:52
  • Booked by can be "Travel and Meeting Support" or "Assistant", Category can be "New", "Pending", "Completed" or "Cancelled", Application Status can be "Waiting on approval, "Approved" or "Rejected" Either I get an error message when I try to modify the CAML myself or it shows wrong items, such as Category = Cancelled or if I elaborate with the CAML I sometimes see for exampel all "Pending" but not "New". – Jenny Fergéus Feb 20 '16 at 22:53
  • The CAML Query above render for example the items where Category = Cancelled, even thought they shouldn't. Any suggestions why? – Jenny Fergéus Feb 22 '16 at 7:43
1

If this were my problem to solve, I think I'd try to reduce the complexity on your CAML query. I'd do that by using a calculated column based on Category. I'd call it something like Category_InProgress and use this as a formula:

=IF(OR([Category]=New,[Category]=Pending),"Yes","No")

Your CAML query then becomes a lot easier.

<Where>
    <And>
        <Eq>
            <FieldRef Name='Booked_x0020_by' />
            <Value Type='Choice'>Travel and Meeting Support</Value>
        </Eq>
        <Eq>
            <FieldRef Name='Category_InProgress' />
            <Value Type='Text'>Yes</Value>
        </Eq>
        <Neq>
            <FieldRef Name='Application_x0020_Status' />
            <Value Type='Text'>Approved</Value>
        </Neq>
    </And>
</Where>
1

Thanks! I was hoping to create it only with CAML, but it seems to much for CAML to handle. Your suggestion worked fine!

I created totally three new columns:

CategoryInProgress

=IF(OR([ICategory]="New",[Category]="Pending"),"Yes","No")

NotCompletedApproved

=IF(AND([Category]="Completed",[Application Status]="Approved"),"Yes","No") 

BookedByCancelled

=IF(AND([Booked by]="Travel and meeting Support",[Category]<>"Cancelled"),"Yes","No") 

Thereafter the CAML weren't any problems:

<Where>
<And>
<Or>
<And>
<Eq>
  <FieldRef Name="CategoryInProgress" /> 
  <Value Type="Calculated">Yes</Value> 
</Eq>
<Eq>
  <FieldRef Name="Booked_x0020_by" /> 
  <Value Type="Text">Travel and meeting Support</Value> 
</Eq>
</And>
<Eq>
  <FieldRef Name="NotCompletedApproved" /> 
  <Value Type="Calculated">No</Value> 
</Eq>
</Or>
<Eq>
  <FieldRef Name="BookedByCancelled" /> 
  <Value Type="Calculated">Yes</Value> 
</Eq>
</And>
</Where>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.