Qdabra Database Accelerator

Re: InfoPath "date" field includes date/time when promoted to SharePoint forms library

Jim Cantwell — Wed, 16 Nov 2011 18:45:25 GMT

DBXL v2.6 will probably be released in January.

Re: InfoPath "date" field includes date/time when promoted to SharePoint forms library

DC-jeff — Wed, 16 Nov 2011 14:51:07 GMT

Thanks Jim. What's the expected release date for 2.6?

Re: InfoPath "date" field includes date/time when promoted to SharePoint forms library

Jim Cantwell — Tue, 15 Nov 2011 17:03:55 GMT

This is a known bug that has been fixed in v2.6, which is still in beta. Please email support at qdabra.com with the version of DBXL you currently have and we will look at getting the fix applied to that version.

Re: InfoPath "date" field includes date/time when promoted to SharePoint forms library

DC-jeff — Tue, 15 Nov 2011 16:02:13 GMT

SharePoint does use the DateTime field type when the date is promoted. The issue appears to be with the DBXL/SharePoint integration....

If I create an IP form, promote the date field and publish to a SharePoint library via the IP client, the date field correctly displays without the time as desired - 11/15/2011

If I create an IP form, promote the date field and have DBXL publish to a SharePoint library via the DAT, the date field includes a default time - 11/15/2011 12:00 AM

In SharePoint, the presense of the default "12:00 AM" time is unnecessary for my application and is confusing to users. So, is there a workaround in DBXL that will prevent this from happening?

thanks

Jeff

Re: InfoPath "date" field includes date/time when promoted to SharePoint forms library

Jimmy — Tue, 15 Nov 2011 15:33:05 GMT

To the best of my knowledge, SharePoint promoted columns always use DateTime rather than just Date or Time. I don't know much about SharePoint's internals, but my guess would be that it's because SharePoint also uses an SQL Server DateTime column to store the data.

So I don't think there's a way around having the data come back that way, but why is it a problem?

Re: InfoPath "date" field includes date/time when promoted to SharePoint forms library

DC-jeff — Tue, 15 Nov 2011 15:13:32 GMT

Ernesto -

Here's the full detail. I understand the SQL side as SQL 2005 only has a DateTime data type rather than date which will store dates as 2011-11-24T00:00:00. The SharePoint side is a mystery to me.

InfoPath Configuration

Field Name: GoalDate
Date type: Date (date)
Configured as promoted field

bound to Date picker Control
Data type: Date (date)
Format: Display date like *3/14/2001

DAT Configuration
DB Type for field configured as DateTime

XML Output
<my:GoalDate>2011-11-24</my:GoalDate>

when queried via queryDB, the AT_Goal_date field (mapped to /my:GoalDate via the DAT) is displayed as 2011-11-24T00:00:00

<row>
<DBXL_Id>888</DBXL_Id>
<AT_ActionItem_text>Nov 15 test item</AT_ActionItem_text>
<AT_Summary_text>Nov 15 test item</AT_Summary_text>
<AT_ActionPOC_name>Jeff Sopko</AT_ActionPOC_name>
<AT_Goal_date>2011-11-24T00:00:00</AT_Goal_date>
<AT_Priority_desc>(2) Normal</AT_Priority_desc>
<AT_Team_name>ASB</AT_Team_name>
<AT_OSSItem_flag p3:nil="true" xmlns:p3="http://www.w3.org/2001/XMLSchema-instance" />
<AT_ItemType_code>OSS Action Item</AT_ItemType_code>
<AT_Completion_date p3:nil="true" xmlns:p3="http://www.w3.org/2001/XMLSchema-instance" />
</row>

SQL 2005 table configuration

CREATE TABLE [oss].[SSPO_ActionTracker](
      [SSPO_Id] [int] IDENTITY(1,1) NOT NULL,
      [DBXL_Id] [int] NULL,
      [SSPO_ActionItem_text] [varchar](1000) NULL,
      [SSPO_Summary_text] [varchar](1000) NULL,
      [SSPO_ActionPOC_name] [varchar](150) NULL,
      [SSPO_CurrentStatus_desc] [varchar](75) NULL,
      [SSPO_Goal_date] [datetime] NULL,
      [SSPO_Status_desc] [varchar](75) NULL,
      [SSPO_Priority_desc] [varchar](75) NULL,
      [SSPO_Team_name] [varchar](100) NULL,
      [SSPO_OSSItem_flag] [varchar](3) NULL,
      [SSPO_ItemType_code] [varchar](25) NULL,
      [SSPO_Completion_date] [datetime] NULL,
      [SSPO_FormLink_code] [varchar](150) NULL,
      [SSPO_Update_id] [varchar](50) NULL,
      [SSPO_Update_date] [datetime] NULL,
      [SSPO_Create_id] [varchar](50) NULL,
      [SSPO_Create_date] [datetime] NULL
) ON [PRIMARY]

SharePoint 2007 Configuration

Column: Goal Date

Type: Date and Time (uneditable since created through IP field promotion)

Displays as "11/24/2011 12:00 AM"

thanks

Jeff

Re: InfoPath "date" field includes date/time when promoted to SharePoint forms library

ErnestoM — Tue, 15 Nov 2011 12:46:39 GMT

Hi Jeff-

What does the mapping look like in DAT? There is a Data Type dropdown in the Database tab; I'm curious to see what that is set to. I'd also like to know the column data type in SQL.

SharePoint is another story. Let's check the SQL side first. :-)

Thanks.

InfoPath "date" field includes date/time when promoted to SharePoint forms library

DC-jeff — Mon, 14 Nov 2011 17:49:33 GMT

I've got an IP form that includes a basic date field tied to a date picker control. I submit the form via DBXL which then shreds it to SQL and also a SharePoint forms library. Despite having configured the field as a date and specifying that it should only include the date in the display format, it shows up in SharePoint as the date + time (i.e. 11/11/2011 12:00 AM). Also in SQL it appears with a time (i.e 2011-11-11T00:00:00). I've checked the form xml and confirmed that the IP field is only storing the date (<my:GoalDate>2011-11-16</my:GoalDate>)

I don't recall ever having this issue before submitting forms via DBXL. How do I get rid of the time? Assistance appreciated.

thanks

Jeff