locked
display list content based on a start and end date RRS feed

  • Question

  • Hi all I have inherited from a previous user a list that contains data and a title and description column as well ad start and end dates.

    The idea is to display the title and description based on the start / end date....so each title and description will run between the start and end dates. There is another column named current which when popuated with any value will display data currently stored in the title / description columns.

    In the display page there is some CAML code as below

    CAMLViewFields:"<ViewFields><FieldRef Name='Description' /></ViewFields>", //Fields returned by the query
            CAMLQuery:"<Query><Where><IsNotNull><FieldRef Name='Current' /></IsNotNull></Where></Query>",

    This queries the current column and if any column is not null it sows the title and description on the page.

    My issue is that nothing is done automatically. From looking at the calculated column options it there doesnt seem to be an option for displaying based on if the current date is between two dates etc so if current date is between start and end date then display the data.

    I have also read that you can't use today either in a calculated column.

    Can anyone offer some guidance over how this could be achieved please?

    Thanks in advance

    Sunday, August 11, 2013 9:39 PM

Answers

  • Hi Michael,

    In order to display items of a list if the current date (i.e. today) falls between start date and end date, here is what we can do:

    1. Create a new view by going into the list tab tools.

    2. Select Standard view, give it a name and select the columns that you want to  display.

    3. Under Filter, select 'Show items only when the following is true'

    4. From the drop down list, select 'Start Date' or the column that represents start date in your list.

    5. From another drop down list select "is greater than"

    6. In the text box type [Today]

    7. Select 'And'

    8. From the next drop down list, select "EndDate" or the column that represents end date in your list.

    9. From next drop down list select "is less than"

    10. In the text box type [Today]

    11. Click ok.

    I am not sure if this meets your requirement  :)

    Thank you.

    • Edited by arni1101 Monday, August 12, 2013 2:27 PM
    • Marked as answer by michael_uk Thursday, August 29, 2013 9:15 PM
    Monday, August 12, 2013 2:24 PM

All replies

  • Hi Michael,

    I am sorry but I didn't quite get your question.

    Are you trying to create a views for your list based on start date and end date or are you trying to make a column?

    Sunday, August 11, 2013 10:56 PM
  • Have you looked at the DateRangesOverlap inside the caml query? It allows for functionality like today and such

    http://www.3guysonsharepoint.com/?p=589


    Please mark my response as an answer if appropriate.
    Learn.SharePoint.com

    Sunday, August 11, 2013 11:39 PM
  • That's fine, thank you for asking me to clarify.

    I have got a list which has the columns, Title, start date, end date, description and current.  There are around 20 data entries for the list.  Each entry will need to be displayed for a week as indicated by the start and end dates associated with each title and description and then move on to the next entry.

    On one of my sharepoint pages there is the code

    CAMLViewFields:"<ViewFields><FieldRef Name='Description' /></ViewFields>", //Fields returned by the query
            CAMLQuery:"<Query><Where><IsNotNull><FieldRef Name='Current' /></IsNotNull></Where></Query>",

    Due to the previous person not being able to get the title and description to update dynamically according to the date they added an extra field called current and for each week they addded a character in to that field for the relevant entry which corresponded to the CAML code above. When the current column is not null then display the title and description columns from the list

    webURL:"************",  //URL of the web site that contains the list
      listName:"Themes1",  //Name of the list
      displayDuration:10000,  //Duration in milliseconds between each rotation
      fadeDuration:1500,  //Duration in milliseconds of the rotation
      CAMLViewFields:"<ViewFields><FieldRef Name='Description' /></ViewFields>", //Fields returned by the query
      CAMLQuery:"<Query><Where><IsNotNull><FieldRef Name='Current' /></IsNotNull></Where></Query>",  //override the CAML query
      
      itemHtml:"<div style='color:blue; text-align=center; font-family:Lucida Sans Unicode; font-size:30px; text-align=center;  font-weight: bold'>{ows_Title}</div><div style='color:green; font-size:15px; text-align=center; font-family:Lucida Sans Unicode '>{ows_Description}</div>",  //pattern used to display each item. Change this if you want specific formatting. Example : itemHtml:"<div>{ows_Title}</div><div>{ows_Body}</div>"
      debug:false,  //If true, some debugging information will be displayed in the console (Firebug for Firefox, native in IE8)
      outerContainer:this,  //Set this to a jQuery object that has to be hidden if there are no items in the list. example : outerContainer:$("#myContainer")
      randomStart:true,  //Set to false if you want items to be displayed in normal order
      overflow:"hidden",  //specify overflow : "visible" or "hidden"
      getItemHtml:jQuery.SPItemsRotator.getItemHtml  //function that will be used to render each item
     });
       });

    I have been looking into either whether the title and description can be picked up purely based on the start and end dates or if the current column can be populated with any character content based on the current day being between the start and end dates etc.

    Does this make better sense?

    Thanks

    Monday, August 12, 2013 8:38 AM
  • Hi Michael,

    In order to display items of a list if the current date (i.e. today) falls between start date and end date, here is what we can do:

    1. Create a new view by going into the list tab tools.

    2. Select Standard view, give it a name and select the columns that you want to  display.

    3. Under Filter, select 'Show items only when the following is true'

    4. From the drop down list, select 'Start Date' or the column that represents start date in your list.

    5. From another drop down list select "is greater than"

    6. In the text box type [Today]

    7. Select 'And'

    8. From the next drop down list, select "EndDate" or the column that represents end date in your list.

    9. From next drop down list select "is less than"

    10. In the text box type [Today]

    11. Click ok.

    I am not sure if this meets your requirement  :)

    Thank you.

    • Edited by arni1101 Monday, August 12, 2013 2:27 PM
    • Marked as answer by michael_uk Thursday, August 29, 2013 9:15 PM
    Monday, August 12, 2013 2:24 PM
  • Hi arni, yes that has worked so far so in my new view it only displays the one item which should last for a week before moving to the next one.  The next test is to see if CAML query code can look at views of a list instead of the entire list itself?

    Thanks


    • Edited by michael_uk Tuesday, August 13, 2013 12:10 PM
    Tuesday, August 13, 2013 12:10 PM
  • Hi again arni, in addition to my above reply I have got the following code that is pulling the information from my sharepoint list and displaying the columns date and description. Although the list view displays  correctly I'm having issues getting the code to select / display my new view. The List is called Themes1 and the view is called 'currentweeklytheme'.

    I've done hours worth of searching and possible the script below will only allow for list to be selected and now a view of that list.  The next step is to look at the CAML query via some software and see if I can use exactly the same criteria to select and output the desired columns based on the dates needed. Are you aware of any other options please?

    Thanks again

    webURL:"/schools/********/staff/ftr/",        //URL of the web site that contains the list
            listName:"Themes1",        //Name of the list
            displayDuration:10000,        //Duration in milliseconds between each rotation
            fadeDuration:1500,        //Duration in milliseconds of the rotation
            CAMLViewFields:"<ViewFields><FieldRef Name='Description' /></ViewFields>", //Fields returned by the query
            CAMLQuery:"<Query><Where><IsNotNull><FieldRef Name='Current' /></IsNotNull></Where></Query>",        //override the CAML query
            
            itemHtml:"<div style='color:blue; text-align=center; font-family:Lucida Sans Unicode; font-size:30px; text-align=center;  font-weight: bold'>{ows_Title}</div><div style='color:green; font-size:15px; text-align=center; font-family:Lucida Sans Unicode '>{ows_Description}</div>",        //pattern used to display each item. Change this if you want specific formatting. Example : itemHtml:"<div>{ows_Title}</div><div>{ows_Body}</div>"
            debug:false,        //If true, some debugging information will be displayed in the console (Firebug for Firefox, native in IE8)
            outerContainer:this,        //Set this to a jQuery object that has to be hidden if there are no items in the list. example : outerContainer:$("#myContainer")
            randomStart:true,        //Set to false if you want items to be displayed in normal order
            overflow:"hidden",        //specify overflow : "visible" or "hidden"
            getItemHtml:jQuery.SPItemsRotator.getItemHtml   

    Tuesday, August 13, 2013 2:54 PM
  • Try this caml designer

    http://sharepoint.biwug.be/Pages/Downloads.aspx

    Tuesday, August 13, 2013 7:46 PM
  • Hi again, I have actually been using a CAML generator for the past 4 hours. The software is called U2C CAML query builder.

    Having linked it to my sharepoint list the code it generates is as follows

    CAMLQuery:"<Query><Where><And><Lt><FieldRef Name='Start' /><Value Type='DateTime'>[Today+00Day(s)]</Value></Lt><Gt><FieldRef Name='End' /><Value Type='DateTime'>[Today+00Day(s)]</Value></Gt></And></Where></Query>",

    This works for my cureny value and if I alter the dates for future list values this also works but yet when I put it in the code on the page, nothing displays which seems strange as when the query is run, the software outputs the correct results.

    My full code is

    $(window).ready(function(){
        $("#divAnnouncements").rotateAnnouncements({
            webURL:"/schools/ecclesfield/staff/ftr/",        //URL of the web site that contains the list
            listName:"Themes1",        //Name of the list
            displayDuration:10000,        //Duration in milliseconds between each rotation
            fadeDuration:1500,        //Duration in milliseconds of the rotation
            CAMLViewFields:"<ViewFields><FieldRef Name='Description' /></ViewFields>", //Fields returned by the query
            CAMLQuery:"<Query><Where><And><Lt><FieldRef Name='Start' /><Value Type='DateTime'>[Today+00Day(s)]</Value></Lt><Gt><FieldRef Name='End' /><Value Type='DateTime'>[Today+00Day(s)]</Value></Gt></And></Where></Query>",
            
            
            CAMLQuery:"<Query><Where><IsNotNull><FieldRef Name='Current' /></IsNotNull></Where></Query>",        //override the CAML query     

    The original line that works i the bottom CAMLQuery, if I comment this out to leave the code that the software has given me, it doesn't work but if I then comment out the software code and uncomment the last CAML query where fieldname=current it works again.

    I'm not sure if something else is missing or if the CAML query code is wrong....if you know?

    Thanks

    Tuesday, August 13, 2013 9:07 PM
  • This software doesn't appear to be let me use the CAML commands greater than or less than.
    Wednesday, August 14, 2013 10:22 AM
  • Hi thanks for your help, a combination of this software and the above code did the trick.
    Thursday, August 29, 2013 9:16 PM