iLogic Multivalue parameter from excel table based on criteria

[Pav3L]

Hello,

I would like to create multivalue parameter Tool_Number with values taken from an embedded excel table column B, based on model parameter Flange_Diameter and excel table filter column A.

For example if Flange_Diameter is 630 mm, the multivalue parameter Tool_Number should show only Tool Numbers that are >(Flange_Diameter-150) <= Flange_Diameter.

The resulting list should be: 5506, 5553, 4419, 5489, 8839, 5532 A, 4111, 10162, QUINTAS, 10299.

I found a similar thread on the forum https://forums.autodesk.com/t5/inventor-ilogic-api-vba-forum/ilogic-excel-multivalue-parameter-for-f...

but it did not help me... it uses only numbers with particular increments and my criteria is more difficult.

I am able to make a simple rule (If...then...ElseIf...End if) where I write the Tool Numbers to particular Diameters, but the table where I want to use it is much longer with several types of Tools. I do not know how API & VBA works.

Is there a kind soul here who could write the rule for me if it can be done?

Thank you very much

[jfenter]

I find it's easier to define the excel file first (this allows you the freedom to move the excel file later with minimal code updates).  

Then create the MultiValue parameter Flange_Diameter from your excel values in Column A.  I create a SharedVariable to find the active Flange_Diameter selected.  Then set your Tool_Number parameter to be the value given in the the coordinating row.

*Edit*

I should also mention that with the method I'm describing, you won't need to create the multivalue list for Tool Number.  The Tool Number parameter text will be driven by the Flange_Diameter chosen and should update automatically with each new selection.   

ExcelFile = "3rd Party:Embedding 1"

MultiValue.List("Flange_Diameter") = GoExcel.CellValues(ExcelFile, "Sheet1", "A2", "")
SharedVariable("FlangeSearch") = GoExcel.FindRow(ExcelFile, "Sheet1", "Flange Diameter", "=", Flange_Diameter)

Tool_Number = GoExcel.CurrentRowValue("Tool Number") 

[Pav3L]

Thank you for the input, but I need it differently.

In your example I am taking the diameter from the table (which I do not want) and I am given an option of one tool, but there is multiple tools to choose from for a particular diameter.

The Flange_diameter parameter will be given by a model (not the table) and then I need a filtered list of tools to choose from based on the Flange_diameter. Each tool is suitable for a particular diameter, these are the Flange diameters in the table.

In the model the Flange_Diameter can be any number from 1 to 1250 mm (including decimal numbers). I need a list of tools to choose from for the particular Flange_Diameter. Few options is enough, I do not want a whole list of dozens of Tool numbers, just few tools to choose from, a range of Flange_Diameter-150 mm is enough.

What I have in mind is that the rule should check what is the current value of the parameter Flange_Diameter and make a Multivalue list of the Tool Numbers checking if the Flange Diameter in the table is in the range of the model parameter >(Flange_Diameter-150) <= Flange_Diameter and creating the Multivalue list only of the particular Tool Numbers that fit the range criteria.

Then in a user Form I choose the exact tool from the created list and link more parameters from another columns to the model, I did not show these in the example, I know how to do this, but the above is outside of my knowledge.

[WCrihfield · Accepted solution]

Try this local iLogic rule.  It worked in my test on your supplied part file.  I didn't bother downloading the separate Excel file, because it was already embedded in the part file.  I did notice that the name of the embedded Excel file was a little different than what is automatically inserted when you use the one iLogic snippet, so I had to slightly modify it to match what was in the document.  I don't know if it had been renamed or what, but this worked for me.

Dim oFD As Double = Flange_Diameter
Dim oFile As String = "3rd Party:Embedded1"
GoExcel.TitleRow = 1
GoExcel.FindRowStart = 2
Dim oToolNumbers As New List(Of String)
Dim oTN As String = String.Empty
For oRow As Integer = 2 To 19
	If GoExcel.CellValue(oFile, "Sheet1", "A" & oRow) = oFD Then
		oTN = GoExcel.CellValue(oFile, "Sheet1", "B" & oRow)
		oToolNumbers.Add(oTN)
	End If
Next
MultiValue.SetList("Tool_Number", oToolNumbers.ToArray)

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

[Pav3L · Accepted solution]

Hi,

this is almost what I wanted, just a little modification and it does exactly what I wanted.

The embedded file was renamed by me, because when I insert it the name uses "Czech characters" characters and I wanted to avoid problems if someone opens it in English.

I will definitely look at the links you mentioned, it looks interesting.

Thank you very much for the code!

Below is the modified code, I have added Range parameter and sorted the list (I found this thanks to google 🙂

Dim oRange As Double = Range
Dim oFDBig As Double = Flange_Diameter
Dim oFDSmall As Double = Flange_Diameter - oRange
Dim oFile As String = "3rd Party:Embedded1"
GoExcel.TitleRow = 1
GoExcel.FindRowStart = 2
Dim oToolNumbers As New List(Of String)
Dim oTN As String = String.Empty
For oRow As Integer = 2 To 19
	If GoExcel.CellValue(oFile, "Sheet1", "A" & oRow) <= oFDBig AndAlso GoExcel.CellValue(oFile, "Sheet1", "A" & oRow) >= oFDSmall Then
		oTN = GoExcel.CellValue(oFile, "Sheet1", "B" & oRow)
		oToolNumbers.Add(oTN)
	End If
Next
MultiValue.SetList("Tool_Number", oToolNumbers.ToArray)

Dim arr As ArrayList
arr = MultiValue.List("Tool_Number")
arr.Sort()
MultiValue.List("Tool_Number") = arr

[WCrihfield]

Hi @Pav3L.  Just another hint.  You don't have to convert the multi-value parameter values to an ArrayList, then sort them, then put them back into the multi-value parameter.  The values were already in a 'List' object originally, and that 'List' object also has a 'Sort' method that will work for you.  So you can just sort them in the oToolNumbers list before adding them to the multi-value parameter the first time.

Like this:

Dim oRange As Double = Range
Dim oFDBig As Double = Flange_Diameter
Dim oFDSmall As Double = Flange_Diameter - oRange
Dim oFile As String = "3rd Party:Embedded1"
GoExcel.TitleRow = 1
GoExcel.FindRowStart = 2
Dim oToolNumbers As New List(Of String)
Dim oTN As String = String.Empty
For oRow As Integer = 2 To 19
	If GoExcel.CellValue(oFile, "Sheet1", "A" & oRow) <= oFDBig AndAlso GoExcel.CellValue(oFile, "Sheet1", "A" & oRow) >= oFDSmall Then
		oTN = GoExcel.CellValue(oFile, "Sheet1", "B" & oRow)
		oToolNumbers.Add(oTN)
	End If
Next
oToolNumbers.Sort
MultiValue.SetList("Tool_Number", oToolNumbers.ToArray)

[Pav3L]

Hi @WCrihfield
ok, thanks. I am still learning 🙂