View Filter

I need help on a view filter. I tried it via view UI setting but does not work.  Then I am working in designer with the following code. I got an error when I open it in browser Cannot complete this action. Please try again.. I appreciate if someone can help me out on the query. Many thanks.

If in code, the condition should be

(OutstandinginvoiceTotal > 0) &&

   (

       (Today() - InvoiceDate > 60 && Today() InvoiceDate < 30) ||    

       (Today() - InvoiceDate_2 > 60 && Today() InvoiceDate_2 < 30)

   )    

Here is the view filter

<Query><Where>

    <And>

         <Gt><FieldRef Name="OutstandingInvoiceTotal"/><Value Type="Currency">0</Value></Gt>

         <And>

              <Geq><FieldRef Name="InvoiceDate"/><Value Type="DateTime"><Today OffsetDays="-60"/></Value></Geq>

              <And>

                 <Lt><FieldRef Name="InvoiceDate"/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Lt>

              </And>

         </And> 

         <Or>

              <And>

                 <Geq><FieldRef Name="InvoiceDate_2"/><Value Type="DateTime"><Today OffsetDays="-60"/></Value></Geq>

                 <And>

                    <Lt><FieldRef Name="InvoiceDate_2"/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Lt>

                 </And>

              </And>

         </Or>      

    </And>

</Where></Query>


  • Edited by eg10013 17 hours 55 minutes ago
April 17th, 2015 9:44am

I play around and the following works now. However, I need to add InvoiceDate_3 to InvoiceDate_5. I just copy the same block for InvoiceDate_2 to InvoiceDate_3 but it errors out?

<Query><Where>
   <And>
      <Gt><FieldRef Name="OutstandingInvoiceTotal"/><Value Type="Currency">0</Value></Gt>
      <Or>
       <And>
          <Geq><FieldRef Name="InvoiceDate"/><Value Type="DateTime"><Today OffsetDays="-60"/></Value></Geq>
          <Lt><FieldRef Name="InvoiceDate"/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Lt>
       </And>  
       <And>
          <Geq><FieldRef Name="InvoiceDate_2"/><Value Type="DateTime"><Today OffsetDays="-60"/></Value></Geq>
          <Lt><FieldRef Name="InvoiceDate_2"/><Value Type="DateTime"><Today OffsetDays="-30"/></Value></Lt>
       </And>     
      </Or>
   </And>
</Where></Query>

Free Windows Admin Tool Kit Click here and download it now
April 17th, 2015 11:00am

Here is my working solution:

  • Edited by eg10013 12 hours 5 minutes ago
  • Marked as answer by eg10013 12 hours 5 minutes ago
April 17th, 2015 3:29pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics