How do I write data from an InfoPath 2007 form to a database
I have an expense form in InfoPath 2007 that have multiple lines of expenses. How do you write that data to the database. Example, I'm turning an expense sheet in with three separate lunch meetings, and 2 trips to separate locations, but it's all on ONE expense sheet. How do you write that record to the database?
ScreenShot.jpg
ScreenShot.jpg
ASKER
Ok, so let's say I don't have a couple a grand to spend. What other ways are there to do this? Are you saying that I can't have the part where I am adding an attachment without using some sort of web service?
Yah unfortunately large file types (attachments, rich text) are not supported in the basic database connectivity. You also cannot query and submit to more then one set of tables, which is often a problem with enterprise type solutions.
InfoPath was ultimately designed to leverage web services and you have the most power and flexibility when you use them. You either have to create them yourself or or purchase them (Qdabra, Sharepoint, Etc).
So that is the bummer is that you would have to completley redesign and the attachment could not be databased.
The upside with InfoPath, SQL, and web services is that you can quickly and easily design, modify, maintain forms and you have the power to build about any enterprise solution very rapidly.
You can download the DBXL for free to do a proof of concept.
Sorry
InfoPath was ultimately designed to leverage web services and you have the most power and flexibility when you use them. You either have to create them yourself or or purchase them (Qdabra, Sharepoint, Etc).
So that is the bummer is that you would have to completley redesign and the attachment could not be databased.
The upside with InfoPath, SQL, and web services is that you can quickly and easily design, modify, maintain forms and you have the power to build about any enterprise solution very rapidly.
You can download the DBXL for free to do a proof of concept.
Sorry
ASKER
You said speaking of web services... "You either have to create them yourself or or purchase them (Qdabra, Sharepoint, Etc).
We have MOSS 2007 and that is what we will be deploying this to.
I envision the user going to an area of SharePoint and clicking New Expense Form then this standard form comes up. It's pulling some data so we can minimize fat fingering certain data. After they are ready they hit submit and instead of putting the data to a form, we write it to a database.
We just thought that we needed a separate database to put all this data to so it would be easier to write reports and potentially automate sending this data straight to the General Ledger and cutting a check. So is there a SharePoint webservice that is already available???
We have MOSS 2007 and that is what we will be deploying this to.
I envision the user going to an area of SharePoint and clicking New Expense Form then this standard form comes up. It's pulling some data so we can minimize fat fingering certain data. After they are ready they hit submit and instead of putting the data to a form, we write it to a database.
We just thought that we needed a separate database to put all this data to so it would be easier to write reports and potentially automate sending this data straight to the General Ledger and cutting a check. So is there a SharePoint webservice that is already available???
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I would recommend Qdabra's Database Accelerator web services suite. www.qdabra.com it is the easiest and fastest way to database enable your form and can put you on the path to all sorts of enterprise InfoPath solutions. Lots of cool features and integrations.
Otherwise, you can do this with standard infopath but it is not real pretty.
First you would need a parent table for reports and a related child table for the expense line items.
Then you would need to start over with a blank form and design it originally as a database form.
You would then get the design and layout back to what you wanted and you could enter an expense and submit it to SQL.
You would not be able to submit the attachments with the base infopath method, not supported, but you can with Qdabra's web services.
Qdarba also has an expense report template that you may be interested in to. I would at least get a demo from them or do some research.