0

enter image description hereI want to build a CAML query that show the data on monthly and weekly basis. Please help me to provide the best solutions for the query

In SharePoint there is a list created for leave application that is having five column Name,type of leave , details , start date and end date . I want to fetch the data from the list that will show weekly employees onleave and monthly employees on leave

above is my list image

Now from this i have the html page to display the data from above list Now please provide me the query that i add in my javscript on where condition is check for month and week to display in my html page using script

  • and which column should be used for the filtering for the month and week? Created? Provide us more information please. – Zdeněk Vinduška Jan 16 at 8:49
  • hi i made the changes as you requested to do so – Davy Jan 16 at 9:43
  • Thanks, so you want to do : Montly > StartDate(1st of ThisMonth) - EndDate(Last of ThisMonth) and Weekly > StartDate(Monday this Week) - EndDate(Sunday this Week) query in caml, right? – Zdeněk Vinduška Jan 16 at 10:03
  • and where you want to use it, in c# application for SharePoint or in any view on SharePoint? – Zdeněk Vinduška Jan 16 at 10:07
  • i want to use it on sharepoint view – Davy Jan 16 at 10:32
2

OK it take me a while, but here is the solution: You need to create 2 calculated columns for Month filtering and 2 calculated columns for Week filtering. View with filtering for each.

EndDate in the formulas is the EndDate internal name of the our colum "end date". Replace ; with , depends on environment localization.

Month Filtering

Create Calculated columns:

  • StartMonth - formula: =DATE(YEAR(EndDate);MONTH(EndDate);1) outcome: DateTime, Date only
  • EndMonth - formula =DATE(YEAR(EndDate);MONTH(EndDate)+1;1)-1 outcome: DateTime, Date only

Create view with filter:

MonthLeaving - filter:

enter image description here

Week filtering

Create Calculated columns:

  • StartOfWeek - formula: =EndDate-WEEKDAY(EndDate)+1 outcome: DateTime, Date only
  • EndOfWeek - formula: =StartOfWeek+6 outcome: DateTime, Date only

Create view with filter:

WeekLeaving - filter:

enter image description here

How it looks like:

All users

Monthly leaving

Weekly leaving

It worked well for me. Hope you like it!

CAML Queries instead of SharePoint Views: Replace (Today) with date in format YYYY-MM-dd.

Monthly Leave:

<Query>
   <Where>
      <And>
         <Leq>
            <FieldRef Name='StartMonth' />
            <Value Type='Calculated'>(Today)</Value>
         </Leq>
         <Geq>
            <FieldRef Name='EndMonth' />
            <Value Type='Calculated'>(Today)</Value>
         </Geq>
      </And>
   </Where>
</Query>

Weekly Leave:

<Query>
   <Where>
      <And>
         <Leq>
            <FieldRef Name='StartOfWeek' />
            <Value Type='Calculated'>(Today)</Value>
         </Leq>
         <Geq>
            <FieldRef Name='EndOfWeek' />
            <Value Type='Calculated'>(Today)</Value>
         </Geq>
      </And>
   </Where>
</Query>
  • I updated my question with image please review than reply over it if you need any more information – Davy Jan 16 at 17:35
  • but i asked you and you wrote you want it in sharepoint view... so please make this as answer and create new topic about the caml query and i will add it tomorrow. – Zdeněk Vinduška Jan 16 at 18:04
  • Where you want to use this Page? It will be on SharePoint or somewhere else? – Zdeněk Vinduška Jan 16 at 18:18
  • i will using this list in sharepoint designer with i create a text file there i have script with me but how i will use this list in that script with the help of caml query – Davy Jan 17 at 5:36
  • I added these queries. Post edited. – Zdeněk Vinduška Jan 17 at 7:29

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.