One of my current projects involves integrating a client's database into an overall CRM system design.
Part of the process was updating their legacy database and porting it over to SQL Server. That's been done without any problem.
We need to be able to provide data input and display for this database within Sharepoint. I've created External Content Types for each table, and set permissions in BCS properly to be able to display and modify the data. The auto-created External Lists work like a charm for those simple operations.
My problem stems from my attempt to customize the table input forms. The external database is properly architected, and has relationships set up to ensure data integrity. When I created the External Content Types, I added in the proper associations to mirror the relationships in the database.
The auto-generated Infopath forms adds External Item Pickers for these associations, so I know they're set up correctly. I want to be able to convert these into dropdowns, however; it makes it easier on the user, and less pop-up screens is always a good thing.
Anyone familiar with Infopath and external lists knows where I'm going with this. Adding extra data connections to an external list form doesn't work; when attempting to publish the form, the Design Checker comes back with "Additional data connections not supported".
There are other forms I want to create that will leverage several tables at the same time; this is just the first one I want to do.
All my Googling to work around this issue just comes back with, "Yup, that's the way it works. Sorry." which is singularly unhelpful to resolving the issue.
Migrating the database into Sharepoint is not an option; there are other business requirements that leverage SQL Server functionality.
So, my question is, how can I go about keeping the database operations inside Sharepoint, while keeping the database itself outside of Sharepoint? Am I even going about this in the correct manner?