RonCK - Tuesday, February 16, 2010 8:36 AM:
My IT guys have been unable to get Report Generator installed and don't want to give me permission to direct access the server, so I was wondering if I can use method code activated by a button on a form to create a custom excel spreadsheet report. I know that you can do the same queries from a report in a method, the display and file creation are really my questions. Has anybody done this? What's the best way to get started?
thanks,
RonCK
RonCK - Wednesday, February 17, 2010 9:31 AM:
OK, so I thought I had this figured out, but I keep getting an "Internal Error: event handler failed" "unknown runtime error". How do I debug this? The script debugger is not reporting any failures, this seems to be an Aras side problem of some sort. Here's the code I'm using:
/* ************************************************************************** *
* Method Name: phnxBOMBtnOnClick *
* Description: This method is called to generate an Excel BOM for an assembly*
* based on Aras Data. *
* Events: onClick *
* History: Initially Created *
* - RJCK 02/16/2010 *
* ************************************************************************** */
//debugger;
if(!document.thisItem)
{
return;
}
var item=document.thisItem;
var itemNumber=item.getProperty("item_number");
var qry=item.newItem();
var AMLtext="<Item type="Part" where="[PART].item_number='"+itemNumber+"' AND [PART].is_current='1'" action="get" select="item_number,name,cost">"+
"<Relationships>"+
"<Item type="Part BOM" select="sort_order,quantity,reference_designator,related_id(item_number,name)">"+
"<related_id>"+
"<Item type="Part" select="item_number,name, cost">"+
"<Relationships>"+
"<Item type="Part BOM" select="sort_order,quantity,related_id(item_number,name)">"+
"</Item>"+
"<Item type="Part AML" action="get">"+
"<related_id>"+
"<Item type="Manufacturer Part" action="get" select="manufacturer,item_number">"+
"</Item>"+
"</related_id>"+
"</Item>"+
"<Item type="Part AVL" action="get">"+
"<related_id>"+
"<Item type="Vendor Part" action="get" select="source_id,catalog_number">"+
"</Item>"+
"</related_id>"+
"</Item>"+
"</Relationships>"+
"</Item>"+
"</related_id>"+
"</Item>"+
"</Relationships>"+
"</Item>";
qry.loadAML(AMLtext);
var result = qry.apply();
var entries = result.getRelationships();
alert("BOM Items: "+entries.getItemCount());
var excel = new ActiveXObject("Excel.Application");
excel.Visible=true;
var strPathDoc = "C:\Aras\BOM100xxxxx.xltx";
var workbook=excel.Workbooks.open(strPathDoc);
var sheet=workbook.ActiveSheet;
for(var i=0; i<entries.getItemCount(); i++)
{
var row=entries.getItemByIndex(i);
var part=row.getRelatedItem();
var partData=part.getRelationships();
for(var j=0; j<partData.getItemCount(); j++)
{
var testData=partData.getItemByIndex(j);
var testItem=testData.getRelatedItem();
if(testItem.getProperty("manufacturer"))
{
//AML STUFF
var mp=testItem;
var mn=mp.getProperty("manufacturer");
var mfg=item.newItem("Manufacturer","get");
var whereClause="[manufacturer].id='"+mn+"'";
mfg.setAttribute("where",whereClause);
mfg=mfg.apply();
}
else if(testItem.getProperty("catalog_number"))
{
//AVL STUFF
var vp=testItem;
var vn=vp.getProperty("source_id");
var vndr=item.newItem("Vendor","get");
whereClause="[vendor].id='"+vn+"'";
vndr.setAttribute("where",whereClause);
vndr=vndr.apply();
}
}
//POPULATE THE ROW FOR THIS ITEM
sheet.Cells(i+2,1).Value = row.getProperty("sort_order"); //always ok
sheet.Cells(i+2,2).Value = part.getProperty("item_number"); //always ok
sheet.Cells(i+2,3).Value = part.getProperty("name"); //always ok
sheet.Cells(i+2,4).Value = row.getProperty("quantity"); //always ok
sheet.Cells(i+2,5).Value = row.getProperty("reference_designator"); //always ok
if(mfg.getProperty("name")) //test, use, then clear
{
sheet.Cells(i+2,6).Value = mfg.getProperty("name");
mfg="";
}
if(mp.getProperty("item_number")) //test, use, then clear
{
sheet.Cells(i+2,7).Value = mp.getProperty("item_number");
mp="";
}
if(vndr.getProperty("name")) //test, use, then clear
{
sheet.Cells(i+2,8).Value = vndr.getProperty("name");
vndr="";
}
if(vp.getProperty("catalog_number")) //test, use, then clear
{
sheet.Cells(i+2,9).Value = vp.getProperty("catalog_number");
vp="";
}
sheet.Cells(i+2,10).Value= part.getProperty("cost"); //should always be ok
}
Brian - Sunday, February 28, 2010 11:06 PM:
Hi Ron,
Did you get this sorted out?
First thing to look at is the file open command.
var strPathDoc = "C:\Aras\BOM100xxxxx.xltx";
var workbook=excel.Workbooks.open(strPathDoc);
Since the file BOM100xxxx.xltx does not exist you cannot open it in Excel.
Change this line to
var workbook=excel.Workbooks.add();
Then somewhere later you will need to save the resulting workbook which should be something like:
excel.Workbook.SaveAs("C:\Aras\BOMxxxx.xltx");
I couldn't run a complete example because I couldn't get the AML to return a result with my dataset.
Cheers,
Brian.
