InfoPath Dev

I am working with Sharepoint 2007, Infopath 2010, and Access 2010. I have many sources that need to provide data input to our Access database, which, unfortunately, has to reside in a secured location that only a few administators have access to. Our proposed solution is to use several sharepoint lists as a front-end, since all users have access to sharepoint. We then want to use the infopath forms to pull the data from Sharepoint so that we, as administrators, can review the data, click submit, and have it all be loaded into the database. (Yeah, we know, if only we had the sharepoint offfice integration services, or even Sharepoint 2010, this would be a lot easier and we could skip the infopath step. Since neither of those are available, we haven't found an easy way for SP and Access to play nice with each other). It seems like it shouldn't be that hard to bind one text box to the SP list to pull the values, then have a 2nd text box, set the value equal to the first box, bind it to the database to submit the values. For one value, it works just fine. However, we are having difficulties mapping the repeating values to the database. Hopefully I can explain: In the most simple test case, I've created one Sharepoint list - Person - with columns "SP_Last_Name" and column "SP_First_Name" and one access db - Table "Person", fields "AC_Last_Name" and "AC_First_Name". In my infopath form, I have a query data connection pulling the data from the sharepoint list, and a submit data connection to my Access database. I've created a repeating section (Bound to the SP list) which contains a text box bound to "SP_Last_Name". I preview the form, and it displays all the last names in the list. Step One Success. I then add a 2nd text box to the same repeating section "AC_Last_Name" and I set the value function to equal "SP_Last_Name". However, I get the error message "You have chosen to bind a non-repeating control to a repeating text group." The text box is only bound to the first item in the list. I then preview the form, and I see that my query fields still show all the values from SP, but my submit fields only display the 1st entry repeated over and over again. I'm sure there's more info I should share, but I'm still rather new to Infopath. So I'll stop here. I've tried to do a lot of searching to find an answer, but haven't found anything yet. It seems like I'm just missing something simple to make the bridge. Any suggestions would be greatly appreciated.

Hi Tedsec and welcome,

I'm not sure that SharePoint 2010 would help you a whole lot. InfoPath to SQL is a major gap for SharePoint. BCS is still not a good solution because it creates a tight binding. The reality is that forms change a lot and there needs to be an abstraction layer (Web service) to handle the mapping when things change.

Disclaimer: we use SQL here for all of our InfoPath forms work. SQL Express is free and has more functionality than Access.

Storing data in SharePoint is effectively storing it in SQL since SharePoint uses SQL behind the scenes for all data storage. SharePoint lists and form libraries are represented as tables in SQL and therefore they don't support repeating structures. In other words, you can't submit a repeating group in InfoPath to a SharePoint list nor can you promote those values to a SharePoint form library.

Regarding your database submit ... back in 2004 we created a Web service called DBXL that maps repeating structures to SQL after-the-fact. In other words, you can take a stock InfoPath form with an XML data source and create a mapping to SQL. No code required. As you can imagine, this has been very popular (MS bought 8 licenses...) and we continue to sell this "accelerator" even now, 7 years later, because nobody has bothered to come up with an alternate solution (boggles my mind). The accelerator is inexpensive and can be used for all of your forms. If you aren't completely satisfied, full money-back guaranteed, etc. Lots of happy people use this every day and while it is middleware (impossible to effectively market) I'd be happy to show you a quick demo or you can go to our Web site and watch the videos, etc. Bottom line: it's way cheaper than trying to do this in code yourself and it will work for all forms down-the-road.

Now, I'm sure I don't understand your scenario well because I am human... so, let's say I am totally off and you could care less about submitting to a database and what you are really concered with is the query of the SharePoint lists ... if so, maybe you need to copy the data from your secondary data source to a properly repeating main data source. That requires code. Maybe you are an avid coder but best practice is not to write code for every form. So, four years ago, we developed this plugin called qRules which is also a free download for a trial version, which basically includes all of the features in InfoPath that people regularly request but for which code is required. One of the most requests is copying data from one table to another. You'll need to do that to get it in a format that is propery structured for submit to Access. qRules is a simpler thing since it's client-side only (no server install) and it's even cheaper plus you get Qdabra's world class support and you can feel good about returning some love to the sponsors of this forum (this is a total loss leader for us).

Or, you can just pay someone hourly to investigate, but my #1 hunch is that you have a data source shape issue on submit and my #2 admonition is that you should use a Web service for this kind of thing. 

Best of luck!