InfoPath form - submit to multiple SharePoint lists

Question

I'm new to InfoPath and SharePoint, so bear with me. I have two lists, a parent list that contains orders and a child list that contains items. The items list has a foreign key that corresponds to the order ID. I can join these two lists into a read-only view just fine, but is there a way to create a new order form that would submit to both these lists?

For example, fields on the order form would include Originator, Delivery Address, etc. and then there would be a repeating data table with fields such as Item Name and Quantity. So the order form would submit to the Orders list, and also to the Items list for each item the user has filled out.

I know I'd probably be better off using a Form Library, but I've been instructed to only use lists. I've done quite a bit of searching and I can find plenty of information on joining two lists in a read-only view but not much on submitting new data to two different lists. Is this even possible, and if so, what's the best way to do it?

Answer 1

You could probably whip something together and fix the lookups via workflow after item creation. Thinking aloud, you'd want to use a submit with rules action. You'd first submit to your orders list and then use some piece of metadata (like a title you build to make it unique) from that to then in turn submit with your repeating item data.

Then on your items list you'd have an on creation workflow that queries your order list to get the list item id of the order with the same title field as the current items metadata and update the lookup column to that id.

I haven't tried it, but it seems as if that should work.

Answer 2

Your case is complicated by the fact that the child items need the ID of their parent in order to be inserted into the list with the lookup field properly set. So the insertion order must necessarily be the order first, then the items. And that's why I don't think this is possible OOB

Answer 3

I have built a shopping cart system that is entirely based on SharePoint lists. An InfoPath browser form uses the qRules add-in from Qdabra software. With qRules it is very easy to write to several different lists from one InfoPath form. qRules is not free but it costs less than a day's developer time and contains awesome functionality for InfoPath.

Answer 4

Sara,

I have done a whole Education system based on InfoPath and utilizing External Lists (fetching from SQL Server Database).. Well my architecture was that I used a WCF based Custom Web Service, and from InfoPath I called that service.. I had repeating tables which I sent to the service in XML format and in the Service I iterate through the XML and insert data into the SQL Server Tables.

The project was quite large and this worked fine for me, instead of SQL Tables you can use SharePoint Server Object Model to add items to the lists and host the Web Service on SharePoint Server.. Or maybe you can host Web Service on a separate server and utilize Client Object Model to add items to the Lists..