Sharepoint/BCS/Infopath Bug/limitation

mikeleenz — Thu, 09 Dec 2010 22:48:08 GMT

Hi, I wanted to share a bug we have encountered just incase anyone else is straching their heads

Below is dialogue sent to/from Microsoft with my own summary at the bottom. Any feedback and thoughts are welcomed

Cheers
Mike

BACKGROUND We have a SharePoint 2010 public facing farm (1WFE , 1 SQL) which has been setup to transition from our 2007 environment.I have configured the following services to enable using external lists in the farm: BCS, SSS The Separate SQL database resides on the SharePoint 2010 SQL Server All servers are Windows 2008 R2 and SQL is 2008R2 Client side is Office 2010 When an external list is created the connection and authentication complete with a successful connection to the SQL datasource. This specific database has many field types which includes string, datetime, integer, decimal etc. When viewing the external list in the External List UI all fields in the database render and function on the screen. We have an additional standard forms library where we have an InfoPath publishing to. Connecting the InfoPath form directly to the Database everything functions as expected and Decimal type fields are available. We are wanting to provide a codeless UI without the need for VPNs to expose the SQL database. For the “codeless” Submit to SharePoint we would be using qDabra’s qRules product. Testing with this product has proven it will meet our needs if we can see the required fields. ISSUEIf the list is left editable without using InfoPath as the editor, editing can occur successfully for fields and calculated fields calculate correctly via the built-in SharePoint list item data entry UI. If the option is activated to create an InfoPath form for the external list editing, data editing is completely broken via the InfoPath form. Using our separate InfoPath form (publishing to its own library) and create a secondary connection to the External list, all fields are displayed EXCEPT any which have an underlying type of System.Decimal. I did testing around adding every number type field to an SQL 2008 R2 table (Incl. Integer, Float, Numeric, Decimal, Currency etc. ) and the only number types I can view via the external list are Int and Float. The table I am using has many int, datetime, string and decimal fields. Each field type functions whether they are calculated fields or not. The only common fault to each field not showing (that I could identify via my testing) was fields with an underlying System.Decimal field type.

Microsofts response after the Sharepoint team recreated the fault and sent it on to an Infopath Product manager

this limitation is by design.

By my understanding, InfoPath and SharePoint can work correctly with Double values, but we cannot support values that go beyond 14 digits of precision. You should consider returning either Double or String values from your query, rather than Decimal, if you need to use the decimal type in the database. For example, you can create a view or sproc that executes a CONVERT on the data, or you can use a web service or .NET shim to mediate between the data source and the external list.

My thoughts on the Matter

This can't surely be a limitation by design for the following reasons.

If you have a BCS list with Decimal datatypes, you still have the option in Sharepoint to "Edit Forms" for the list from the ribbon bar. If you do this then you break your list. DO NOT click the option (inSharepoint designer when you create a BCS list) to create infopath forms for the list then the list will be broken from the get go.
If it is by design, why can you create a form successfully when connecting direct to a database (SQL etc)
Sharepoint itself (if you view the list settings and field types) correctly recognises the system.decmal based fields and list interaction via the web GUI functions perfectly.

Come on Microsoft. Please look at a fix as you actively in all your demos use the "Expense Claim Form" template and what development team in their right mind would have any financial numerical data using a float field type???

I actively use Sharepoint and infopath everyday and overall I am imressed with 2010. I hope Microsoft take my comments in a positive manner because that is how they are meant. High volume users are not going to accept "By Design" as an acceptable solution when the logic is plainly flawed.

Take care for the season ahead.

SharePoint Integration

Sharepoint/BCS/Infopath Bug/limitation