Getting Data from DB to Form Items

Consider providing a Combo Box for the list of Debit Account No and Beneficiary Account fetched dynamically from database. This can be done using the data-support class.

In the Payment Form shown in the previous screenshot, perform the following steps:

Delete the Textbox "TXT_DBIT_AC_NO".
Insert a Combo and put the Item ID as "TXT_DBIT_AC_NO".
Create a data-support class implementing the Canvas IAdditionalDataSupport class and retrieve the data using a SQLMap.
Map the data-support class path with Form. The data-support class must be located within the implementation team's application source code.

Now, create a data-support class that implements the following Canvas class:

import com.intellectdesign.canvas.formdefinition.addinfo.IAdditionalDataSupport

Assuming the data-support class name is PaymentDataSupport.java,

package com.intellectdesign.modelhouse.dataSupport; 
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map; 
import com.intellectdesign.canvas.database.DatabaseConstants;
import com.intellectdesign.canvas.database.DatabaseException;
import com.intellectdesign.canvas.database.DatabaseRequest;
import com.intellectdesign.canvas.database.DatabaseResult;
import com.intellectdesign.canvas.formdefinition.FormDefinitionException;
import com.intellectdesign.canvas.formdefinition.FormItemDefinition;
import com.intellectdesign.canvas.formdefinition.addinfo.AdditionalDataCodeValue;
import com.intellectdesign.canvas.formdefinition.addinfo.IAdditionalDataSupport;
import com.intellectdesign.canvas.value.IUserValue; 

public class PaymentDataSupport implements IAdditionalDataSupport
{ 
	/* This method is invoked by the Form Manager to provide a hook for the data support 
	*  to handle the data fetch for fields that need additional data. This method is 
	*  intended to provide a list of all the supported data that is needed for the Form Item 
	*  whose definition is passed as a parameter to this method.
	* 
	*  @param itemDefn - FormItemDefinition that contains the items which need additional data 
	*  @param userValue - UserValue object containing logged on user details 
	*  @param inputParams - Hashmap of cached client input parameters 
	*  @return ArrayList<AdditionalDataCodeValue> - Parameters for AdditionalDataCodeValue are Data Code and Display Value.
	*  @throws FormDefinitionException is thrown if any errors/exceptions while getting Additional Data.
	*/ 
	
	@Override
	public ArrayList<AdditionalDataCodeValue> getAdditionalDataFor(
            FormItemDefinition itemDefn, IUserValue userValue, HashMap inputParams) throws FormDefinitionException
	{
		ArrayList<AdditionalDataCodeValue> resultList = null;
		DatabaseRequest dbRequest = null;
		DatabaseResult dbResult = null;
		List tmpList = null;
		String key = null;
		
		try
		{
			//Create a DB Request to fetch the Account No from DB to the combo 
			dbRequest = new DatabaseRequest();
			dbResult = new DatabaseResult();
			tmpList = new ArrayList();
			resultList = new ArrayList<AdditionalDataCodeValue>();
			dbRequest.setDataSource(DatabaseConstants.DEFAULT_DATASOURCE); 

			//Getting the data for the Debit Account No combo
			if (itemDefn.getItemId().equals("ACC_NO"))
			{
				dbRequest.setOperation(DatabaseConstants.SELECT); 
				// Actual result map key, which contains the SQL to be executed
				dbRequest.setDataAccessMapKey("FUNDTRANS");
				dbRequest.setOperationExtension("ACC_NO");
				dbResult = dbRequest.execute(); 
				key = "ACCOUNT_NO"; 
				tmpList = dbResult.getReturnedList(); 
			} else if (itemDefn.getItemId().equals("BENE_ACC_NO"))
			{
				dbRequest.setOperation(DatabaseConstants.SELECT);
				dbRequest.setDataAccessMapKey("FUNDTRANS");
				dbRequest.setOperationExtension("BENE_NO");
				dbResult = dbRequest.execute();
				key = "BENE_ACC_NO";
				tmpList = dbResult.getReturnedList();
			}
			for (int i = 0; i < tmpList.size(); i++)
			{
				Map tmpMap = (HashMap) tmpList.get;
				resultList.add(new AdditionalDataCodeValue((String) tmpMap.get(key), (String) tmpMap.get(key)));
			}
		} catch (DatabaseException dbExcep)
		{
			throw new FormDefinitionException(dbExcep);
		}
		return resultList;
	}
}

The following method can be used from the FormItemDefinition class:

getItemId - This method returns the form item ID.

The following methods can be used from the IUserValue class:

getUserNo - This method returns the user number of the current user.
getLoginId - This method returns the login ID of the current user.

For more information, refer the Java documentation of the IUserValue interface. You must download the JavaDoc ZIP and open the index.html file.

The getAdditionalDataFor method must return a list of AdditionalDataCodeValue objects. The AdditionalDataCodeValue has the following types of constructors:

AdditionalDataCodeValue(List valArray)
AdditionalDataCodeValue(String codeKey, String codeValue) - the first parameter is the display value and the second parameter is the code.

Now, create a Select SQL result map in SQLMap file to get the data from the database for the access map key.

Assuming you want to add the SQL in the same PaymentSQLMap.xml file, add the SQL result map (FUNDTRANS) as follows:

<! Debit Account Number selection SQL Extension value as SELECT_ACC_NO---> 
   <select id="FUNDTRANS_SELECT_ACC_NO" resultMap="FUNDTRANS_SELECT_ACC_NO_RESULT_MAP">
		select ACCOUNT_NO from ACCOUNT_SUMMARY_DATA
   </select>
 
<! Debit Account Number selection SQL Extension value as SELECT_ACC_NO---> 
   <select id="FUNDTRANS_SELECT_BENE_NO" resultMap="FUNDTRANS_SELECT_BENE_NO_RESULT_MAP">
		select BENE_ACC_NO from BENE_DATA where PAYMENT_TYPE='Domestic Fund Transfer'
   </select>

Similarly, replace the Beneficiary Account No text box with Combo and get the Beneficiary Account No for the combo in the same data-support class.

After adding, map the data-support class path with the Payment Form as com.intellectdesign.modelhouse.dataSupport.PaymentDataSupport.

Build your sources and run the application. The form must appear as shown in the following screenshot:

Getting the data for other items using AJAX Request

You can use the form and form item events to get the data dynamically for those controls that cannot get data using the data-support class.

For the given example, we can display the Account Names dynamically on change of Account No combo boxes. This can be achieved through AJAX requests.

In the sample listener class payments.intertransfer.js created for the Payment Form, add the following handlers:

registerHandlers : function ()
{
	this.fm.registerHandler("CFEC.CHANGE", "TXT_DBIT_AC_NO", function (fm, event, fieldName, value)
	{
		if(!canvas.isEmpty (value))
		{
			var paramsOb =
			{
				"INPUT_ACTION" : "GET_ACC_DTLS",
				"INPUT_FUNCTION_CODE" : "VSBLTY",
				"INPUT_PRODUCT" : "PAYMNT",
				"INPUT_SUB_PRODUCT" : "BKSIFT",
				"PAGE_CODE_TYPE" : "ACC_CODE",
				"PRODUCT_NAME" : "PAYMNT",
				"ACCOUNT_NO" : value
			};
			canvas.ajax(
			{
				params : paramsOb,
				success : function (result, request)
				{
					fm.model.setValue('TXT_DBIT_AC_NAME', (response.ACC_NAME||
					response.ACCOUNT_NAME||""));
				},
				scope : this
			});
		}
	});
}

Similarly, create a change event for the TXT_BEN_AC_NO combo and get the data through Action Class > Handler Class > Instruction Class > SQL Map from the database.

Alternatively, there is another method to fetch data for Combo form items through View ID and Column ID. For more information, refer the "Settings menu" row in the table for Combo form item configuration.