Trying to get the most out of SP2010 (BCS) out-of-the-box solution

Struggling to decide whether you can use the out-of-the-box Business Connectivity Services of SharePoint 2010 or to build a custom BCS implementation? Through this blog I would like to share my list of lessons learned and tips about using the out-of-the-box SharePoint 2010 BCS solution.

The most recent unexpected behavior I experienced is the behavior of default values in a BCS on an Info Path form. After this example you can find my personal list of lessons learned about BCS out-of-the-box solution.

Unexpected behavior of default values in Info Path
While developing my latest BCS with an Info Path form for data entry I created default values for two columns called “added on” and “added by” a textbox. (See screenshot below)

Later on I found out that the user did not need to see/or fill them and removed the textboxes from the Info Path form. My expectation was that the values for these columns would no longer be written to the database because I removed the entry boxes. But this was not the case, the defaults are set on the column binding of the textbox. (See screenshot below)

So if you don’t want this data to be sent to the database, be sure to remove the defaults from the data field. After seeing this behavior I have used it several times for filling default values without using the fields within the form or settings on the database side.

Overview of lessons learned/tricks:

When I use BCS I try to use the out-of-the-box solution when possible before creating a custom BCS implementation. Because most of the time it is (or seems like) just a “Rather simple” data visualization/entry/manipulation. (With custom implementation I mean writing the stored procedures yourself and/or creating the BCS via coding in visual studio)

This is my list of Lessons Learned/tricks that I like to share:

   Be aware that default settings are set to the data fields (The column binding on the textbox or object in Info Path) and not to the textbox fields itself. So when the default is set and the field is removed the default settings still apply;
    Check the limitations of BCS before developing (especially the data type limitations here, personally had a problem with the data type “BigInt” in BCS and this one is not part of the list);
    Check the limitations of the External lists and see if the business requirements can be met with the external list. (Good overview can be found here or Google on the specific terms “BCS External List Limitations”);
    Use the namespace to cluster the corresponding BCS External Content Types;
    BCS could be used to replace certain Excel input files that are loaded to the Data Warehouse. With the BCS you are more in control of what kind of data can be filled in and the format is always the same. Because we all know what can happen in Excel….;
    Use BCS for manual input of data that is used in multiple reports that require immediate result (example exclusion lists, hierarchies in the DWH, calculation methods used within reports etc.);
    Do not set the security on the BCS components for everybody except yourself until you are done (can save you a lot of time setting the security every time you change the BCS during developments);
    Make sure you check the input of not nullable text fields of a database when it is filled from a form. Because when I did not check the input and updated a not nullable column with a “ ” it saved the record but when reopening the form it gives an error.