PerfectXML.com: Learn to use XML in your applications

3.1 Start Microsoft InfoPath 2003, Click on File | Design a Form... and select New Blank Form from the task pane.

3.2 From the Layout task pane, drag and drop Table with Title on to the form.

3.3 Change the title to New Loan Application and description text to Sample InfoPath solution that connects to Oracle via .NET Web service.

3.4 Click on Format | Color Schemes... and choose a color scheme that you like for the top title table.

3.5 From the Layout task pane, drag and drop Two-Column Table on to the form.

3.6 In the above two-column table, first row first column, write Applicant Name and in the next column drag and drop a Text Box control from the Controls task pane. Resize the row hight accordingly.

3.7 Right click on the text box and select Text Box Properties..., change the field name to FullName and check Cannot be blank checkbox. Click OK.

3.8 Press tab to add a second row. On second row first column, write Property Value. Drag and drop text box control on the adjacent column. Right click on this text box and select Text Box Properties... and change the field name to PropertyValue, change the data type to Decimal (double), click on Format... button and change the format to Currency, press OK. Check Cannot be blank checkbox, click on Data Validation button and click Yes on the message box, click Add... and specify a condition that Property value must be a positive number above 25000 (just for example).

3.9 Press tab to add third row. Write Down Payment in the left column. On the right column, drag and drop yet another text box control. Right click on this text box, select Text Box Properties, change the field name to DownPayment, data type to Decimal (double), click on Format button, and set it to Currency. Check Cannot be blank and then click on Data Validation and add a condition that if DownPayment is greater than or equal to PropertyValue, then display an error message.

3.10 Add one more row. On left column, write Rate, and on the right column, drag and drop a Drop-Down List Box and a Rich Text Box below the select combo box.

We are going to bind this Drop-Down List Box (select combo box) with the one of the Web service method that returns the mortgage rates. And when one is selected, we'll display the rate details in the Rich Text Box below the combo box.

Right click on Drop-Down List Box, select Drop-Down List Box Properties, change the field name to RateID, data type to Whole Number (integer), check Cannot be blank, and select the radio button that says "Look up in a database, Web service, or file".

We'll add the Web service method as a secondary data source. You can do it from here or upfront from the Tools | Secondary Data Sources menu item. Let's do it from here in this example.

After clicking on Secondary Data Source... button, click Add..., and select Web service as the source, click Next. Type the Web service WSDL URL; for example, on my machine it is http://localhost/OraDataService/HomeLoan.asmx?wsdl, click Next. Select one of the GetRates_XXX methods, for example, GetRates_UsingMSProvider, click Next thrice, leave the data source name as GetRates_UsingMSProvider and click Finish, click Close. Click on XPath expression icon

next to Entries text box and select Table node under NewDataSet. Click OK.

Make sure that the Value in the following dialog is RateID and change the Display name to Description.

3.11 Just to make sure that the combo box is populated with the data from Web service, click on Preview Form and see if combo box contains three entries. Note that for the very first time you preview the form, it might not work and report a timeout error. This is because the ASP.NET compiles the Web service code and takes longer than subsequent requests; and InfoPath thinks that the Web service method timed out. At this stage, if you click on the Preview Form button again, you should see a screen similar to one shown below:

3.12 In step 3.10, we added We added a rich text box below the Mortgage Rate combo box. Lets now populate this text box with the rate detail when the selection is changed from the Mortgage rate combo select box.

Right click on rich text box below rate combo box and select Rich Text Box Properties.... Change the field name to RateDetails; click on Display tab and check Read-only and click OK.

Right click on RateID drop down list and select Properties; click on Data Validation button and select the OnAfterChange from the Events list and click Edit...

Clicking on the Edit... button on the above dialog box will start the Microsoft Script Editor; write the following code under msoxd_my_RateID::OnAfterChange(eventObj):

function msoxd_my_RateID::OnAfterChange(eventObj)
{
   if (eventObj.IsUndoRedo)
   {
      // An undo or redo operation has occurred and the DOM is read-only.
      return;
   }

   //   Find out the current Rate ID
   var selectedRateID = XDocument.DOM.selectSingleNode("/my:myFields/my:RateID").text;
   if(selectedRateID.length <= 0)
   {
      XDocument.DOM.selectSingleNode("/my:myFields/my:RateDetails").text = "";
      return;
   }
   
   //   Select the Table node matching the currently selected RateID
   var rateNode = 
      XDocument.DataObjects("GetRates_UsingMSProvider").DOM.selectSingleNode(
         "//Table[RATEID='" + selectedRateID + "']");
      
   //XDocument.UI.Alert(rateNode.xml);
   
   //   Get other rate details and show it in the Rich text box
   var closingCost;
   if (rateNode.selectSingleNode("CLOSINGCOSTINCLUDED").nodeTypedValue == "1")
   {
      closingCost = " is ";
   }
   else
   {
      closingCost = " is not ";
   }
   
      
   XDocument.DOM.selectSingleNode("/my:myFields/my:RateDetails").text = 
      rateNode.selectSingleNode("DESCRIPTION").nodeTypedValue + 
      " at " + rateNode.selectSingleNode("RATE").nodeTypedValue + "%. " + 
      rateNode.selectSingleNode("POINTS").nodeTypedValue + 
      " points. Closing cost " + closingCost + "included.";
}

Preview the form once again; change the mortgage rate selection, and as you change the selection in the combo box, you should now see that the rich text box is populated with other details about the selected mortgage rate.

Note that make sure to change/update the secondary data source name (GetRates_UsingMSProvider) in the above example, if you use a different Web method or give a different name to the secondary data source.

3.13 Return to the form design mode. Add two more rows. The label on first newly added row should read "Is Rate Locked?" and on the second row, the label on the left column should read "Is Pre-approved". Add two combo select boxes on each row, name them as IsRateLocked and IsPreApproved and add two elements under each select combo box with display text as Yes and No having values as 1 and 0, respectively. Make sure to change the data type to integer.

3.14 Drag and drop a Button control, right click on it and select Properties... Change the button label to be Submit and Action to be Submit. Changing the action to Submit will popup the following dialog box (or click on Define Action Parameters... button):

Select Enable Submit and make sure Submit to a Web service is select as a method, and then click on Select a Web service button.

Type the WSDL URL of the Web service that we created earlier (for example, as http://localhost/OraDataService/HomeLoan.asmx?wsdl on my machine). Click Next.

Select AddApplication_UsingODP Web method. Click Next.

On the Parameter mapping panel, click on Modify... button and make sure that parameters are mapped as shown in the following screenshot:

Click Next, then Finish, and then click OK twice.

The form and backend is all ready to be tested. Click on the Preview Form toolbar button, enter some values and click on Submit.

Goto Oracle SQL*Plus and run SELECT * FROM tblApplications; and make sure that the data that you just entered in InfoPath form is present in the database table.

Microsoft InfoPath 2003 Guided Tours: InfoPath and Oracle

Abstract

Getting Ready

Steps

Summary