DEVELOPERS FORUM - Populating a form drop down with federated values, please help

Philip - Thursday, January 29, 2015 11:06 AM:

Hello,

I am new to innovator development, so am hoping someone can help me out;

Essentially, What I want to accomplish is equal to this;

Pull the list values from an external database and populate a list on an Innovator form for selection.

What is the best practise and does anyone have a quick example?

I have been trying to call a server method from a client method.  But I have yet to get it working, here is what I have;

//I want to return xml do that I don't have to iterate through the records

strSQL="SELECT Value, label FROM table ITEM order by field1 FOR XML  auto, ELEMENTS";

System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSQL, _conn);

cmd.CommandType = System.Data.CommandType.Text;

System.Data.SqlClient.SqlDataReader _rdr = cmd.ExecuteReader();

string result = string.Empty;

while (_rdr.Read())

{

                result = _rdr[0].ToString().Replace("<ITEM>"," <ITEM type='list' action='add'>");

}

_conn.Close();

Aras.IOM.Item resItem = this.newItem();

resItem.loadAML("<AML>" + result + "</AML>");

return resItem.apply();

DavidSpackman - Friday, February 6, 2015 8:35 AM:

Hi Phillip,

One possible method is to

• Start with a client method on the form you want to load the data on.
• Call a server method that gets the data from your external source.
• Return the result back to the client
• Populate a dropdown control 

A quick example from Aras documentation to populate a dropdown control on a form in Aras. (The dropdown property is called "Vendor List")

This javascript code should be called onFormPopulated for your form

//get Dropdown Control Element
var div_id = getFieldByName("VendorList").id;
var control_id = div_id.substring(0, div_id.indexOf("span"));
var select = document.getElementById(control_id);

//...Replace this with a call to a external datasource...
//get Vendors
var returnItm = document.thisItem.newItem("Vendor", "get");
returnItm.setAttribute("select", "name");
returnItm = returnItm.apply();

//populate the select list with options
for (var i=0;i < returnItm.getItemCount(); i++) 
{
	var vendor = returnItm.getItemByIndex(i);
	select.options[i]=
	new Option(vendor.getProperty("name"),
	vendor.getProperty("id"));
} 

You can call a server method in javascript by using .applyMethod() in the javacsript

var inn = new Innovator();
var param = "" + one + "";
param += "" + two + "";
var result = inn.applyMethod("dotNetMethod",param);

Your server method might look like this..

Innovator inn = this.getInnovator();

string param1 = this.getProperty("param1");
string param2 = this.getProperty("param2");

//System.Diagnostics.Debugger.Break();
Item itemReturn = inn.newItem("ReturnItem");

string res = "Test2";

itemReturn.setProperty("returnvalue1", "Test");
itemReturn.setProperty("returnvalue2", res);

return itemReturn;

Hope this helps.

Dave

Philip - Friday, February 6, 2015 1:38 PM:

Hi Dave, Thanks for the info.

I ended up doing exactly that. This is my server method;

string sql = this.getProperty("sql");
string strSQL = sql + " FOR XML raw"; ////Returning xml

System.Data.SqlClient.SqlConnection _conn = null;
 _conn = new System.Data.SqlClient.SqlConnection("Connection string here");
_conn.Open();
           
System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand(strSQL, _conn);
cmd.CommandType = System.Data.CommandType.Text;
System.Data.SqlClient.SqlDataReader _rdr = cmd.ExecuteReader();

string result = string.Empty;
while (_rdr.Read())
{
 result = _rdr[0].ToString().Replace("row","Item type='row'");
}
_conn.Close();

Aras.IOM.Item resItem = this.newItem();
resItem.loadAML("<AML>" + result + "</AML>");
return resItem;

And this is my client method;

var inn = top.aras.newIOMInnovator();

var sql ="sql statement here";
var itm = inn.applyMethod("server method","<sql>" + sql + "</sql>");
var counter = itm.getItemCount();  // how many items 

var ddlWarehouse = document.all.ddl_Warehouse;
for (var i = 0; i < counter; i++) // loop to add them all
{
 var theOption = document.createElement("OPTION");
 ddlWarehouse.options.add(theOption);
 theOption.innerText =  itm.getItemByIndex(i).getAttribute("label"); // these fields must match from above sql
 theOption.value = itm.getItemByIndex(i).getAttribute("value");// these fields must match from above sql
}

This populates data into a dropdown list from an external database

Could use some tweaking but it works.

Philip 

DavidSpackman - Sunday, February 8, 2015 6:00 PM:

Hi Philip.

Good to hear.

Dave

AbhishekSrivastava - Monday, March 21, 2016 9:04 AM:

Hi Dave ,

If i need to click on dropdown value from db and related value fetch on other fields, like Vendor Type, Vendor Name, Vendor Status all are dropdown .

So if i select Vendor type then it filters Vendor Name and provide that name which relates with vendor type , when i select vendor name then fetch value from DB like approved or pending.

I want fetch data from vendor Item Type to other Item Type.

Kindly guide me with code if possible.

Thanks in advance

Abhishek