3

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>

Improve this question
4
  • Can you list possible values of each of Booked by, Category and Application Status? Commented Feb 20, 2016 at 17:30
  • And the query you've posted returns error, wrong items or all items? Commented Feb 20, 2016 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". Commented Feb 20, 2016 at 22:53
  • The CAML Query above render for example the items where Category = Cancelled, even thought they shouldn't. Any suggestions why? Commented Feb 22, 2016 at 7:43

2 Answers 2

Reset to default
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>
Improve this answer
0
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>
Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.