Lookup List and return data from within a Form

Question

OK, I'm beginning to despair at SharePoint...please help restore some faith :)
Seems some very basic things are very hard to achieve?

1:
I have data in an SQL database table. 78,000 rows, but I want to pick just one item, based upon filters.

I've tried External List - but this is too slow (even after having to change the data item limits)

I've tried a Data Source connection to the database - but no matter what connection string I try, I cannot get this to connect (works for External List tho?)

2:
I have created a Content Type that matches the fields I require. This is so I can build a List specific to certain content types that will all share workflows (and many common fields - I will extend the Content Type as appropriate)

3:
I need a New Item Form that allows me to "find" an item from the database/list/whatever and return only certain items from it.
I don't need any kind of referential integrity - just a one off find and return.

This needs to allow filtering/searching of some kind to reduce the number of items returned to a sensible size.
e.g. Enter partial Mobile number and search on that.
e.g. Filter/Search on Name, Mobile No, Customer Number - return these plus Address, Email.

This all seems to be pretty standard functionality - but every time I try something, SP throws another (usually bigger) spanner at me.

Company Policy is to try to use SP native functionality unless there is absolutely NO other way to do things. Although I am a c#/.Net/Web developer for many years I can understand this position...including anything outside SharePoint carries an overhead for maintenance/new staff/skillsets/source code etc, etc.

What seems like a very simple form/workflow business requirement appears to be a massive undertaking to develop? Coming from a coding background - it seems that SP (native) is a very poor platform for anything but the most basic of tasks?

We are using SP2010 Standard, although I am hoping to get permission (budget) for Enterprise in the hope that InfoPath forms will offer some respite?

Answer 1

I simply Created a SharePoint WCF Service and used it to merge sharepoint data and SQL data. I never had luck with BCS or External lists. I don't think it has the capability to handle much data. Here is a link to how to accomplish this.

http://msdn.microsoft.com/en-us/library/ff521581.aspx

Just make sure you access your sql database from here. After you have this service setup you can call it using javascript within your form using custom actions and commanuihandlers. Here is a link on how to use the custom javascript OM.

http://msdn.microsoft.com/en-us/library/ee535709.aspx http://msdn.microsoft.com/en-us/library/hh185009.aspx

You would just call your functions instead of the SP functions.

Answer 2

I also have no luck in using (direct) data connection and BCS to MSSQL Server and resort to web service usage though I am on Sharepoint Online (Office 365).

Also I use Infopath and one can access MSSQL through Infopath Form Sharepoint Service (IPFS, i.e. using Infopath from browser) only through web service, cf.:

• Can I save data in SQL Server using InfoPath?
• Infopath Concept
• Patrick Halstead. InfoPath gaps in SharePoint and SQL

The latter is very specific on a number of issues, also non-Infopath only (Sharepoint/MSSQL) scope, here are some:

• "Using SharePoint Designer to create write-backs to External Content (SQL DB's) is complicated and not recommended for complex data structures"
• "BCS cannot be configured on the fly after an InfoPath form is deployed. You have to convert main data source. Also, BCS requires SharePoint licenses"

Also, from my experience avoid using space and some other symbols like # (sharp) in column and list/library names referenced in lookup columns.