Compare two date in SharePoint 2007 Lists

Apr 12, 2011 at 2:03 AM

Hi All,

I read the topic post by Marc Anderson about question on SharePoint 2007 list

http://sympmarc.com/2011/04/07/real-world-spservices-example-checking-dates-against-a-blackout-list/

 

  $().SPServices({
operation: "GetListItems",
async: false,
listName: "List 1",
CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_0020_Date' /></ViewFields>",
CAMLQuery: "<Eq><FieldRef Name='Blackout_x0020_Date' /><Value IncludeTimeValue='FALSE' Type='DateTime'>" + blackoutDateValue + "</Value></Eq>",
completefunc: function (xData, Status) {
$(xData.responseXML).find("[nodeName='z:row']").each(function() {
// Do something here if there are items returned }); } }); });

By as far as I concerned, with SP2007 you can not run CAML queries and compare results from two lists. The way to work around this is having to run two seperate queries to capture the results and then adding some logical statement to compare the results retrieved by the two seperate queries.

Anyone had tried this code?

Please share if anyone manage to do compare two list of date in SharePoint 2007?


Regards,

John




Coordinator
Apr 12, 2011 at 3:27 AM

John:

I think you missed some of the context for the post. In a form, you can always use script to look things up in other lists using the Web Services, and that was what I was proposing. The GetListItems call above (or a similar one) would be used either in the PreSaveAction or on the blur event for the Date Picker. Further logic in the script would then take some action based on what was returned from "List 1" in the example.

M.

Apr 12, 2011 at 6:17 AM
sympmarc wrote:

John:

I think you missed some of the context for the post. In a form, you can always use script to look things up in other lists using the Web Services, and that was what I was proposing. The GetListItems call above (or a similar one) would be used either in the PreSaveAction or on the blur event for the Date Picker. Further logic in the script would then take some action based on what was returned from "List 1" in the example.

M.

Can you add further logic in the script?

Because as far as I know, it cannot be done.Compare two list in sharepoint.Especially in Sharepoint 2007

Apr 12, 2011 at 6:50 AM

I wonder what this code doing..can anyone explain?

$(xData.responseXML).find("[nodeName='z:row']").each(function() {

I never used SPServices before.Would like to see the capability of these libraries.

If I were you I would add an Item level Event Receiver to List 2 and OnItemAdding check that the date they are selecting is not in List2. You can then cancel the save and send a custom error message back via the SPItemEventProperties object.

Apr 12, 2011 at 6:54 AM

Wow.. this is fantastic... lets try and see the result :)

Apr 12, 2011 at 7:00 AM

Why not try this.... Cool.....

<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery-1.4.2.min.js"></script>
<script language="javascript" type="text/javascript" src="http://sharepoint.bankofamerica.com/sites/fremtest/Jpoint/jquery.SPServices-0.5.4.min.js"></script>
<script language="javascript" type="text/javascript">
  $(document).ready(function() {
    $().SPServices({
      operation:'GetListItems',
      listName: "PPCB",
      CAMLViewFields:'<ViewFields><FieldRef Name="ID"/></ViewFields>',
      completefunc: function (xData, Status) {
        alert(xData.responseXML.xml);
        $(xData.responseXML).find("[nodeName=z:row]").each(function() {
          var ID = $(this).attr("ows_ID");
          $().SPServices({
            operation: "UpdateListItems",
            listName: "PPCB",
            updates: "<Batch OnError='Continue'>" +
               "<Method ID='1' Cmd='Delete'>" +
                 "<Field Name='ID'>" + ID + "</Field>" +
               "</Method>" +
             "</Batch>",
            completefunc: function (xData, Status) {
              alert(xData.responseXML.xml);
            }
          });
        });
      }
    });
  });
</script>

Apr 12, 2011 at 7:13 AM

 

Hi All
I think this article may help us to understand more on CAML Queries
. Please review it :)

 

Coordinator
Apr 12, 2011 at 11:26 AM
Edited Apr 12, 2011 at 11:45 AM

This thread has balooned into all sorts of things!

SPServices is based on jQuery. jQuery and the JavaScript it is based upon run on the client side, meaning in the browser. No event receivers, no .NET code.

If you're interested in using SPServices, you'll need to read up on the SharePoint Web Services. There are links to everything available in SPServices in the Documentation.

Trust me that you can do what I outline in that blog post, because I and many people do this sort of thing daily.

M.

p.s. Take a look a SPCascadeDropdowns, which is one of the functions in SPServices. It compares values from two lists and many of the other functions in SPServices do as well. That's how these form enhancements work.

Coordinator
Apr 12, 2011 at 11:48 AM
 
This line:
$(xData.responseXML).find("[nodeName='z:row']").each(function() { 
is parsing the XML which is returned from the Lists Web Service and iterating through the items.
M.
Apr 12, 2011 at 12:50 PM

Of course it can be done, exactly as @nzuhairah says: Run one GetListItems and then nest a second one inside the first. Compare/contract/mashup to your heart's content.

Apr 13, 2011 at 1:15 AM

 

<script type="text/javascript" language="javascript" src="http://eccoe-sps2010/SiteDirectory/Digi/jquery%20libraries/jquery-1.4.2.min.js"></script>
<script type="text/javascript" language="javascript" src="http://eccoe-sps2010/SiteDirectory/Digi/jquery%20libraries/jquery.SPServices-0.6.0.min.js"></script>
<script type="text/javascript">

  $(document).ready(function() {
  alert("Hi SPServices");
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "List 1",    
    CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_0020_Date' /></ViewFields>",
    completefunc: function (xData, Status) {
      $(xData.responseXML).find("[nodeName=z:row]").each(function() {
        var liHtml = $(this).attr("ows_Blackout_x0020_Date");
        alert(liHtml);
      });
    }
  });
});
</script>


Ok.I set a blackout date field in List 1 and I manage to get the Title column, but when I change to Blackout_0020_Date, I pop-up an error say "undefined "..

Why?
Coordinator
Apr 13, 2011 at 1:21 AM

On useful trick is to look at the raw XML coming back from the Web Service call to be sure that you have the right column StaticName. You can do that with Firebug or simply alert xData.responseText.

It may simply be that "ows_Blackout_x0020_Date" isn't the right nodename in the XML. Also note that you don't have the right StaticName in the ViewFields.

Also, thought it shouldn't matter here because you are using jQuery 1.4.2, get in the habit of using single quotes around the nodename:

find("[nodeName='z:row']")

jQuery 1.5+ requires it.

M.

Apr 13, 2011 at 1:24 AM
kumHing wrote:

 

<script type="text/javascript" language="javascript" src="http://eccoe-sps2010/SiteDirectory/Digi/jquery%20libraries/jquery-1.4.2.min.js"></script>
<script type="text/javascript" language="javascript" src="http://eccoe-sps2010/SiteDirectory/Digi/jquery%20libraries/jquery.SPServices-0.6.0.min.js"></script>
<script type="text/javascript">

  $(document).ready(function() {
  alert("Hi SPServices");
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "List 1",    
    CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_0020_Date' /></ViewFields>",
    completefunc: function (xData, Status) {
      $(xData.responseXML).find("[nodeName=z:row]").each(function() {
        var liHtml = $(this).attr("ows_Blackout_x0020_Date");
        alert(liHtml);
      });
    }
  });
});
</script>


Ok.I set a blackout date field in List 1 and I manage to get the Title column, but when I change to Blackout_0020_Date, I pop-up an error say "undefined "..

Why?


Hi Kumhing, I think you need to adjust your code

 "ows_Blackout_x0020_Date" is not equal to CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_0020_Date' /></ViewFields>", .

Please check it back.

 

 

Apr 13, 2011 at 1:26 AM
sympmarc wrote:

On useful trick is to look at the raw XML coming back from the Web Service call to be sure that you have the right column StaticName. You can do that with Firebug or simply alert xData.responseText.

It may simply be that "ows_Blackout_x0020_Date" isn't the right nodename in the XML. Also note that you don't have the right StaticName in the ViewFields.

Also, thought it shouldn't matter here because you are using jQuery 1.4.2, get in the habit of using single quotes around the nodename:

find("[nodeName='z:row']")

jQuery 1.5+ requires it.

M.

Hi M.

You really brilliant to develop this libraries. Quite good. :)

Apr 13, 2011 at 1:46 AM

<script type="text/javascript" language="javascript" src="http://eccoe-sps2010/SiteDirectory/Digi/jquery%20libraries/jquery-1.4.2.min.js"></script>
<script type="text/javascript" language="javascript" src="http://eccoe-sps2010/SiteDirectory/Digi/jquery%20libraries/jquery.SPServices-0.6.0.min.js"></script>
<script language="javascript" type="text/javascript">

_spBodyOnLoadFunctionNames.push("PreSaveAction");

function PreSaveAction()
{
    var date1 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","Start Date"); 
    var date2 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","End Date");
    var arrDate1 = date1.value.split("/");
    var useDate1 = new Date(arrDate1[2], arrDate1[1]-1, arrDate1[0]);
    var arrDate2 = date2.value.split("/");
    var useDate2 = new Date(arrDate2[2], arrDate2[1]-1, arrDate2[0]);
    if(useDate1 > useDate2)
    {
        alert("The end date cannot happen earlier than the start date");
        return false; // Cancel the item save process
    }
    $(document).ready(function() {
  alert("Hi SPServices");
  $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "List 1",    
    CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_x0020_Date' /></ViewFields>",
    completefunc: function (xData, Status) {
      $(xData.responseXML).find("[nodeName=z:row]").each(function() 
      {
        var liHtml = $(this).attr("ows_Blackout_x0020_Date");
        alert(liHtml);
        
        if(useDate1 == liHtml)
        {
        	alert("The date is same.Chose another date")
        	return false;
        }
        
      });
    }
  });
}); 
    return true;  // OK to proceed with the save item
        
}

function getTagFromIdentifierAndTitle(tagName, identifier, title) 

{

  var len = identifier.length;
  var tags = document.getElementsByTagName(tagName);
  for (var i=0; i < tags.length; i++) 
  {
    var tempString = tags[i].id;
    if (tags[i].title == title && (identifier == "" || tempString.indexOf(identifier) == tempString.length - len)) 
    {
      return tags[i];
    }
  }
  return null;
}

</script>

What I'm trying to do here is

in List 1 : I have one column blackout dates.

In List 2 : I'm have to User date. It has two column. That is start date and End date. Start date cannot happen earlier than end date.

But, the problem, how can I do, in order to make start date and end date cannot happen in Blackout Date.

For example :

List 2 : User input : Start Date : 11/4/201 End Date : 13/4/2011

List 1 : Blackout Date : 12/4/2011

How?

 

Coordinator
Apr 13, 2011 at 1:47 AM

Thanks, John.

One quirk of the GetListItems operation (and several others) is that the column StaticNames are all prefixed by "ows_" in the returned XML. That said, there is still an issue, as you pointed out, but maybe not what you thought:

"ows_Blackout_x0020_Date" is not equal to CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_0020_Date' /></ViewFields>", .

Spot it?

M.

Coordinator
Apr 13, 2011 at 1:57 AM
 
It looks like you are grabbing sample code from various places and sticking it together without really thinking about how it ought to work.
 
When you call GetListItems as you've currently written it, you are going to get all of the items in the list back. That probably isn't what you want. From what you've said, you should probably put a CAMLQuery filter on the call to only get back Blackout Date(s) which are within the date range you are checking.
 
This line
_spBodyOnLoadFunctionNames.push("PreSaveAction");
will cause PreSaveAction to fire when the page loads, which isn't what you want.

This line
$(document).ready(function() {

does a similar thing, but with jQuery.

You're going to have things running in an odd order which doesn't accomplish your goals.
 
M.
Apr 13, 2011 at 3:59 AM
sympmarc wrote:
 
It looks like you are grabbing sample code from various places and sticking it together without really thinking about how it ought to work.
 
When you call GetListItems as you've currently written it, you are going to get all of the items in the list back. That probably isn't what you want. From what you've said, you should probably put a CAMLQuery filter on the call to only get back Blackout Date(s) which are within the date range you are checking.
 
This line
_spBodyOnLoadFunctionNames.push("PreSaveAction");
will cause PreSaveAction to fire when the page loads, which isn't what you want.

This line
$(document).ready(function() {

does a similar thing, but with jQuery.

You're going to have things running in an odd order which doesn't accomplish your goals.
 
M.

Thank you M.

I don' realized it.

function PreSaveAction()
{
    var date1 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","Start Date"); 
    var date2 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","End Date");
    var arrDate1 = date1.value.split("/");
    var useDate1 = new Date(arrDate1[2], arrDate1[1]-1, arrDate1[0]);
    var arrDate2 = date2.value.split("/");
    var useDate2 = new Date(arrDate2[2], arrDate2[1]-1, arrDate2[0]);
    
    alert(date1.value);
    alert(useDate1);
    
    if(useDate1 > useDate2)
    {
        alert("The End Date cannot happen earlier than the start date");
        return false; // Cancel the item save process
    }
    if(date1 != null)
    {
    $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "List 1",    
    CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_x0020_Date' /></ViewFields>",
    completefunc: function (xData, Status) {
      $(xData.responseXML).find("[nodeName=z:row]").each(function() 
      {
        var liHtml = $(this).attr("ows_Blackout_x0020_Date");        
        if(date1 == liHtml)
        {
        	alert("This is blackout date.Choose another date")
        	return false;
        }
        
      });
    }
  });
  
  }
    return true;  // OK to proceed with the save item
        
}


I got another problem. From List 2 and List 1, their values cannot be compared because they are returning same format.

How to code in order to make sure they are comparing same format.?

 

 

Coordinator
Apr 13, 2011 at 4:44 AM

You're still grabbing all values from List 1, which probably isn't what you want.

You'll need to manipulate the date values in your script so that you can compare them the way you need to. The best format for this is YYYYMMDD.

M.

Apr 13, 2011 at 5:57 AM

I want to put all the holiday date in List 1. So, that's why i'm taking all the values from List 1. Am I correct?

Coordinator
Apr 13, 2011 at 11:26 AM
Edited Apr 13, 2011 at 12:05 PM

They are your requirements, so I can't really answer that. If you want to pull all of the dates, then the code above is doing that. It would seem more efficient to only pull the dates that might be within the start-end range.

M.

Apr 14, 2011 at 1:49 AM

var s = '4/18/2011';
var inChar = '/';
var outChar = '-';
alert(s);
var s2 = s.split(inChar);
alert(s2);
s3 = s2.join(outChar);
alert(s3);

 

I have problem to match the code. Can anyone help me please? From this date 4/18/2011, i want to change to 18-4-2011 because I want this date to be match the date from List 1 : Blackout date.

The List 1:Blackout Date format is like this 2011-04-11 00:00:00

 Any helps really appreciated.

Coordinator
Apr 14, 2011 at 2:15 AM

How about this?

var s = '4/18/2011';
var dateArray = s.split("/");
testDate = dateArray[2] + "-" + zeropad(dateArray[0]) + "-" + zeroPad(dateArray[1]) + " 00:00:00";
function zeroPad(n) {
 return (n < 10) ? "0" + n : n;
}

M.

Apr 14, 2011 at 2:24 AM
sympmarc wrote:

How about this?

 

var s = '4/18/2011';
var dateArray = s.split("/");
testDate = dateArray[2] + "-" + zeropad(dateArray[0]) + "-" + zeroPad(dateArray[1]) + " 00:00:00";
function zeroPad(n) {
 return (n < 10) ? "0" + n : n;
}

 

M.

  Error : Object Expected.

<script type="text/javascript">
$(document).ready(function() 
{

var s = '4/18/2011';
var dateArray = s.split("/");
testDate = dateArray[2] + "-" + zeropad(dateArray[0]) + "-" + zeroPad(dateArray[1]) + " 00:00:00";

function zeroPad(n) 
{
 	return (n < 10) ? "0" + n : n;
}


</script>

Coordinator
Apr 14, 2011 at 2:26 AM

Well, I just typed it out. You'll need to do some debugging! I do see that I missed a capital "P" in zeroPad.

M.

Apr 14, 2011 at 2:33 AM

lol.Thank you very much M..

Don't see it..haiya..

Apr 14, 2011 at 4:24 AM

function PreSaveAction()
{
    var date1 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","User Date"); 
    var arrDate1 = date1.value.split("/");
    testDate = arrDate1[2] + "-" + zeropad(arrDate1[0]) + "-" + zeropad(arrDate1[1]) + " 00:00:00";
    
	function zeropad(n) 
	{
 		return (n < 10) ? "0" + n : n;
	}
    
    
    $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "List 1",    
    CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_x0020_Date' /></ViewFields>",
    completefunc: function (xData, Status) 
    {
      $(xData.responseXML).find("[nodeName=z:row]").each(function() 
      {
        var liHtml = $(this).attr("ows_Blackout_x0020_Date");
        alert(liHtml);
         	        
        if(testDate == liHtml)
        {
        	alert("The date is same.Chose another date");
        	return false;
        }
                
       });
    }
  });
  
  return true;


}

Hi All, this code should return false when I click button Submit, but apparently it's not. It still save the date into the list.

Any idea how to solve this?

Do i need to do for loop in somewhere?

Coordinator
Apr 14, 2011 at 1:58 PM

So the issue here is that your "return false;" is going to return the function for that "each" node as false, not the overall PreSaveAction function. Remember that when you call something like .each(function() {}); you're effectively creating and executing a function for every "each" (if that makes sense).

What I typically do with this type of scenario is define a variable at the start of my PreSaveAction, say "returnValue", then change it to false if need be. At the very end of your PreSaveAction you then do a quick if(returnValue) logic to see if it should be returned true or false.

 

function PreSaveAction()
{
    var returnValue = true;
    var date1 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","User Date"); 
    var arrDate1 = date1.value.split("/");
    testDate = arrDate1[2] + "-" + zeropad(arrDate1[0]) + "-" + zeropad(arrDate1[1]) + " 00:00:00";
    
	function zeropad(n) 
	{
 		return (n < 10) ? "0" + n : n;
	}
    
    
    $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "List 1",    
    CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_x0020_Date' /></ViewFields>",
    completefunc: function (xData, Status) 
    {
      $(xData.responseXML).find("[nodeName=z:row]").each(function() 
      {
        var liHtml = $(this).attr("ows_Blackout_x0020_Date");
        alert(liHtml);
         	        
        if(testDate == liHtml)
        {
        	alert("The date is same.Chose another date");
        	returnValue = false;
        }
                
       });
    }
  });
  
  if (returnValue) {
    return true;
  } else {
    return false;
  }
}

In your scenario, you could also move your alert to the very end inside the if(returnValue) logic.

Apr 15, 2011 at 3:16 AM

<script type="text/javascript" language="javascript" src="http://eccoe-sps2010/SiteDirectory/Digi/jquery%20libraries/jquery-1.4.2.min.js"></script>
<script type="text/javascript" language="javascript" src="http://eccoe-sps2010/SiteDirectory/Digi/jquery%20libraries/jquery.SPServices-0.6.0.min.js"></script>
<script language="javascript" type="text/javascript">

//_spBodyOnLoadFunctionNames.push("PreSaveAction");

function PreSaveAction()
{
    var date1 = getTagFromIdentifierAndTitle("INPUT","DateTimeFieldDate","User Date"); 
    var arrDate1 = date1.value.split("/");
    testDate = arrDate1[2] + "-" + zeropad(arrDate1[0]) + "-" + zeropad(arrDate1[1]) + " 00:00:00";
   
	function zeropad(n) 
	{
 		return (n < 10) ? "0" + n : n;
	}

    $().SPServices({
    operation: "GetListItems",
    async: false,
    listName: "List 1",    
    CAMLViewFields: "<ViewFields><FieldRef Name='Blackout_x0020_Date' /></ViewFields>",
    completefunc: function (xData, Status) 
    {
      $(xData.responseXML).find("[nodeName=z:row]").each(function() 
      {
        var liHtml = $(this).attr("ows_Blackout_x0020_Date");
        alert(liHtml);
   
        		if(testDate == liHtml)
        		{
        			returnValue = false;

        		}
           		else
           		{
           			
    				returnValue = true;
    			}         
       });
    }
  });
  
  if (returnValue) 
  {
    return true;
  } 
  else 
  {
    	alert("The date is same.Chose another date");
    	return false;
  }

}

Hi webdes03, thank you for your quick response. I managed to solve that edit a little bit from your code. Need to add else to return true value when user enter valid date. Otherwise, It shown object error.

But the other problem is, this code only took the lastest date from List 1 :

For example, I have 3 Blackout Date in List 1 :

Blackout Date 1: 15/4/2011

Blackout Date 2 : 16/4/2011

Blackout Date 3: 17/4/2011

When user select date : 15/4/2011 -  the system still return value true. and save the info

but when user select 17/4/2011 -  the system pop-up error Chose another date.

So, if my opinion, I need to add array to store the GetListItems? Am I correct? Or do you guys here have any better suggesttion?

 

Coordinator
Apr 15, 2011 at 1:29 PM
Edited Apr 15, 2011 at 1:37 PM

You added an "else returnValue true" (in the date compare logic) to the script that I sent you; that's the problem. You would need to define returnValue as true at the beginning of the script (see line 1 of the script I posted), and then assign it to false at the time of your date comparison. This ensures that the return is true unless one or more of the blackout dates is encountered.

By adding the else logic, you've essentially told the script to only compare the date to the last blackout date, as it resets returnValue to true or false each time.

The script I posted for you should work, did you get errors with it?

Edit: Are all of your blackout dates in this list, "List 1"?

Apr 21, 2011 at 2:22 AM

Hi All,

 

Read this link. http://65.216.39.79/ibi_html/javaassist/intl/EN/help/source/cs_rchelp_blackoutdates.htm

It makes us understand better on blackout dates. Seems to be out of our discussion topic but this may help in our understanding upon blackout dates. I believed in a strong fundamental and the roots of the cause before we can solve the problem...

Please take a look at this....  

Thanks... :)r

Coordinator
Apr 21, 2011 at 1:22 PM

The OP hasn't come back so I'm assuming he sorted it out. I don't believe there was any issue with the concept of the blackout dates, just that his logic in the script wasn't correct.

It should just be a case of:

 

if (date == blackout date 1) {

return true

} else if (date == blackout date 2) {

return true

} else if (date == blackout date 3) {

return true

} else {

return false

}

 

If you've got more than a couple blackout dates, or if they're dynamic, automate the logic with a loop, done.