Qdabra qRules

Re: what is the correct format for submitting time to a SharePoint list

Hilary Stoupa — Mon, 20 May 2013 15:13:57 GMT

SharePoint wants the date in the format:

2012-05-04 12:00:00

InfoPath stores date time as:

2012-05-04T12:00:00

So - you need to remove the "T" prior to submitting to a date time column in SharePoint.

The options are:

Store the date time as a string in InfoPath so you can just use a translate formula on it to remove the T, OR
Have an additional field where you store the date time value as a string w/out the T

In general, I like option 2 - so that I have the date time info in the form as a date time still. We've been talking about in the future adding some data type options in the form used for mapping - then qRules could just handle some of these things without putting the burden on the designer.

EDITED TO ADD: I see I did an awful job of reading your original question - but the time values you are storing are for a specific date - which I imagine you also have in the form's XML. I'd use a calculated string field that concats those values (with a space between) and submit it to a date time column in SharePoint - then you have the data as the correct data type in SharePoint (since start & end time aren't actually just times - they do occur on a date...)

Re: what is the correct format for submitting time to a SharePoint list

MrWilde — Sun, 19 May 2013 22:42:51 GMT

Thanks again Patrick for a quick response. I always like the fact that I have an issue like this and ask you guys for help and get a reply so quickly. You have confirmed everything for me exactly. I tried using the time field in InfoPath then the dateandtime with only time but no go. I tested a blank Infopath form published to share-point list to see what type it created but I could not replicate the type with Infopath and Qrules. I finally tested using the type format and then submitted to SharePoint list text type. That gives me the XML version of dateandtime stamp, 1899-12-30T09:25:00+10:00, but it works and is easy to play with. thanks again for the advice and quick response. Rob Wilde

Re: what is the correct format for submitting time to a SharePoint list

Patrick Halstead — Sun, 19 May 2013 20:06:54 GMT

Hi MrWilde,

If you're using a SharePoint list form, you don't have the Time only field type option. So, you should choose DateTime type. I'm guessing you did but that you are just using the Time component. This should still not cause a problem since it will insert today's date as the default. I just tried it and it promotes to SharePoint list columns just fine. If you create the columns from SharePoint as "Date Time" InfoPath only sees them as "Date" and not "Date Time" and you won't be able to change the type from InfoPath designer. I guess that's a limitation in the list forms.

In general, for most business process forms, SharePoint list forms are too limited. They don't support many of the controls (including repeating), have no support for extensions, and data can't be easily migrated - to list just a few reasons. But, the biggest issue is that they are limited by SharePoint list limits, so you can't extend them past ~80 columns (+/- 10 because it depends on the data types). They will just refuse to publish.

I prefer using a full-fledged InfoPath form and map to the list using CAML or qRules - the latter is more flexible, less time consuming, and supports roundtrips and mapping to multiple lists. Maybe you are already one of these techniques. If so, that's great. You can use a pure Time field type and map it as a string to your list. That's how the out-of-box sample timesheets worked in InfoPath 2003.

Send email to Support if you have qRules issues mapping time.

Good luck!

what is the correct format for submitting time to a SharePoint list

MrWilde — Sun, 19 May 2013 14:24:11 GMT

I am getting close to the end of my very large time sheet Infopath form and have got stuck on something regarding the times. for calculations I would like the fields to be times in the form so I need to know what are the correct field types to use in the form and in the SharePoint list I am submitting to. I am trying a lot of different field types but keep getting Invalid date/time value. A date/time field contains invalid data. Please check the value and try again.