iLogic to select stock number from excel file

[KWarrenCA]

I'm working on an iLogic to look at the sheet metal flat pattern dimensions and select the correct part number. Right now I have it working correctly if the size is in between 2 numbers. The issue I'm running into is if say a 48 x 120 sheet is not in stock or unavailable but a 60 x 120 is in stock to select the larger sheet stock number.

I attached the excel file and the code below.

I also only want this to run on a sheet metal part. How would I go about doing that.

''Sheet Metal Stock Number
'Pulls flat pattern extents
	Dim oLength As Double
	Dim oWidth As Double

	ex_Length = SheetMetal.FlatExtentsLength
	ex_Width = SheetMetal.FlatExtentsWidth

'Checks flat pattern to see what length is longer and defines the longer side to be length
	If ex_Length > ex_Width Then
		oLength = ex_Length
		oWidth = ex_Width
	Else
		oWidth = ex_Length
		oLength = ex_Width
	End If



'Define Excel File to Lookup
	ExcelFile = "C:\CA Workspace\CAD Standards\CA Library A\iLogic Automation\CA - Sheetmetal Part Number Selection - Table.xlsx"
'Ignores any excel alerts
	GoExcel.DisplayAlerts = False

'Message box warning for non stocked items
	NonStockedNote = "This Item is not Stocked. Check with Purchasing on Availablity!"
	
	Dim SheetMetalStyle As String
	SheetMetalStyle = SheetMetal.GetActiveStyle
	SheetMaterial = iProperties.Material
	
'IF the sheet is larger than 72 X 144 notifys user sheet is custom order
If (oWidth >72) AndAlso (oLength >144) Then
	i = MessageBox.Show("Sheet is too large for Stocked material. Check with Purchasing if we can order custom sheet sizes", "Too Large of Sheet", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1)
	SheetPartnumber = "Check With Purchasing if we can order a sheet this size."
End If


''Setting Width depending on sheet size
If (oWidth <= 48) AndAlso (oWidth >0) Then
	oSetWidth = 48
Else If (oWidth <= 60) AndAlso (oWidth > 48.000001)
	oSetWidth = 60
Else If (oWidth <=72) AndAlso (oWidth > 60.000001) Then
	oSetWidth = 72
End If

''Setting Length depending on sheet size
If (oLength <= 120) AndAlso (oLength > 0)
	oSetLength = 120
Else If (oLength <= 144) AndAlso (oLength > 120.000001)
	oSetLength = 144
End If

'i = MessageBox.Show(oSetWidth & OSetLength, "Sheet Size", MessageBoxButtons.OK, MessageBoxIcon.None, MessageBoxDefaultButton.Button1)


Dim rowNum As Integer = GoExcel.FindRow(ExcelFile, "Stocked", "Sheet Metal Rule", "=", SheetMetalStyle, "Material", "=", iProperties.Material, "Sheet Width (Short Side)", "=", oSetWidth, "Sheet Length (Long Side)", "=", oSetLength)
'If no option is available then message box
If (rowNum >0) Then

		If GoExcel.CurrentRowValue("Stocked") = "Yes" Then
			SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
 			question = MessageBox.Show("The Stock Number will be replaced with the "& SheetPartnumber &" are you sure you want to procceed", "Warning Stock Number Change", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
				If question = vbNo Then
					Else If question = vbYes Then
				iProperties.Value("Project", "Stock Number") = SheetPartnumber
				Return
			End If
		Else If GoExcel.CurrentRowValue("Stocked") = "No" Then
			SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
			question = MessageBox.Show(NonStockedNote & "The Stock Number will be replaced with the "& SheetPartnumber &" are you sure you want to procceed", "Non Stocked Sheet", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
				If question = vbNo Then
					Else If question = vbYes Then
				iProperties.Value("Project", "Stock Number") = SheetPartnumber
				Return
			End If
		End If
Else If (rowNum <0) Then
	i = MessageBox.Show("No Sheet Size Available. Check Purchasing for Part Number.", "No Sheet Size Available", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)

End If

[WCrihfield · Accepted solution]

Hi @KWarrenCA.  As for how to set this rule up so that it will only run on a sheet metal part, I think the following bit of code should do that for you, if put right at the very start of your rule.

If ThisDoc.Document.SubType <> "{9C464203-9BAE-11D3-8BAD-0060B0CE6BB4}" Then
	MsgBox("This is not a Sheet Metal Part.  Exiting rule.", vbCritical, "iLogic")
	Exit Sub 'or Return
End If

I will revue the other request(s) and the Excel file next, if time/opportunity permits.

[KWarrenCA · Accepted solution]

I was able to rework this code to get it to work the way I wanted it to by looking for stocked items first if no stocked items were found it would search again for non stocked items.

''Sheet Metal Stock Number
'Checks to see if the file is sheet metal. If not it will return.
If oSubType = "Sheet Metal" Then

'Pulls flat pattern extents
	Dim oLength As Double
	Dim oWidth As Double

	ex_Length = SheetMetal.FlatExtentsLength
	ex_Width = SheetMetal.FlatExtentsWidth

'Checks flat pattern to see what length is longer and defines the longer side to be length
	If ex_Length > ex_Width Then
		oLength = ex_Length
		oWidth = ex_Width
	Else
		oWidth = ex_Length
		oLength = ex_Width
	End If



'Define Excel File to Lookup
	ExcelFile = "C:\CA Workspace\CAD Standards\CA Library A\iLogic Automation\CA - Sheetmetal Part Number Selection - Table.xlsx"
'Ignores any excel alerts
	GoExcel.DisplayAlerts = False

'Message box warning for non stocked items
	NonStockedNote = "This Item is not Stocked. Check with Purchasing on Availablity!"
	
	Dim SheetMetalStyle As String
	SheetMetalStyle = SheetMetal.GetActiveStyle
	SheetMaterial = iProperties.Material
	
'Checks the sheet if it is larger than 72 X 144 notifies user sheet is custom order
If (oWidth >72) AndAlso (oLength >144) Then
	i = MessageBox.Show("Sheet is too large for Stocked material. Check with Purchasing if we can order custom sheet sizes", "Too Large of Sheet", MessageBoxButtons.OK, MessageBoxIcon.Hand, MessageBoxDefaultButton.Button1)
	SheetPartnumber = "Check With Purchasing if we can order a sheet this size."
End If


''Setting Width depending on sheet size
If (oWidth <= 48) AndAlso (oWidth >0) Then
	oSetWidth = 48
Else If (oWidth <= 60) AndAlso (oWidth > 48.000001)
	oSetWidth = 60
Else If (oWidth <=72) AndAlso (oWidth > 60.000001) Then
	oSetWidth = 72
End If

''Setting Length depending on sheet size
If (oLength <= 120) AndAlso (oLength > 0) Then
	oSetLength = 120
Else If (oLength <= 144) AndAlso (oLength > 120.000001)Then
	oSetLength = 144
End If

'Used to verify it works on sheet size
'i = MessageBox.Show(oSetWidth & OSetLength, "Sheet Size", MessageBoxButtons.OK, MessageBoxIcon.None, MessageBoxDefaultButton.Button1)


Dim rowNum As Integer = GoExcel.FindRow(ExcelFile, "Stocked", "Sheet Metal Rule", "=", SheetMetalStyle, "Material", "=", iProperties.Material, "Sheet Width (Short Side)", ">=", oSetWidth, "Sheet Length (Long Side)", ">=", oSetLength, "Stocked", "=", "Yes")

'Checks if no option is available then displays message box to user
If (rowNum >0) Then
			SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
			CostCode = GoExcel.CurrentRowValue("Cost Code")
 			question = MessageBox.Show("The Stock Number will be replaced with the "& SheetPartnumber &" are you sure you want to procceed?", "Warning Stock Number Change", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
				If question = vbNo Then
					Return
					Else If question = vbYes Then
				iProperties.Value("Project", "Stock Number") = SheetPartnumber
				iProperties.Value("Project", "Cost Center") = CostCode
				Return
			End If
		
	Else If (rowNum <0) Then
	End If
	
Dim rowNum2 As Integer = GoExcel.FindRow(ExcelFile, "Stocked", "Sheet Metal Rule", "=", SheetMetalStyle, "Material", "=", iProperties.Material, "Sheet Width (Short Side)", ">=", oSetWidth, "Sheet Length (Long Side)", ">=", oSetLength, "Stocked", "=", "No")

If (rowNum2 >0)Then
			SheetPartnumber = GoExcel.CurrentRowValue("Part Number")
			CostCode = GoExcel.CurrentRowValue("Cost Code")
			question = MessageBox.Show(NonStockedNote & "The Stock Number will be replaced with the "& SheetPartnumber &" are you sure you want to procceed?", "Non Stocked Sheet", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
				If question = vbNo Then
					Return
					Else If question = vbYes Then
				iProperties.Value("Project", "Stock Number") = SheetPartnumber
				iProperties.Value("Project", "Cost Center") = CostCode
				Return
				End If
		Else If (rowNum2 <0) Then
			i = MessageBox.Show("No Sheet Size Available. Check Purchasing for Part Number.", "No Sheet Size Available", MessageBoxButtons.OK, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1)
		End If


Else 
	Return
End If