Strange problem with date fields and editing in datasheet view - InfoPath Dev

InfoPath Dev

Use our Google Custom Search for best site search results.

Strange problem with date fields and editing in datasheet view

Last post 01-28-2022 11:44 AM by poppyq. 7 replies.
Page 1 of 1 (8 items)
Sort Posts: Previous Next
  • 06-16-2011 09:10 AM

    Strange problem with date fields and editing in datasheet view

    I have a series of forms that have a number of promoted fields in a SharePoint list.  Most of the promoted fields are editable from within SharePoint as well. 

    Recently, I've been noticing a discrepency between date fields as they appear in the SharePoint list and the form itself.  It seems that the forms are sometimes one day earlier than the forms.  After looking at the forms that are exhibiting this odd behavior, I was able to figure out that when any item is updated in the list view of the library, the next time the form is opened, the date fields are all set back to the prior day (It's actually 4 hours earlier - the date fields are set for 12:00 AM and when I open the form in IP, it shows them on the previous day at 8:00PM).  I don't understand what's going on.  Further, when I attempt to modify any date fields from datasheet view, it says that they cannot be edited because the file is checked out.  Other fields, however, can be edited and save just fine.

     It appears that this is happening with multiple forms.  There are no rules set on the date fields or the fields that are being modified from the list view (and even if there were, the rules shouldn't run, or so I've been brought to understand).  The format of the fields in both the form and the library are both date and time. I must be missing something simple...or at least I hope I am.

     Any ideas?

    Thanks in advance...

  • 06-16-2011 06:06 PM In reply to

    Re: Strange problem with date fields and editing in datasheet view

    Date format is different from SQL (SharePoint format) and XML (InfoPath format), and that may have something to do with your issue. XML wants it in YYYY-MM-DDThh-mm-ss where YYYY is a 4-digit year, MM the 2-digit month, DD the 2-digit day, T a delimiter, hh the 2 digit hour, mm the 2 digit minute and ss the two digit seconds. If SharePoint is not storing it in that format, it may be hard for InfoPath to parse it. You could add a Form Load rule to the InfoPath form to "fix it up" when the form is opened. To do that, add a separate text field and use that instead on SharePoint. Then, when the form opens, use that field to update the date. You may need to use some string commands (click on Fx button in your rule action when you set the value) to concatenate the date string together in the right format but should be simple enough.

    Patrick Halstead
    Project Manager at Qdabra
  • 06-17-2011 06:57 AM In reply to

    Re: Strange problem with date fields and editing in datasheet view

    Thanks so much for the reply.

    I will try your suggestion above as a band aid, but I fear that this is just going to come around and bite me later on.  I think that this issue has to do with a time zone problem. Surely, I shoudln't have to do that kind of workaround with every date/time field I create.  Is there any way to set the time zone in IP?  I think I've read information stating that it inherits from the user's computer, but my computer is set for ET and so is the SharePoint library.  The library keeps the dates correct, the form does not.  I appreciate that workaround,, however, I need to find a better solution.  Some of my forms have 15+ date fields and I have 11 forms to go through, 

  • 06-17-2011 10:45 AM In reply to

    Re: Strange problem with date fields and editing in datasheet view

    more info:

    If I change my fields to date only and use the None (display XML value) formatting option, the problem goes away. For date fields, this isn't so bad, but date/time fields are just so horrible to read. How can I use formatting and keep it displaying the correct time?  I tried using the formats that it says are tied to the date and time settings used by the OS and then tried using those that were not.  Either way, same problem.  Is there something form level that I'm missing.  The regional settings on my SharePoint site are correct and in my time zone.  Why does IP want 4 hours from me? 

  • 06-20-2011 06:29 AM In reply to

    Re: Strange problem with date fields and editing in datasheet view

    So, I finally figured out how I can do this.  I am using the XML value for my date fields and have created a Form Open rule that looks at each date field and uses the substring function to only show the date portion.  Clunky, but it works. 

     If anyone knows of a better way to do this, please let me know. 

  • 08-23-2011 09:31 AM In reply to

    Re: Strange problem with date fields and editing in datasheet view

    I have done some testing in InfoPath and tried to set my DateTime field to a Text field which contains a date in the format of YYYY-MM-DDThh-mm-ss. InfoPath didnt parse this as a datetime.

    I see that the DateTime field recommends a format for their default value. If I use that format directly in the DateTime field then it works perfectly. But if I use a Text field to hold the content with the exact same format, the DateTime field cannot parse it.

    @Patrick: I am a bit confused about how I am going to be able to get a datetime value from SharePoint into InfoPath even if I know what format each accepts/wants. InfoPath doesnt seem to like to accept much in terms of datetime.

    Any additional information would be helpful as this is the last hurdle in moving my project into Production.

  • 08-23-2011 10:03 AM In reply to

    Re: Strange problem with date fields and editing in datasheet view

    OK, here is what I've learned about this whole thing:  


    If I have a site column created as a Date/Time column and a form field created as a Date/Time column and I publish the form, promoting the field and checking off the box to allow editing, publish it as site content type, it publishes and stores the data just fine in both places. 

    Once I go to edit the properties (in my test, I just changed the file name) and then open the form the next time, it re-formats it to UTC time zone.

    When I open the form in IP after editing from SP, it lobs off 4 hours (which is the offset for my time zone).  Here’s the XML before the properties were edited: (problem fields highlighted):                 <my:DateTime xmlns:xsi="">2011-07-29T18:46:43</my:DateTime>                <my:FormDateOnly xsi:nil="true" xmlns:xsi=""></my:FormDateOnly>                <my:ListDateOnly xsi:nil="true" xmlns:xsi=""></my:ListDateOnly>                <my:DateOnly xsi:nil="true" xmlns:xsi=""></my:DateOnly>                <my:DateTimeToDateOnly xsi:nil="true" xmlns:xsi=""></my:DateTimeToDateOnly>                <my:DateTimetoDateOnly_All xmlns:xsi="">2011-07-31T00:00:00</my:DateTimetoDateOnly_All> And after…<my:DateTime xmlns:xsi="">2011-07-29T18:46:00Z</my:DateTime><my:FormDateOnly xsi:nil="true" xmlns:xsi=""></my:FormDateOnly><my:ListDateOnly xsi:nil="true" xmlns:xsi=""></my:ListDateOnly><my:DateOnly xsi:nil="true" xmlns:xsi=""></my:DateOnly><my:DateTimeToDateOnly xsi:nil="true" xmlns:xsi=""></my:DateTimeToDateOnly><my:DateTimetoDateOnly_All xmlns:xsi="">2011-07-31T00:00:00Z</my:DateTimetoDateOnly_All> 

    It’s adding the Z in there and that’s where the problem is.


    Unfortunately, I couldn't get anyone from the server side here to investigate further so I had to create a workaround.  That workaround was to simply create a submit rule that adds 5 hours to the time (even though it's showing on my form as date only, it publishes with a time by virtue of it being a date time field).  Since I'm only concerned with the date, the time is irrelevant.  Also, I learned that it only modifies it the first time...after that, it keeps the Z in there.     One last thing, I did try appending a time zone to the date field manually and SP stripped it out.  What a mess. Hope this helps...
  • 01-28-2022 11:44 AM In reply to

    Re: Strange problem with date fields and editing in datasheet view

    I know this is old, but I just ran into this one myself. The problem for me is that it is intermittent. All forms are browser forms. I noticed that the display value in the form was showing me 6:19 am, for example, but the value in SP is 12:19 pm. When I look at the XML for the form, it has appended the Z to the end. I can't, for the life of me, figure out why this is happening. Users only edit these forms through InfoPath itself, never through Quick Edit or in SharePoint. I also have 18 other forms that I've never seen this issue on, and it only happens on one date/time field that I use in the form. All other date/time fields match up exactly and don't have the Z appended. The worst part, that I mentioned, is that it's intermittent so I can't tie it to a specific action. Thankfully, the data in SP is fine for reporting, but in some cases it can show that a form was "closed" before it was created since created might show 7:19 am and with the Z it shows closed as 6:19 am. Ugh.
Page 1 of 1 (8 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.