InfoPath Dev

Hey i'm looking for a solution to the following situation;

for reasons i won't go into i'm unable to initiate this process directly from an infopath form;
so it goes like this;

users are prompted for 5 pieces of info at the start of this process. this information is dropped into an sql table;

is there a possibility to have an infopath form generated with the data from a single record out of this table, and further could i have this form submitted to a sharepoint library.

the latter seems very much straight forward but for populating the data and authoring the xml i'm at a loss. perhaps this is not possible.. but at this time i figure it's possible an ssis package could grab the row's data, create the xml infopath form and save it to a sharepoint library..

impossible yes/no?

Thanks,
Matt

i should add the intended sharepoint library would be a form library for the possibility of filtering shared columns between infopath/sharepoint

Hi Matt and welcome to our forum,
This is a common scenario. InfoPath supports designing a form to submit/retrieve to/from SQL but due to limitations in InfoPath, you have to do this at the beginning when you create your form. That's because InfoPath uses the schema that matches the relational database table that you are submitting to. If you already have a form, you can use a secondary data source to submit and retrieve data from SQL to work around this limitation but it requires more work (and some code). We also sell the Qdabra Database Accelerator product that allows you to easily hook up a pre-existing form to SQL without the need to write code. There are many other features in this suite.

You're right that submitting to SharePoint is straightforward. However, keep in mind that the SQL submit and the SharePoint submit are separate transactions. You'll want to make sure you treat one as the "main store" for your data. And if that submit fails, you should not let the other submit happen. As far as promoted properties go, it's easy to specify fields to promote to SharePoint.

Without using a solution like Database Accelerator (which allows storing the XSN as well as the form in the DB) you'll want to publish your form's template (the XSN) to SharePoint. Include a submit that first tries to submit to SQL and then saves to SharePoint. If the SQL submit fails, you may want to display an error message and prevent saving to SharePoint so that the SQL database does not become out of synch with SharePoint. Of course this is only a problem if SQL and SharePoint are on different machines and there is connectivity issues with one. It could also be a problem if you don't have the permissions set up correct in SQL or SharePoint.

Good luck! Let us know if you have any other questions.