How to Determine a BOM Quantity Cell in a Parts List

[petestrycharske]

All,

Good morning!  I've ventured into the exciting world of iLogic once again and have a question on accessing the BOM Qty columns of a Parts List.  I believe the entity that I'm looking for is the 'kBOMQuantityObject', but I'm not sure.  I've been able to identify the "QTY" columns in the Parts List, but I'm using text string comparisons to get there.  It works, but I'm missing the "Base Qty", "Item Qty" and "Unit Qty" for some reason...

Ultimately my goal is to replace 0 quantity items with a "-", but don't want to do that for every potential column, just for quantity columns, so I'm leery about setting every 0 value to a "-".  This is why I'm searching for a reliable way to isolate the quantity columns.  

I've made a video to help illustrate this as well.  Much thanks to @Anonymous for the code to cycle through the Parts List rows and columns.

'Define the necessary objects
Dim oDrawDoc As DrawingDocument = ThisDoc.Document

Dim oSheet As Sheet = oDrawDoc.ActiveSheet

Dim oPartsList As PartsList = oSheet.PartsLists.Item(1)

Dim oPLColumn As PartsListColumn
Dim oPLRow As PartsListRow
Dim oPLCell As PartsListCell


'iterate through each row of the parts list
For i = 1 To oPartsList.PartsListRows.Count
	oPLRow = oPartsList.PartsListRows.Item(i)
	'look at each column in the row
	For j = 1 To oPartsList.PartsListColumns.Count
		oPLColumn = oPartsList.PartsListColumns.Item(j)
		oPLCell = oPLRow.Item(j)
		'Check to see if the column title contains the letters "qty", "True" indicates the case doesn't matter
		If String.Compare(Left(oPLColumn.Title, 3), "qty", True) = 0 Or String.Compare(Mid(oPLColumn.Title, 5, 3), "qty", True) = 0 Then 
'		If oPLCell.Type = kBOMQuantityObject Then
			'Check to see if the cell has not been overriden and is set to 0, if so, replace the value with a "-"
			Try
				If oPLCell.Static = False And oPLCell.Value = 0 Then
					oPLCell.Value = "-"
				Else If oPLCell.Static = True
					Temp_Val = oPLCell.Value
					oPLCell.Static = False
					If oPLCell.Value = 0 Then
						oPLCell.Value = "-"
					Else 
						oPLCell.Value = Temp_Val
					End If
				End If
			Catch
			End Try
		End If
	Next
Next


iLogicVb.UpdateWhenDone = True

[WCrihfield]

Hi @petestrycharske.  Since it looks like you may know your way around in iLogic and PartsLists for the most part, here is a bit of iLogic code that should point you in the right direction for identifying those columns and cells within your parts lists.  The main point I'm showing within is how to use the column's PropertyType and check it against the PropertyTypeEnum variants to determine what type of data the column was designed to show, then record that column for use when looping through the cells of each row, so you know where that data will be in those rows.  Of course, if you have not designed those columns into your parts list, you will not find them this way though, but you could probably add them, if not found, and needed.

oDDoc = ThisDrawing.Document
oSheet = oDDoc.ActiveSheet
oPList = oSheet.PartsLists.Item(1)
Dim oCol, oBaseQtyCol, oItemQtyCol, oUnitQtyCol As PartsListColumn
'find columns for quantities
For Each oCol In oPList.PartsListColumns
	If oCol.PropertyType = PropertyTypeEnum.kBaseQuantityPartsListProperty Then
		oBaseQtyCol = oCol
	ElseIf oCol.PropertyType = PropertyTypeEnum.kUnitQuantityPartsListProperty Then
		oUnitQtyCol = oCol
	ElseIf oCol.PropertyType = PropertyTypeEnum.kItemQuantityPartsListProperty Then
		oItemQtyCol = oCol
	End If
Next
Dim oBaseQtyVal, oItemQtyVal, oUnitQtyVal As String
For Each oRow As PartsListRow In oPList.PartsListRows
	'get Cell values corresponding to identified columns
	oBaseQtyVal = oRow.Item(oBaseQtyCol).Value
	oItemQtyVal = oRow.Item(oItemQtyCol).Value
	oUnitQtyVal = oRow.Item(oUnitQtyCol).Value
Next

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

If you want and have time, I would appreciate your Vote(s) for My IDEAS 💡 or you can Explore My CONTRIBUTIONS

[petestrycharske]

@WCrihfield, thank you so much!  I realized after looking at your code, I was using "Type", not "PropertyType", so thanks for clearing that up for me.  However, now I've run into a new set of challenges.  The code, as written, only changes the last configuration of the iAssembly PartsList, so I was wondering if you had any tips or links for creating an array.  Below is a video link of what I'm seeing...

https://knowledge.autodesk.com/community/screencast/ff7c3de3-8f66-43f1-8754-e6c619b147b9

Appreciate all the help and have a most blessed night!

Peace,

Pete

[A.Acheson · Accepted solution]

Would it be easier to identify the column index by what is contained in the column title?

oPLColumn.Title.Contains("Qty")

Place this in an if statement and this should confine the search to the cells in those columns. 

It isn't the object method you were looking at but rather would be just looking purely at what the user sees in the table. 

[WCrihfield · Accepted solution]

Hi @petestrycharske.  I believe I understand the situation now.  However, in this situation, I believe that working with List objects to get the multiple columns of the same type may be an easier option than attempting to use Array objects, but that is mostly personal preference.  With a List, I don't have to specify collection size ahead of time, and it will be 1 based, instead of 0 based, which is just more natural.  I altered my iLogic code a bit to accommodate that unique situation.  So now I have created 3 List(Of PartsListColumn) objects just ahead of looping through the columns.  Each List is for one type of column, which there may be multiple of.  And within the loop, if the column is the specific type, it gets added to the corresponding List object.  Then later when it is looping through the rows, it now does a secondary loop through those lists of columns, as it does the final checks and changes.  See if this idea works better for you.

oDDoc = ThisDrawing.Document
oSheet = oDDoc.ActiveSheet
oPList = oSheet.PartsLists.Item(1)
Dim oBaseQtyCols, oItemQtyCols, oUnitQtyCols As New List(Of PartsListColumn)
'find columns for quantities
For Each oCol As PartsListColumn In oPList.PartsListColumns
	If oCol.PropertyType = PropertyTypeEnum.kBaseQuantityPartsListProperty Then
		oBaseQtyCols.Add(oCol)
	ElseIf oCol.PropertyType = PropertyTypeEnum.kUnitQuantityPartsListProperty Then
		oUnitQtyCols.Add(oCol)
	ElseIf oCol.PropertyType = PropertyTypeEnum.kItemQuantityPartsListProperty Then
		oItemQtyCols.Add(oCol)
	End If
Next
'if you really need an Array, instead of a List, just convert it like this
'oBQtyCols = oBaseQtyCols.ToArray
'List is 1 based (first Item index is 1), Array is 0 based (first Item index is 0)
For Each oRow As PartsListRow In oPList.PartsListRows
	'get Cell values corresponding to identified columns
	If oBaseQtyCols.Count > 0 Then
		For X As Integer = 1 To oBaseQtyCols.Count
			If Not oRow.Item(oBaseQtyCols.Item(X)).Static And _
				oRow.Item(oBaseQtyCols.Item(X)).Value = "0" Then
				oRow.Item(oBaseQtyCols.Item(X)).Value = "-"
			End If
		Next
	End If
	If oUnitQtyCols.Count > 0 Then
		For X As Integer = 1 To oUnitQtyCols.Count
			If Not oRow.Item(oUnitQtyCols.Item(X)).Static And _
				oRow.Item(oUnitQtyCols.Item(X)).Value = "0" Then
				oRow.Item(oUnitQtyCols.Item(X)).Value = "-"
			End If
		Next
	End If
	If oItemQtyCols.Count > 0 Then
		For X As Integer = 1 To oItemQtyCols.Count
			If Not oRow.Item(oItemQtyCols.Item(X)).Static And _
				oRow.Item(oItemQtyCols .Item(X)).Value = "0" Then
				oRow.Item(oItemQtyCols.Item(X)).Value = "-"
			End If
		Next
	End If
Next

If this solved your problem, or answered your question, please click ACCEPT SOLUTION.
Or, if this helped you, please click (LIKE or KUDOS) 👍.

If you want and have time, I would appreciate your Vote(s) for My IDEAS 💡 or you can Explore My CONTRIBUTIONS

[petestrycharske]

@A.Acheson, YES!!!  This is actually what I wanted to do in the beginning, but for some reason I couldn't find the "CONTAINS" option...  Is that contains case sensitive?  It doesn't look like it is, but I'm not finding any information on it in the iLogic help...  Is this just a general VB.Net thing?

Anyway, thanks for this tip, as this got me what I wanted and make the code a bit shorter.  Have an awesome and most blessed day!

Peace,
Pete

[petestrycharske]

@WCrihfield, thanks for the tip on lists!  After playing with it a bit, I think you do have to treat it like an array counting from Item "0".  I modified my line of code below to incorporate your changes (I'll post the full code below).  It works beautifully and am confident it won't modify any columns unintentionally!  Appreciate the help and have a most blessed day!

Peace,

Pete

For Each oPLRow In oPartsList.PartsListRows
	If oQtyCols.Count > 0 Then
		For i = 0 To oQtyCols.Count - 1
			Cell_Checker(oPLRow.Item(oQtyCols.Item(i)))
		Next
	End If

[petestrycharske · Accepted solution]

Here is the code that will ONLY impact BOM quantity columns, so it won't inadvertently update any other columns that may contain the characters "qty".  Thanks again @WCrihfield!!!

Sub Main
'Define the necessary objects
Dim oDrawDoc As DrawingDocument = ThisDoc.Document

Dim oSheet As Sheet = oDrawDoc.ActiveSheet

Dim oPartsList As PartsList = oSheet.PartsLists.Item(1)

Dim oPLColumn As PartsListColumn
Dim oPLRow As PartsListRow
Dim oPLCell As PartsListCell
Dim oQtyCols As New List (Of PartsListColumn)
Dim oBaseQtyCols As New List (Of PartsListColumn)
Dim oItemQtyCols As New List (Of PartsListColumn)
Dim oUnitQtyCols As New List (Of PartsListColumn)


'Iterate through every column in the parts list, identify it as a particular BOM
'column and then add it to a list for each BOM type
For Each oPLColumn In oPartsList.PartsListColumns
	If oPLColumn.PropertyType = kQuantityPartsListProperty Then
		oQtyCols.Add(oPLColumn)
	Else If oPLColumn.PropertyType = kBaseQuantityPartsListProperty Then
		oBaseQtyCols.Add(oPLColumn)
	Else If oPLColumn.PropertyType = kItemQuantityPartsListProperty Then
		oItemQtyCols.Add(oPLColumn)
	Else If oPLColumn.PropertyType = kUnitQuantityPartsListProperty Then
		oUnitQtyCols.Add(oPLColumn)
	End If
Next


'Cycle through each row of the parts list and modify only the BOM columns
'using the list from above and the Cell_Checker subroutine
For Each oPLRow In oPartsList.PartsListRows
	If oQtyCols.Count > 0 Then
		For i = 0 To oQtyCols.Count - 1
			Cell_Checker(oPLRow.Item(oQtyCols.Item(i)))
		Next
	End If
	
	If oBaseQtyCols.Count > 0 Then
		For i = 0 To oBaseQtyCols.Count - 1
			Cell_Checker(oPLRow.Item(oBaseQtyCols.Item(i)))
		Next
	End If
	
	If oItemQtyCols.Count > 0 Then
		For i = 0 To oItemQtyCols.Count - 1
			Cell_Checker(oPLRow.Item(oItemQtyCols.Item(i)))
		Next
	End If
	
	If oUnitQtyCols.Count > 0 Then
		For i = 0 To oUnitQtyCols.Count - 1
			Cell_Checker(oPLRow.Item(oUnitQtyCols.Item(i)))
		Next
	End If
Next


iLogicVb.UpdateWhenDone = True


End Sub


Public Sub Cell_Checker (oPLCell As PartsListCell)
'Check to see if the cell has not been overriden and is set to 0, if so, replace the value with a "-"
Try
	If oPLCell.Static = False And oPLCell.Value = 0 Then
		oPLCell.Value = "-"
	'Check to see if the underlying assembly quantity is 0 for Static cells and make the change to "-"
	'if the assembly quantity is 0.  Otherwise, leave the overriden value in place
	Else If oPLCell.Static = True
		Temp_Val = oPLCell.Value
		oPLCell.Static = False
		If oPLCell.Value = 0 Then
			oPLCell.Value = "-"
		Else 
			oPLCell.Value = Temp_Val
		End If
	End If
Catch
End Try


End Sub

[petestrycharske · Accepted solution]

Here is the code that will impact ANY column that contains the characters "qty".  Thanks again @A.Acheson!!!

'Define the necessary objects
Dim oDrawDoc As DrawingDocument = ThisDoc.Document

Dim oSheet As Sheet = oDrawDoc.ActiveSheet

Dim oPartsList As PartsList = oSheet.PartsLists.Item(1)

Dim oPLColumn As PartsListColumn
Dim oPLRow As PartsListRow
Dim oPLCell As PartsListCell
Dim oQtyCols As New List (Of PartsListColumn)
Dim oBaseQtyCols As New List(Of PartsListColumn)
Dim oItemQtyCols As New List (Of PartsListColumn)
Dim oUnitQtyCols As New List (Of PartsListColumn)

'iterate through each row of the parts list
For i = 1 To oPartsList.PartsListRows.Count
	oPLRow = oPartsList.PartsListRows.Item(i)
	'look at each column in the row
	For j = 1 To oPartsList.PartsListColumns.Count
		oPLColumn = oPartsList.PartsListColumns.Item(j)
		oPLCell = oPLRow.Item(j)
		'Check to see if the column title contains the letters "qty"
		If oPLColumn.Title.Contains("Qty") Then 
			Try
				'Check to see if the cell has not been overriden and is set to 0, if so, 
				'replace the value With a "-"
				If oPLCell.Static = False And oPLCell.Value = 0 Then
					oPLCell.Value = "-"
				'Check to see if the underlying assembly quantity is 0 for Static cells and make the change 
				'to "-" if the assembly quantity is 0.  Otherwise, leave the overriden value in place
				Else If oPLCell.Static = True
					Temp_Val = oPLCell.Value
					oPLCell.Static = False
					If oPLCell.Value = 0 Then
						oPLCell.Value = "-"
					Else 
						oPLCell.Value = Temp_Val
					End If
				End If
			Catch
			End Try
		End If
	Next
Next


iLogicVb.UpdateWhenDone = True

[A.Acheson]

This looks to be a VB.NET method. Once you have a string just type "." and the dropdown will bring up the list of methods available. 

https://docs.microsoft.com/en-us/dotnet/api/system.string.contains?view=net-6.0

The equivalent in VBA is 

InStr([ start ], string1, string2, [ compare ])

https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/instr-function

Actually I likely have never tried to use this in VBA so I learned something in checking out the origins. As regards case sensitivity I believe both VB.NET and VBA are not case sensitive but I do prefer to keep things uniform regardless. 

Glad I could help. 

[WCrihfield]

If I'm correct in my inspection of the two resulting codes, I believe you could greatly condense the code and maintain its current functionality.  Since you are doing the same end task on every cell that is within any of those quantity type columns, why not just all the columns to the same single List object, using either a Select Case...Case block or If...ElseIf block, then using a single loop later for just that one List object, instead of different loops for 4 different lists.  If we do this, we could cut the code down to at least 2/3 its original size.

Sort of like this:

Sub Main
	'Define the necessary objects
	Dim oDrawDoc As DrawingDocument = ThisDoc.Document
	Dim oSheet As Sheet = oDrawDoc.ActiveSheet
	Dim oPartsList As PartsList = oSheet.PartsLists.Item(1)
	Dim oPLColumn As PartsListColumn
	Dim oPLRow As PartsListRow
	Dim oPLCell As PartsListCell
	Dim oQtyCols As New List (Of PartsListColumn)

	'Iterate through every column in the parts list, identify it as a Qty column
	'then add it to a list
	For Each oPLColumn In oPartsList.PartsListColumns
		If oPLColumn.PropertyType = kQuantityPartsListProperty Or _
			oPLColumn.PropertyType = kBaseQuantityPartsListProperty Or _
			oPLColumn.PropertyType = kItemQuantityPartsListProperty Or _
			oPLColumn.PropertyType = kUnitQuantityPartsListProperty Then
			oQtyCols.Add(oPLColumn)
		End If
	Next

	'Cycle through each row of the parts list and modify only the BOM columns
	'using the list from above and the Cell_Checker subroutine
	For Each oPLRow In oPartsList.PartsListRows
		If oQtyCols.Count > 0 Then
			For i = 0 To oQtyCols.Count - 1
				Cell_Checker(oPLRow.Item(oQtyCols.Item(i)))
			Next
		End If
	Next
	iLogicVb.UpdateWhenDone = True
End Sub


Public Sub Cell_Checker (oPLCell As PartsListCell)
	'Check to see if the cell has not been overriden and is set to 0, if so, replace the value with a "-"
	Try
		If oPLCell.Static = False And oPLCell.Value = 0 Then
			oPLCell.Value = "-"
		'Check to see if the underlying assembly quantity is 0 for Static cells and make the change to "-"
		'if the assembly quantity is 0.  Otherwise, leave the overriden value in place
		Else If oPLCell.Static = True
			Temp_Val = oPLCell.Value
			oPLCell.Static = False
			If oPLCell.Value = 0 Then
				oPLCell.Value = "-"
			Else 
				oPLCell.Value = Temp_Val
			End If
		End If
	Catch
	End Try
End Sub

[WCrihfield]

Better yet.  If we go with just checking if the column title contains "Qty", then we can condense it down even further, while still making use of the single List object and still using For Each loops instead of those pesky For I loops.  Here is a fairly condensed version using that route.

Sub Main
	'Define the necessary objects
	Dim oDrawDoc As DrawingDocument = ThisDoc.Document
	Dim oSheet As Sheet = oDrawDoc.ActiveSheet
	Dim oPartsList As PartsList = oSheet.PartsLists.Item(1)
	Dim oQtyCols As New List(Of PartsListColumn)
	oQtyCols.AddRange(oPartsList.PartsListColumns.OfType(Of Inventor.PartsListColumn).Where(Function(c) c.Title.Contains("Qty")))
	If oQtyCols.Count = 0 Then Exit Sub
	For Each oPLRow As PartsListRow In oPartsList.PartsListRows
		For Each oQtyCol In oQtyCols
			oCell = oPLRow.Item(oQtyCol)
			Try
				If oCell.Static = False And oCell.Value = "0" Then 'the value is a String
					oPLRow.Item(oQtyCol).Value = "-"
				Else If oCell.Static = True
					Temp_Val = oCell.Value
					oCell.Static = False
					If oCell.Value = 0 Then
						oCell.Value = "-"
					Else 
						oCell.Value = Temp_Val
					End If
				End If
			Catch
			End Try
		Next
	Next
	iLogicVb.UpdateWhenDone = True
End Sub