InfoPath Dev

I have forms that submit to SQL DB via a web service. One issue I am having is when I right click on my Group in the Data Source task pane I don't have the ability to choose all of the section controls...ie I can't choose Optional Section. If I use the Controls Task Pane I can pull the Optional Section over but can't bind it to the the group I want, it asks to be bound to a control with an "occurance of 0".

Another issue is the fact that I don't have any control over the Data Tab on any of my "section" properties.

Lastly, if I try to convert my data source from Database to Webservice on an existing form there are even more issues, I lose control of the Data Tab on text boxes, check boxes, etc.

Can anyone explain what is going on here and if possible point me in the right direction to fix/correct my problems. If I have to, I will rebuild my existing forms to be used with the webservice but I want to be sure that I can have my current funtionality before I make this change.

It seems that InfoPath allows you to do just about anything if all you are doing is dumping data to XML but as soon as you try to do something useful like pass data to a DB for reporting and storage, you lose some functionallity unless you are a full blown programmer which unfortunately, I am not. The problem seems to worsen with the use of a webservice.

Thanks
Smitty

Hi Smitty,
This is why we developed the Database Accelerator tool (see Downloads->Free). InfoPath binds to Web service schemas in a way that prevents you from modifying the data source. It has to do this to keep the contract with the Web service.

You're best off developing your form's XML schema from scratch (ideally, modelled after a single pivot/table of data) and then setting up a secondary data source (2DS) that connects to your DB or Web service to load. This gives you a layer of abstraction.

You're absolutely right about coding required for most of the stuff that people actually need to do. If you use our Database Accelerator, you can be up and running with SQL in 10-15 minutes and the accelerator includes an InfoPath form that you can use to define the XML->SQL mapping, dynamically, such that you can update the mapping later without breaking the submit.

Let me know if you have questions,
Patrick

Patrick Halstead [InfoPath MVP]
InfoPathDev

This appears to be one of those "Best Practice" issues that I have been encountering as I test InfoPath's various development solutions. Correct me if I am off base, but it seems that using Web Services to pass DataSets to InfoPath encounters significant limitations as described above for reasons to maintain a reusable format for return information. It seems the best method is to create the XML document that will be passed off to the web service to fill and return. Same with return submits where the full control over the XML data and schema remains via InfoPath designer and the web service must be coded to consume and handle the data within the document accordingly. That would seem to be the goal of the DB Accelerator to make this handling of the XML document and the mapping of the data to and from a relational DB. Trying to make sure have an understanding on this subject as we seem to be encountering the same set of issues. Overall to have complete control over the design environment it seems InfoPath must handle the XML document and schema creation and then handle that via additional code on the service end in order to populate or parse data for the DB Please feel free to correct me if I am off in this line of thought.

Hi John,
The big issue is in mapping to the database and keeping data type fidelity. The Database Accelerator solves both problems by allowing the mapping to be done in data and it roundtrips a blob to preserve full data type. In essence, it is exporting the data to SQL tables using the defined mapping but allowing the client to load and save data. SQL is faster than SharePoint and has other downstream functionality (like Reporting, Data Transformation, Notifications, etc.) that can be easily leveraged. In addition the Database Accelerator supports an arbitrary number of form templates and mapping to an arbitrary depth, so it gives you the ability to quickly use SQL to store your forms without having to write any code on the client. If you have an existing database today and need to map to it, you could define the XML schema from the relational one, but that results in many limitations. You're better off - like you said - defining an XSD on the client, and using an abstraction layer - like the one provided by the Database Accelerator - to do your work.

Patrick Halstead [InfoPath MVP]
InfoPathDev