VB.net Custom sorting in BOM

[pball]

I have a script that sorts the assembly BOM based on two properties, Type and Part Number. Type previously contained three values of (blank), M(ake), or B(uy), which by random change would sort in the preferred order using a descending sort. The resulting order is M, B, (blank). The second Part Number sort would sort all of the items in each Type by part number.

My problem now is I want to add R(eference) to the Type properties, but the descending sort order won't be in the order I want. I want the order to be M, B, R, (blank).

I'm using the code below currently. Is there a way to get the Type to sort in a custom order of my choosing and not just ascending or descending alphanumeric order?

        Dim oBOM As BOM = oAssydoc.ComponentDefinition.BOM
        oBOM.StructuredViewEnabled = True
        oBOM.StructuredViewFirstLevelOnly = False
        Dim oBOMView As BOMView = oBOM.BOMViews.Item("Structured")
        Call oBOMView.Sort("REQ Type", False, "Part Number", True)

[pball]

Does anyone have an example of how to use the BOMRowsToRenumber option of the renumber command? For example I want to renumber 5-10 on the BOM.

BOMView.Renumber( [StartValue] As Long, [Increment] As Long, [BOMRowsToRenumber] As Variant )

BOMRowsToRenumber

Variant

Optional input ObjectCollection of BOMRow objects. If not supplied, all rows in the BOMView are renumbered. If provided, only the input rows are renumbered. This is an optional argument whose default value is null.

[omartin]

Hey pball, there is no custom sorting, you can create a column to assign a, "Sort weight" (you can make the M's 0, B's 1 etcc..). then use this new column for sorting.

If you don't want the new column to show up in your BOM, you can just remove it at the end of your script.

for the BOMRowsToRenumber, you can create the collection by:

Dim colBomrows As ObjectCollection
Set colBomrows = ThisApplication.TransientObjects.CreateObjectCollection

 you can use a loop to add to the collection:
colbomrows.Add(...bomrow...)

and then use this object collection as the BOMRowsToRenumber variable

[pball]

@omartin 

Unfortunately the api can not add columns to the BOM so using a column with sort weight valves isn't an option as this has to work with existing parts. You example code for the BOMRowsToRenumber parameter works.

I'm running into issues which appear to be either api limitation or bugs. I've tried two different method of renumbering items which works but the issue is sorting the BOM in the middle of executing code does not seem to apply. Which causes an issue with the method I'm using to sort item.

Attached is an assembly with some virtual parts that are out of order. The attached code should swap the order of the first two items in the BOM. The best way I can describe the issue is to show a before and after picture of the BOM and debugging printouts.

sub routines bomex and bomex2 do the same thing with two different methods

Bom before code is run

Debug printout before renumbering and sorting happens

bom row # 1 - item # 1 - PN Reference
bom row # 2 - item # 2 - PN Buy
bom row # 3 - item # 3 - PN Blank
bom row # 4 - item # 4 - PN Made
bom row # 5 - item # 5 - PN Stock
-------------

Debug printout after renumbers and sorting happens. Notice how row 1 and 2 have had the item numbers switched, but the item number order is not properly sorted.
bom row # 1 - item # 2 - PN Reference
bom row # 2 - item # 1 - PN Buy
bom row # 3 - item # 3 - PN Blank
bom row # 4 - item # 4 - PN Made
bom row # 5 - item # 5 - PN Stock

After the code finishes running the BOM displays the proper order.

The sub bomex3 tries to renumber more items and demonstrates how the sort order not updating causes issues due to referencing incorrect items.

After running bomex3 the Buy and Reference part are out of order, because during the first part of the code those two item numbers are swapped but in the second part that change is overridden because the sort command did not sort the item numbers.

I hope this makes sense.

[omartin]

I remember reading in the docs some where the the bomrows are not stored in a sequential order so the bomrow Index 1, is not necessarily always the first Bom as displayed, I think that is why when you print it out in code they appear different.

so instead of swaping item 1 and 2 by their index, you need to check their actual .itemNumber properties and swap.

Also one way to workaround the adding column is. to first add the column manually via the bom editor, and then export the bom customization.

and then you can import the bom customization (via, oBOM.ImportBOMCustomization..)

to any assembly and it will add the column(s) even if they were not there before.

[pball]

I figured out a method to sorting the BOM and subsequently the Parts List on a drawing with a custom order. The method is pretty simple once figured out. I'm not going to share the full code but I'll post bits and pseudo code which I hope could help others.

The general idea is to use a class or just a list that the items in the BOM can temporarily be saved in and then sorted. After that list is sorted the BOM can be updated based off the index of each part in the list.

If this isn't enough to work with, just ask and I could explain more.

        Dim oCompDef As ComponentDefinition
'Add items in BOM to a class list
        Dim i As Long
        For i = 1 To oBOMRows.Count

            oCompDef = oBOMRows.Item(i).ComponentDefinitions.Item(1)

            Dim ReqType As String = ""
            Dim oPartNum As String = ""

            If (TypeOf oCompDef Is VirtualComponentDefinition) Then
                oPartNum = oCompDef.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value

                Try
                    ReqType = oCompDef.PropertySets.Item("Inventor User Defined Properties").Item("REQ TYPE").Value
                Catch
                End Try
            Else
                oPartNum = oCompDef.Document.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value

                Try
                    ReqType = oCompDef.Document.PropertySets.Item("Inventor User Defined Properties").Item("REQ TYPE").Value
                Catch
                End Try

            End If
            If (oPartNum <> "") Then EbomAddorUpdateQty(oPartNum, "", 0, ReqType, "")
        Next
'Use a class function to sort the class in the desired order
        EBomSort()
'Iterate through each BOM item and update the Item Number based off the location in the class list
        For i = 1 To oBOMRows.Count
            oBOMRows.Item(i).ItemNumber = EBomIndex(oBOMRows.Item(i).ComponentDefinitions.Item(1).Document.PropertySets.Item("Design Tracking Properties").Item("Part Number").Value) + 1
        Next
'Sort the BOM
        Call oBOMView.Sort("Item", True)

[dg2405]

Here is the custom-way i sort the BOM-Table:

AddReference "System.Data"
AddReference "System.Xml"

Dim oAsmDoc As AssemblyDocument
If TypeOf ThisDoc.Document Is DrawingDocument Then
	Try 'Wenn keine Ansichten auf Blatt würde sonst Fehler kommen
		oAsmDoc = ThisDoc.Document.Sheets(1).DrawingViews(1).ReferencedDocumentDescriptor.ReferencedDocument
	Catch
		Exit Sub
	End Try
Else
	oAsmDoc = ThisDoc.Document
End If

Dim oBOM As BOM = oAsmDoc.ComponentDefinition.BOM
oBOM.ImportBOMCustomization("C:\Vault-Workspace\Preferences\Einstellungen\BOM\BOM-Settings.xml")
If oBOM.PartsOnlyViewEnabled = False Then oBOM.PartsOnlyViewEnabled = True
If oBOM.StructuredViewEnabled = False Then oBOM.StructuredViewEnabled = True
Dim oBOMView As BOMView = oBOM.BOMViews.Item("Nur Bauteile")
If oBOMView.BOMRows.Count = 0 Then Exit Sub

'BOM-Datatable erstellen
Dim dtBOM As New System.Data.DataTable
dtBOM.Columns.Add("Objektnummer", GetType(String))
dtBOM.Columns.Add("TeileNummer", GetType(String))

'Datatable für Sortieren erstellen
Dim dtSort As New System.Data.DataTable
dtSort.Columns.Add("CS", GetType(String))
dtSort.Columns.Add("Zulieferer", GetType(String))
dtSort.Columns.Add("BestellNummer", GetType(String))
dtSort.Columns.Add("TeileNummer", GetType(String))

'Datatable befüllen
Dim oBomRows As BOMRowsEnumerator = oBOMView.BOMRows
Dim oBomRow As BOMRow
Dim oRowDoc As Document
Dim oCusPropSet As PropertySet
Dim oDesPropSet As PropertySet
Dim CS As String = ""
Dim TeileNummer As String
Dim BestellNummer As String
Dim Zulieferer As String
Dim ObjektNummer As Integer = 0
For Each oBomRow In oBomRows
	oRowDoc = oBomRow.ComponentDefinitions(1).Document
	If TypeOf oBomRow.ComponentDefinitions(1) Is VirtualComponentDefinition Then
		oCusPropSet = oBomRow.ComponentDefinitions(1).PropertySets("User Defined Properties")
		oDesPropSet = oBomRow.ComponentDefinitions(1).PropertySets("Design Tracking Properties")
	Else
		oCusPropSet = oRowDoc.PropertySets("User Defined Properties")
		oDesPropSet = oRowDoc.PropertySets("Design Tracking Properties")
	End If
	Try
		CS = oCusPropSet("CS").Value
	Catch
		CS=""
	End Try
	Zulieferer = oDesPropSet("Vendor").Value
	If CS = "F" Then
		BestellNummer = ""
	Else
		BestellNummer = oDesPropSet("Stock Number").Value
	End If
	TeileNummer = oDesPropSet("Part Number").Value
	'Properties in Datatable
	dtSort.Rows.Add(CS, Zulieferer, BestellNummer, TeileNummer)
	'Properties in OrigDatatable
	ObjektNummer=ObjektNummer+1
	dtBOM.Rows.Add(ObjektNummer,TeileNummer)
Next

'Datatable sortieren
dtSort.DefaultView.Sort = "CS ASC, Zulieferer ASC, BestellNummer ASC, TeileNummer ASC"
Dim dtResult As System.Data.DataTable = dtSort.DefaultView.ToTable()

'ItemNumber in BOM nach Sortierung von Datatable zuweisen
For i = 0 To dtResult.Rows.Count - 1
	TeileNummer = dtResult.Rows(i).Item("TeileNummer").ToString
	Dim dtRow As System.Data.DataRow
	For Each dtRow In dtBOM.Rows
		If dtRow("TeileNummer") = TeileNummer Then
			oBomRows(dtRow("Objektnummer")).ItemNumber = i + 1
			Exit For
		End If
	Next
Next

'BomView "Nur Bauteile" sortieren
oBOMView.Sort2("Objekt", True)

'BomView "Strukturiert" sortieren
oBOMView = oBOM.BOMViews.Item("Strukturiert")
If oBOMView.BOMRows.Count = 0 Then Exit Sub
oBOMView.Sort2("CS", True, "Bestandsnummer", True, "Bauteilnummer", True, True)
oBOMView.Renumber()