mendenaresh1357 - Tuesday, March 2, 2010 1:25 PM:
Hi Everyone,
I am working on federated Item. I am getting data from a remote server. iam supposed to display the data on a federated item which is present in the relationship tab of an Itemtype. Iam able to display a single row but not able to display multiple rows in the relation Grid(federated item). This is the code am using...which returns only the last row. i even tried appenItem() method but failed to dispaly any results.
************************************************************************************************
Innovator inno = this.newInnovator();
Item itemreslts = inno.applySQL("select c_invoice_id,totallines,grandtotal,m_pricelist_id,c_paymentterm_id from postgresql.postgres.adempiere.c_invoice");
int count = itemreslts.getItemCount();
StreamWriter sw = null;
try
{
sw = File.CreateText("C:/Program Files/Aras/Innovator/Innovator/Server/logs/postgres1.txt");
sw.WriteLine(count);
}
catch(Exception e)
{
inno.newError(e.ToString());
}
Item postgres = this.newItem("pt_post_asset");
Item returnitem = this.newItem("Sample");
for(int i = 0; i<count; i++)
{
Item results = itemreslts.getItemByIndex(i);
string assetid = results.getProperty("c_invoice_id");
string assetname = results.getProperty("grandtotal");
string assetvalue = results.getProperty("totallines");
string lifeunits = results.getProperty("m_pricelist_id");
string assetgroupid = results.getProperty("c_paymentterm_id");
sw.WriteLine("assetid: " + assetid);
sw.WriteLine("assetname: " + assetname);
sw.WriteLine("assetvalue: " + assetvalue);
sw.WriteLine("lifeunits: " + lifeunits);
sw.WriteLine("assetgroupid: " + assetgroupid);
sw.WriteLine("");
string itemid = getNewID();
postgres.setID(itemid);
postgres = postgres.apply();
postgres.setProperty("a_asset_id",assetid);
postgres.setProperty("name",assetname);
postgres.setProperty("value",assetvalue);
postgres.setProperty("lifeuseunits",lifeunits);
postgres.setProperty("a_asset_group_id",assetgroupid);
}
return postgres;
*************************************************************************************************
jenC - Tuesday, March 2, 2010 4:50 PM:
Are you doing this in an OnGet event for the federated item?
The easiest way I've found is to move your SQL:
select c_invoice_id,totallines,grandtotal,m_pricelist_id,c_paymentterm_id from postgresql.postgres.adempiere.c_invoice
into a stored procedure SQL item called "SP_GET_INVOICES" for instance
Then you can do something like this:
Item postgres = this.newItem("SQL", "SQL PROCESS");
postgres.setProperty("name", "SP_GET_INVOICES");
postgres.setProperty("PROCESS", "CALL");
postgres = postgres.apply();
//do error checking here if you want
return (postgres);
that should return all results to the relationship grid.
mendenaresh1357 - Wednesday, March 3, 2010 7:08 AM:
Hi JenC,
Yes. Iam doing this in onGet Event. Your Approach Helped me in fetching whole data at once(in different AML tags). But i Cant see any data in the relationshipGrid after return statement. I think the issue is that we need to setID() for each row. but in your approach i didn't find a way to setID(). So i tried other way. I modified the Procedure to create Id in each row. The code is below..
create procedure SP_GET_INVOICES (@ci int)
AS
BEGIN
select replace(NewID(),'-','') id, 'pt_post_data' type,
c_invoice_id,totallines,grandtotal,m_pricelist_id,c_paymentterm_id
from postgresql.postgres.adempiere.c_invoice where c_invoice_id = @ci
END
when i run for a c_invoice_id = '103' i get the result...
i dont get the row of data in the relationship Grid. I also think we should be able to set Property on each field on the grid (fields in the relationship grid. e.g : postgres.setProperty("c_invoice_d", postgres.getProperty("c_invoice_d"))).
Cheers,
Naresh
jenC - Wednesday, March 3, 2010 11:18 AM:
Can you show me what your entire OnGet method is that calls your Stored Procedure?
I see that you are setting type. In the example that works on my machine, we are not setting a type, I wonder if that's confusing it....
Is pt_post_data the item type name of your federated item?
mendenaresh1357 - Wednesday, March 3, 2010 2:52 PM:
Hi JenC,
Thanx for ur Help. I actullay soved the problem in another approach. Still I want to know what your aproach is. The earlier approach you gave me(I might didn't put it correctly) didn't work beacuse we are not adding 'TypeID' property in the main tag e.g : <Item Type='x' Id='xyz'>. But later i added another properyt (e.g: <Item Type='x' TypeID = "xyz" Id='xyz'>) to the main tag. My job is done. Earlier what i was doing is(approach u gave me) i have written 2 procedures
1) Without adding tag type and id the code is below:
create procedure SP_GET_INVOICES_1
AS
BEGIN
select totallines,grandtotal,m_pricelist_id,c_paymentterm_id
from postgresql.postgres.adempiere.c_invoice
END
and then written a method in aras. code is pasted below:
Item postgres = this.newItem("SQL", "SQL PROCESS");
postgres.setProperty("name", "SP_GET_INVOICES_1");
postgres.setProperty("PROCESS", "CALL");
postgres = postgres.apply();
return postgres;
2) adding tag type and id the code is below:
create procedure SP_GET_INVOICES
AS
BEGIN
select replace(NewID(),'-','') id, 'pt_post_asset' type,
c_invoice_id,totallines,grandtotal,m_pricelist_id,c_paymentterm_id
from postgresql.postgres.adempiere.c_invoice where c_invoice_id = @ci
END
and then written a method in aras(same as above but calling different procedure). code is pasted below:
Item
postgres = this.newItem("SQL", "SQL PROCESS");
postgres.setProperty("name",
"SP_GET_INVOICES");
postgres.setProperty("PROCESS", "CALL");
postgres
= postgres.apply();
return postgres;
Both the ways didn't work.
I finally worked with :
Innovator inno = this.newInnovator();
Item itemreslts = inno.applySQL("select totallines,grandtotal,m_pricelist_id,c_paymentterm_id from postgresql.postgres.adempiere.c_invoice");
int count = itemreslts.getItemCount();
Item res = this.getInnovator().newResult("");
XmlElement resultNd = (XmlElement) res.dom.SelectSingleNode("/*/*/*");
Item currItm = this.newItem("");
currItm.loadAML("<Item type='pt_data_asset' typeId='120DA28DB71442839FC5072CD1A27B2C'></Item>");
for (int i=0; i<count; i++)
{
Item results = itemreslts.getItemByIndex(i);
string id = this.getInnovator().getNewID();
currItm.setID(id);
currItm.setProperty("grandtotal", results.getProperty("grandtotal"));
currItm.setProperty("totallines", results.getProperty("totallines"));
currItm.setProperty("m_pricelist_id", results.getProperty("m_pricelist_id"));
currItm.setProperty("c_paymentterm_id", results.getProperty("c_paymentterm_id"));
resultNd.AppendChild(resultNd.OwnerDocument.ImportNode(currItm.node, true));
}
return res;
With this piece of code i achieved.
If there are any issues/errors in my earlier code, Please let me know
Cheers,
Naresh
lisawu - Monday, October 1, 2012 11:37 PM:
Excuse me , Could you tell me that how do you set the database link in the ARAS and remote System,
I set the account(innovator) in the remote DB,role is db_owner .
when I run the SP(get date from the remote DB), it is an error ocurr, the message is ?select permission denied on object 'AAA',database 'BBB', schema 'dbo' ?.
BR.
Lisa
