Designing a browser-enabled InfoPath form that can store data in a database - Part3

Here's the last part of this blog post.

We will finish creating the form and we will test it.

 

Now create another section named DiscountData, insert a table with title and another table with three columns and four rows as follows:

 

startDate and endDate are date pickers controls, discount rate is a textbox.

This section will contain the discount data that will be stored to the database via our web service.

To make this form more "robust" you could set the visibility of this section to depend on the shopID value: in the Display properties of the discountData section click on conditional formatting and add a rule that hides this section if shopID field is blank.

To send data to our web service, we will configure the “Save!” button to do our job but first we need to create the data connection and store it to the data connection library in SharePoint.

Now in the data source section choose manage data connections and add a data connection. The Data Connection Wizard will start: choose “Create a new connection to”->“Submit data” then click “Next”. Select “To a Web Service” and then click “Next”. Now insert the location of the web service: https://youservername/sql?wsdl and then select the name of the webservice (in our case StoreDiscountData) and click Next. Now you will be able to bind the required parameters to the controls in our InfoPath form: s0:shopID to our shopID textbox and so on (see below).

Now click Next, give a name to this new connection, click on Finish, and OK until you will close all open dialogs. Now, like we did for the other data connection save this data connection to our data connection library, delete the one in the form and re-create it adding it from the sharepoint data connection (you will have to re-bind the parameters to the ones in our form).

Now double click on the button "Save!" and add a rule with an action to submit using our newly created data connection. 

Back to the Rule window: we want the form to be closed after the data is stored therefore choose: Add Action, and select Close this form (no prompt will be automatically selected as we are using Form Services forms). Click OK until you close all windows.

We also need to save this form in a SharePoint document library in order to make it editable with a browser using Form Services.

If you haven’t saved the form so far, then it’s time save the form template locally.

Now select File->Publish and choose “To a SharePoint server…” and click Next. Insert the URL of your SharePoint and click Next. In the next step check the “Enable this form to be filled out by using a browser”, select Document Library and click Next. Select Create a new document library and click Next. Give the document Library a name (like “Discount Forms”) click Next and select the columns you will want to promote as Document Library columns (i.e. you will see the data in each form exposed as columns in the newly created document library). Click Next and click Publish. If everything is OK you will be noticed your form has been successfully published to your SharePoint. 

We are not done yet: we also want to specify the form name therefore we will add another rule to our Save button: double click on the button, click on Rules->Modify->Add Action. Select submit using a data connection and select Add. The data connection wizard will start again, and again we will choose Create a new connection to Submit data and then click Next. Choose “to a document library on a SharePoint site” and click Next. Now insert the URL of the document library where we have just published our form and edit the rule that will generate the name of our form – in my case: concat(shopID, "_", concat(startDate, "_", concat(endDate, "_", discountRate))). Click Next and Finish. Click OK until all windows will close.

Save the template and re-publish the template to our document library (it should be just a Next-Next, this time choose “update the form template”).

One final step: in case you want the form to only e filled out with a browser:

Go to the Document Library where we published our form template. Select Settings->Form Library Settings and click on advanced settings: click on “display as a web page” and click OK. Now every time you will click on “New” the form will open directly in the browser.

Now we are

Now we are lef with testing the form.

Go to back to your document library and click on New. the form should open in the browser.

Insert some data and click on the Save button: the form is saved to SharePoint and the discounts are saved in our Discount table in SQL Server.