Platform and Tools: SharePoint Server 2010, InfoPath 2010, SharePoint Designer 2010.
The client wanted to insert data into two tables with SharePoint. for example: Products and Orders, One table has a foreign key relationship to the other table's primary key.
1. Create two database tables on a backend SQL Server database, create an Auto Increment column on Products table as primary key.
2. By using Business Connectivity Services(BCS), Create two External Content Types based on a back end database tables in SharePoint Designer 2010.
3. Create two External Lists based on the External Content Types in SharePoint Designer.
4. Design Form in InforPath: Select the created External List, start from here, see picture:
put a submit button, add two rules: 1. Submit data to main data connection. 2. send data to web part.
For Orders: Add a field named "ProductID",then File -> Info -> Form Options -> Property Promotion, add the foreign key field in datafields and the newly added field "ProductID" to the bottom list. put a submit button, add 4 rules: 1. Submit data to main data connection, 2. set a field's value: EIR="", this is very important, Later on when user enter data by InfoPath Form web part, after submit, it will step to next record for enter automatically. Otherwise it will stay on same record. 3. set a field's value: ProductID=foreign key field name, 4. send data to web part. Of course, we want to see the data entered once we submit it. New a blank form, add a new data connection to Orders External list. we have already created, drag and drop the data fields to the form as a repeating table, add a new field "ProductID" under Main data connection data fields, add two rule on this field's this field changes event: 1. Set a field's value, select the foreign key field from queryfields, set the value as field "ProductID" value. 2. Query using a data connection: Orders.
We can create one more InfoPath form to display and filter Products data entered in the same way above.
Publish above forms to the Document Librarys on the site you want.
5. On SharePoint site, add InforPath Webpart to webpart page. See picture:
Create a webpart page, add three or four InfoPath Form Web Parts on the same page from top to bottom by selecting the right library from the tool pane: Products new item form, Orders new item form, Orders display form, or Products display form. Then create web part data connections, Orders new item should get data from Products new item web part, connect Products primary key field to Orders foreign key field. Orders display form connect ProductID field to Orders foreign key field.
There is a trick here. We have already created Products and Orders External Lists, it is supposed to use Products new item web part connection to Orders External List web part to filter data, but the reality is that Orders External List web part is always empty,I have not found the reason or explanation so far. That's why we use InfoPath form repeating table to display the filtered data.
