Go into Capture like you usually would and generate a CIS Bill Of materials. The only things you want in the BOM are:
- Item Number
- Quantity
- PART_NUMBER
- Part Reference
When you click OK you should get a csv file with all of your data in it. That looks something like this:
This is the file we'll load into Innovator. Make sure you close this file in excel because I had trouble opening it in innovator if it was already open in another program.
The Form
In a previous post I showed how I created a new item type CIP LOADED BOM to store this information. Now we need to go modify the form associated with this item, because that is where we'll import this file. This is kind of a hack way of doing things but at least you can see what I did and do it better on your system.
Go to Administration->Forms and find the form LOADED BOMs form that we created in the last post.
Modifying this form is just like using any gui editor, you can add fields, select, boxes, etc.
Text Fields
This is mostly for my own debug but I added four text fields for each of the columns in the CIP LOADED BOM. You can see above where I set the data source for the partID field to partID. Now when you select a row from the CIP LOADED BOM it will populate with that rows data, and you can edit it if you want to. I'll probably disable this for regular users.
Form Events
There are two form events that I used, onLoad and onResize. onLoad runs every time the form loads and gets the Innovator parts for the Assembly Part Number select box. onResize runs whenever you resize the form, it will resize the html2 element that holds the table of CIP parts in it.
Form onResize
document.getElementById("MyGrid").style.width = document.body.clientWidth - 100;
Notice that we're switching over to javascript here. All the forms are just html documents and all the active elements are done in javascript. This script just re-sizes the form whenever the window resizes.
Form onLoad
The user needs to be able to choose which Innovator part to associate this Orcad BOM with. So when the page loads I look up all the parts that are not released and put them in a select box so they can pick one. My basic assumption is you can only load a CIP Bom onto a part that is unreleased, and that part is only used for holding CIP BOM data and nothing else.
var item = document.thisItem;
// Set up the query Item. Get all the most current part numbers that are not released
var qryItem = item.newItem("Part","get");
qryItem.loadAML(
"<Item type='Part' action='get' select='item_number,description,cost'>" +
"<item_number condition='like'>*</item_number>" +
"<is_released condition='eq'>0</is_released>" +
"<is_current condition='eq'>1</is_current>" +
"</Item>"
);
/*qryItem.loadAML(
"<Item type='Part' action='get'>" +
"<generation condition='in'>SELECT MAX(generation) FROM [PART] WHERE is_released='0' AND is_current='1'</generation>" +
"</Item>"
);
*/
// Perform the query.
var results = qryItem.apply();
// Test for an error.
if (results.isError()) {
top.aras.AlertError("No items maybe? Item not found: " + results.getErrorDetail());
return;
}
//debugger;
//loop through the returned items
var count = results.getItemCount();
//var select = document.getElementById("assemblyPart");
var select = document.getElementsByName("assemblyPart")[0];
select.options.length = 0;
for (i=0; i<count; ++i) {
var rItem = results.getItemByIndex(i);
//select.options.add(new Option(rItem.getProperty("id"), rItem.getProperty("item_number"));
//select.options.add("Test", "test");
select.options[select.options.length] = new Option(rItem.getProperty("item_number"), rItem.getProperty("id"));
}
The code above uses the loadAML function to create a query to get the most current version of all parts, that are also unreleased. This way once a user has uploaded and released a bom part they can only change it buy increasing the rev. That's my idea anyway. The next part runs the query and throws up a dialog box if it finds no parts to use. Finally we get how many results the query returned and stuff all those results into our select box.
Html Table
I thought it would be a good idea to first load the .csv bom file from Capture, and show it back to the user for review before we save it. That way if there's some corruption or something, you at least get a chance to cancel. The first step to that is adding an html field to the form, and putting this html code into it:
<script type="text/javascript"> top.aras.uiAddConfigLink2Doc4Assembly(document, "TreeTable"); </script> <object id="MyGrid" style="width:600px;height:500px;"classid="../cbin/TreeTable.dll#Aras.Client.Controls.GridContainer"> </object>
Note the object id of MyGrid, it will be used to populate the grid in the next step.
Load CSV Button
To add code to the load button I just defined a onClick function under Field Events. Then I used this javascript.
var vault = top.aras.vault;
var fileName = vault.SelectFile();
strData = vault.ReadText(fileName);
//alert(strData);
strDelimiter = ",";
// Create a regular expression to parse the CSV values.
var objPattern = new RegExp(
(
// Delimiters.
"(\\" + strDelimiter + "|\\r?\\n|\\r|^)" +
// Quoted fields.
"(?:\"([^\"]*(?:\"\"[^\"]*)*)\"|" +
// Standard fields.
"([^\"\\" + strDelimiter + "\\r\\n]*))"
),
"gi"
);
// Create an array to hold our data. Give the array
// a default empty first row.
var arrData = [[]];
// Create an array to hold our individual pattern
// matching groups.
var arrMatches = null;
// Keep looping over the regular expression matches
// until we can no longer find a match.
while (arrMatches = objPattern.exec( strData )){
// Get the delimiter that was found.
var strMatchedDelimiter = arrMatches[ 1 ];
// Check to see if the given delimiter has a length
// (is not the start of string) and if it matches
// field delimiter. If id does not, then we know
// that this delimiter is a row delimiter.
if (
strMatchedDelimiter.length &&
(strMatchedDelimiter != strDelimiter)
){
// Since we have reached a new row of data,
// add an empty row to our data array.
arrData.push( [] );
}
// Now that we have our delimiter out of the way,
// let's check to see which kind of value we
// captured (quoted or unquoted).
if (arrMatches[ 2 ]){
// We found a quoted value. When we capture
// this value, unescape any double quotes.
var strMatchedValue = arrMatches[ 2 ].replace(
new RegExp( "\"\"", "g" ),
"\""
);
} else {
// We found a non-quoted value.
var strMatchedValue = arrMatches[ 3 ];
}
// Now that we have our value string, let's add
// it to the data array.
arrData[ arrData.length - 1 ].push( strMatchedValue );
}
window.arrData = arrData;
// Return the parsed data.
//alert( arrData );
var item = document.thisItem;
// Get the relationships
var results = item.newItem("CIP LOADED BOM");
//debugger;
test = item.getNewID()
results.setID(test);
//debugger;
//qry.setAttribute("select", "quantity,related_id(item_number,name,cost)");
results.setProperty("quantity", "200");
//prepare the grid
document.getElementById("MyGrid").style.width = document.body.clientWidth - 100;
var propNameArr = new Array("quantity");
var gridXml =
"<table editable='false' draw_grid='true' justify='true'>" +
"<columns>" +
"<column width='10%' align='left' />" +
"<column width='10%' align='left' />" +
"<column width='30%' align='right' />" +
"<column width='50%' align='right' />" +
"</columns>" +
"<thead>" +
"<th>Item</th>" +
"<th>Quantity</th>" +
"<th>Part Number</th>" +
"<th>Reference Designator</th>" +
"</thead>" +
"</table>";
var inn = item.getInnovator();
var gridDom = inn.newXMLDocument();
gridDom.loadXML(gridXml);
var tableNd = gridDom.selectSingleNode("/table");
var c = arrData.length;
for (var i=0; i<c; ++i) {
//var bom = results.getItemByIndex(i);
//var part = bom.getRelatedItem();
var trNd = gridDom.createElement("tr");
trNd.setAttribute("id", i);
var tdNd;
tdNd = gridDom.createElement("td");
tdNd.text = arrData[i][0];
trNd.appendChild(tdNd);
tdNd = gridDom.createElement("td");
tdNd.text = arrData[i][1];
trNd.appendChild(tdNd);
tdNd = gridDom.createElement("td");
tdNd.text = arrData[i][2];
trNd.appendChild(tdNd);
tdNd = gridDom.createElement("td");
tdNd.text = arrData[i][3];
trNd.appendChild(tdNd);
tableNd.appendChild(trNd);
}
document.MyGrid.InitXml(gridDom.xml);
The very first part of this code opens a file explorer dialog box to let the user choose the file they want to upload. The next section is borrowed from someone on Stack Overflow. It parses the csv file and converts it into an array of array's that we can work with. I store that array as a global variable, because I don't want to have to parse it again when I click the save button. Something about duplicating the parsing code in two different functions didn't seem right to me.The rest of the code is dedicated to creating the grid that will display all of the component information. The final action is to take that grid and stuff it into the MyGrid html object we created in the last step.
Save the bom
Now that you can upload and preview the bom, you need to save it and this is the last step for that. The onclick code for the save button is below.
var bomData = window.arrData;
//check if the global bom data is saved
if (bomData == null) {
top.aras.AlertError("You need to load a BOM before you can save.");
return;
}
if(bomData.length <= 1){
top.aras.AlertError("I think the bom you loaded is empty, is there no header column in your csv file?");
}
var select = document.getElementsByName("assemblyPart")[0];
var partID = select.options[select.selectedIndex].value;
if(partID.length != 32){
top.aras.AlertError("ID should be 32 long");
return;
}
var innovator = new Innovator();
//debugger;
var c = bomData.length;
for (var i=1; i<c; ++i) {
if(bomData[i].length != 4){
continue;
}
var partItem = innovator.newItem("CIP LOADED BOM","add");
partItem.setProperty("partid", partID);
partItem.setProperty("quantity", bomData[i][1]);
partItem.setProperty("altiorpartnumber", bomData[i][2]);
partItem.setProperty("ref_designator", bomData[i][3]);
var resultItem = partItem.apply();
if (resultItem.isError()) {
top.aras.AlertError(resultItem.getErrorDetail());
return;
}
}
The above code looks for the global array of bom items from when we loaded the csv file. If it's not found it throws up an error. If it is found it loops through all of the parts, creating a new item for each one and inserting it into the table by calling the apply() method. There's probably a more efficient way to do them all at once but I didn't know what it was. For a large bom this only takes a few seconds for me. Putting it all together
That's all there is, if everything came together for you, you should now be able to upload a bom created in orcad into Innovator. To invoke this form you just created you need to go to CIP LOADED BOMS on the design tab and then edit one of the existing items. If you don't have any then just create a fake test one to start. I'm sure eventually I'll figure out how to just click and have this form come up but I don't know today :)
Now just upload a csv bom, choose a part to attach these parts to and save away. When it's done uploading you'll be able to select that part under Design - > Parts, click on the CIP BOM tab and see all your parts under there. Even better you can export that bom to excel to send to your CM.
The End
Hope all this helps someone else, I know it's a bit of a mess but it does work. Good Luck!
This work is licensed under a Creative Commons Attribution-ShareAlike 3.0 Unported License.
