locked
Filter a data view webpart by Time, not date. RRS feed

  • Question

  • Hello,

    I am trying to create a dataview that is a Sign Out Sheet. I have a field called Time Out and a field called Time In. I only want the items that are equal to the current time (Greater than or egual to time out and less than or equal to time in) to show on the home page. I can not get this to work. I have a dataview and I pasted the code, but I must be missing something else? Any advice would be appreciated!

     I am using SharePoint 2007, but this should work the same I am assuming whether it be SharePoint 7 or 10. Alslo we will eventually be going to 10. I posted this in another forum but I beleive I did incorrectly so I m reposting here. I am in a time crunch to get this done and have tried numerous things. Here is what I have done so far:

    1. Created a dataview from designer.
    2. Fields are Employee Destination Time Out Time In
    3. Changed the fields to show only time
    4. went to code view an this is the code I have._________SelectCommand="<View><Query><Where><And><Geq><FieldRef Name="Time_x0020_Out"/><Value Type="IncludeTimeValue="True"><Today/></Value></Geq><Leq><FieldRef Name="Expected_x0020_Time_x0020_Back"/><Value Type="IncludeTimeValue="True"><Today/></Value></Leq></And></Where></Query></View>" UpdateCommand="
    5. After that I tried to add a filter to the dataview that would show only the Time Out -greater than or equal to today and also Time In -less than or equal to today, but it will not stick.

    Stumped!

    Monday, October 22, 2012 1:08 PM

Answers

  • I am just not sure what is happening. It seems like this should be a simple thing to do.

    I started from scratch with a custom list, followed the steps above and made the below change in web controls

    View><Query><Where><And><Leq><FieldRef Name="Time_x0020_Out"/><Value Type="DateTime" IncludeTimeValue="True"><Today/></Value></Leq><Geq><FieldRef Name="Time_x0020_In"/><Value Type="DateTime" IncludeTimeValue="True"><Today/></Value></Geq></And></Where></Query></View

    Still could not get it to work. Until I have time to investigate further I created a list view and that is filtering by date, not what i need but will  have to do! Thank you for all your help!!  I have learned a lot from it.

    Tuesday, October 23, 2012 6:48 PM

All replies

  • If you manually update your SelectCommand (which I assume you're doing to add the IncludeTimeValue attribute) you won't want to go back into the filter dialog because it will likely overwrite your changes.  If you have sorting/filtering options you want to define in your dataview, make those changes first and THEN update your SelectCommand as needed.

    Hope that helps!

    Tuesday, October 23, 2012 3:28 AM
  • Thank you. Still not working! The time has passed and it is still on my page. Here are the order of my steps.

    1. I recreated a dataview webpart from my list.
    2. I changed the fields to show only time format
    3. I added a filter to show only if Time Out is greater than or equal to today, and Time in is less than or equal to today.
    4. I went to code view and modified this line.

    <DataSources><SharePointWebControls:SPDataSource runat="server" DataSourceMode="List" UseInternalName="true" selectcommand="&lt;View&gt;&lt;Query&gt;&lt;Where&gt;&lt;And&gt;&lt;Geq&gt;&lt;FieldRef Name=&quot;Time_x0020_Out&quot;/&gt;&lt;Value Type=&quot;IncludeTimeValue=&quot;True&quot;&gt;&lt;Today/&gt;&lt;/Value&gt;&lt;/Geq&gt;&lt;Leq&gt;&lt;FieldRef Name=&quot;Expected_x0020_Time_x0020_Back&quot;/&gt;&lt;Value Type=&quot;IncludeTimeValue=&quot;True&quot;&gt;&lt;Today/&gt;&lt;/Value&gt;&lt;/Leq&gt;&lt;/And&gt;&lt;/Where&gt;&lt;/Query&gt;&lt;/View&gt;" id="dataformwebpart6"><SelectParameters><WebPartPages:DataFormParameter

    5. Back to design view and the filter is gone.

    The view is still showing an entry that has passed the time of day. I am missing something probably simple but do not know what. I am new to modifying code.

    Example of what I want: If I sign out to the conference room for Time Out at 10 am and I will be back at 11 am then that entry should only show on my Home page view between 10-11 am. Stumped!

    Tuesday, October 23, 2012 12:30 PM
  • Based on what you're saying you want:

    "If I sign out to the conference room for Time Out at 10 am and I will be back at 11 am then that entry should only show on my Home page view between 10-11 am"

    I think you have your query backwards.  The SelectCommand you're using is this:

    <View><Query><Where><And><Geq><FieldRef Name="Time_x0020_Out"/><Value Type="IncludeTimeValue="True"><Today/></Value></Geq><Leq><FieldRef Name="Expected_x0020_Time_x0020_Back"/><Value Type="IncludeTimeValue="True"><Today/></Value></Leq></And></Where></Query></View>

    Which translates to this:

       timeout >= now
         and
       time back <= now

    When really I think you want it the other way around, right?

    Tuesday, October 23, 2012 3:12 PM
  • Yes, but I am not sure how to fix that in the code view as I am not that familiar with it. Or I can recreate the dataview change the filter to opposite of what i set it , repeat the selct command code change and it should work? Or is there an additional piece of code I am missing. Thank you!
    Tuesday, October 23, 2012 3:19 PM
  • Gotcha - the best thing we can do is get you familiar with it.  Basically what you're doing is adding the "IncludeTimeValue" attribute to your CAML query that is doing the date filter.  The update looks like this:

    <Value Type="DateTime" IncludeTimeValue="TRUE"><Today /></Value>

    The method is outlined in detail here:  http://nickgrattan.wordpress.com/2008/04/24/filtering-views-by-time-and-date/

    I didn't catch it at first but your CAML specified above is malformed:

    <Value Type="IncludeTimeValue="True">

    If it isn't too much trouble, I would suggest this:

    • re-create the dataview using the correct filters
    • update the select command to contain the IncludeTimeValue attribute for both time filters

    Where it gets tricky is if you are updating the select command in code view because the CAML query is HTML encoded.  The update needs to look like this:  IncludeTimeValue=&quot;True&quot;

    What I usually do instead is use split view in SharePoint Designer and place my cursor somewhere in the <SharePointWebControls:SPDataSource> tag in the code section. Once there, open the tag properties toolpane (view ribbon in 2010, Task Panes menu in 2007) and find the SelectCommand property and update it there without worrying about the encoding:

    Once you've made your updates, the filtering should work just fine!

    Tuesday, October 23, 2012 3:41 PM
  • Here is my code using this method.

    <View><Query><Where><And><Leq><FieldRef Name="Time_x0020_Out"/><Value Type="DateTime" IncludeTimeValue="TRUE"><Today/></Value></Leq><Geq><FieldRef Name="Expected_x0020_Time_x0020_Back"/><Value Type="DateTime" IncludeTimeValue="TRUE"><Today/></Value></Geq></And></Where></Query></View>

     I am obviously not getting something correct here, I am just stumped.

    Tuesday, October 23, 2012 4:40 PM
  • That looks correct, are you still seeing issues?
    Tuesday, October 23, 2012 4:47 PM
  • Yep. It is 12:17 and the second one in the list is still showing on my home page view. darn it.

    10/23/2012 12:00 PM 10/23/2012 12:05 PM
    10/23/2012 11:00 AM 10/23/2012 12:10 PM

    Tuesday, October 23, 2012 5:17 PM
  • It is strange. That item is not in the designer view but when i look at my page in browser view it shows.
    Tuesday, October 23, 2012 5:24 PM
  • Perhaps your browser is caching?  Add a ? to the end of the page URL and that should force a browser refresh.
    Tuesday, October 23, 2012 5:27 PM
  • I am just not sure what is happening. It seems like this should be a simple thing to do.

    I started from scratch with a custom list, followed the steps above and made the below change in web controls

    View><Query><Where><And><Leq><FieldRef Name="Time_x0020_Out"/><Value Type="DateTime" IncludeTimeValue="True"><Today/></Value></Leq><Geq><FieldRef Name="Time_x0020_In"/><Value Type="DateTime" IncludeTimeValue="True"><Today/></Value></Geq></And></Where></Query></View

    Still could not get it to work. Until I have time to investigate further I created a list view and that is filtering by date, not what i need but will  have to do! Thank you for all your help!!  I have learned a lot from it.

    Tuesday, October 23, 2012 6:48 PM