ilogic use of Array.FindAll, how to use the Function (?)

[Guido.LangeTuchscherer]

Hello there,

I have been working with Inventor for some 3-4 years and now got the chance to aquire some experience in iLogic, which is a great tool i think and has a lot of potential.

I got some experience with mainly fortran and matlab, bute still don't get the syntax of ilogic or  VB.NET.

I am trying to import excel lists and then depending on the user input writing data into iProperties, basically similar to an content center pik window.

If the user picked a value or data set <lookUp>, then i would need to search the array containing the first excel column for this data and get all the indices back, so I can show the options for the second pik from the second column.

Basically this works:

Dim MyArrayList As New ArrayList 
MyArrayList.Add("daten 1") 
MyArrayList.Add("daten 2") 
MyArrayList.Add("daten 1") 
MyArrayList.Add("daten 3") 

lookUp = "daten 1"

jIndex = 0 'Index für die Index Liste
Dim foundValuesIndicies As New ArrayList 'Leere Liste definieren

'Für jedes Element in vorhandener Liste durchsuchen
For Each oItem In MyArrayList 'oItem ist der Inhalt der Zelle e.g. Bl 3
	'Wenn der Inhalt der Zelle mit dem Suchwert übereinstimmt, dann zu Zeilen-Index Liste hinzufügen 
	If oItem = lookUp Then
		foundValuesIndicies.Add(jIndex)
	End If
	jIndex = jIndex + 1 'Index zählen
Next

but I would prefer to use FindAll to return the indices if possible:

Dim MyArrayList As New ArrayList
MyArrayList.Add("daten 1")
MyArrayList.Add("daten 2")
MyArrayList.Add("daten 1")
MyArrayList.Add("daten 3")

lookUp = "daten 1"

Dim zIndex As New Object() = Array.FindAll(MyArrayList, Function(?) lookUp)

I don't understand the Function, I know you can specifiy one in VB.Net which does not seem to work in iLogic, probably as you are in the sub main. I don't understand the term predicate or likewise explanations either.

Any help is apreciated, thanks.

[JhoelForshav]

Hi @Guido.LangeTuchscherer 

Are you looking for something like this? 🙂

Dim MyArrayList As New ArrayList
MyArrayList.Add("daten 1")
MyArrayList.Add("daten 2")
MyArrayList.Add("daten 1")
MyArrayList.Add("daten 3")

lookUp = "daten 1"

Dim lookUpArr As Object() = Array.FindAll(Of Object)(MyArrayList.ToArray(), Function(t) t = lookUp)

'Just to verify
For Each oVal As String In lookUpArr
	MsgBox(oVal)
Next

[Guido.LangeTuchscherer]

Thanks@JhoelForshav for your input,

this returns the content of the

MyArrayList which is equal to lookUp

I would need the Index of those or all matches (0 and 3 in this case), so I can use it to read in a second ArrayList (next column) by the Index.

Just like:

foundValues = MyArrayList.IndexOf(lookUp) 'returns only the first index

returns what I need, but only the first index, if several matches occur.

Basically my first code does what I want, the lists are just really big and I would like to make it a bit faster as this will kind of end in a big loop.

I understand t in the Function is just a variable then?

[WCrihfield]

Would you maybe be thinking of using a two dimensional array, instead of multiple single dimensional arrays?

Dim oOptions(2, 1) As String
oOptions(0, 0) = "First"
oOptions(0, 1) = "One"
oOptions(1, 0) = "Second"
oOptions(1, 1) = "Two"
oOptions(2, 0) = "Third"
oOptions(2, 1) = "Three"
For i As Integer = 0 To 2
	MsgBox(oOptions(i,0) & " - " &  oOptions(i,1))
Next

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

Vote For My IDEAS 💡and Explore My CONTRIBUTIONS

Inventor 2020 Help | Inventor Forum | Inventor Customization Forum | Inventor Ideas Forum

[WCrihfield]

This article may also interest you. It deals with filtering the options available within a secondary column (and third) based on selections made within the first column.  Then further filtering which options will then be available within the third column, based on the first two columns.

https://inventortrenches.blogspot.com/2017/02/ilogic-dynamic-multivalue-parameter.html 

[Guido.LangeTuchscherer]

@WCrihfield 

Thanks and yes this is the further plan to use a multidimensional array and sort it for the lists to show.

I just thought I might figure out the Index, rather than using a for loop, so I just need to read certain values of the second then third... array column to make it faster, as it will contain several 100 lines of data.

[WCrihfield]

OK. Here's another link you may like then from the Microsoft's website.

(in the upper right portion of the website, change the drop-down options from C# to VB, to see the visual basic examples.)

Array.FindIndex Method 

[WCrihfield]

Here is a maybe a simpler way to capture the index numbers of the items within your array that match the value you are searching for:

'get count of items from source (example = 4), then subtract 1
Dim oSourceCount As Integer = 4
Dim oCount As Integer = oSourceCount - 1 'because arrays use item zero as its first item
Dim Datens(oCount) As String
Datens(0) = "daten 1"
Datens(1) = "daten 2" 
Datens(2) = "daten 1" 
Datens(3) = "daten 3" 

lookUp = "daten 1"

Dim oItemNums As New List(Of Integer)
For i As Integer = 0 To Datens.Length -1
	If Datens(i) = lookUp Then
		oItemNums.Add(i)
	End If
Next
InputListBox(" ",oItemNums," ","The items at these indexes matched your query.")

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

[WCrihfield]

@Guido.LangeTuchscherer 

Here is an example using Excel, to better reflect your application.

For this example, I created an Excel file named "Test Index.xlsx" and saved it to my profile's 'DeskTop' directory.

Within this Excel file I simply entered the following data:

- in Cell A1, I entered "One"

- in Cell A2, I entered "Three"

- in Cell A3, I entered "Eight"

- in Cell A4, I entered "One" again

Then I set up the iLogic rule like this:

Imports System.Environment
Imports System.Environment.SpecialFolder
Dim oDeskTop As String = Environment.GetFolderPath(System.Environment.SpecialFolder.DesktopDirectory)
Dim oFileName As String = "Test Index.xlsx"
Dim oFile As String = oDeskTop & "\" & oFileName
Dim oDataEntries As New ArrayList
oDataEntries = GoExcel.CellValues(oFile, "Sheet1", "A1", "A4")
Dim oFind As String = "One"
Dim oItemNums As New List(Of Integer)
For i As Integer = 0 To oDataEntries.Count - 1
	If oDataEntries.Item(i) = oFind Then
		oItemNums.Add(i)
	End If
Next
InputListBox(" ",oItemNums," ","The items at these indexes matched your query.")

You'll notice that within the code I defined a variable called oFind as a String to hold the value I'm searching for ("One").  This is on purpose, because I know I entered "One" two times within that range of cells in the Excel file.

When I run this rule, I get the InputListBox that pop's up and shows me that index "0" & index "3" within that ArrayList contained the value I was searching for.

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

[Guido.LangeTuchscherer]

Thanks @WCrihfield  for your input,

so I can tighten the for loop up.

Is there really no way of using FindAll or FindIndex to find all indecies matching a value, maybe using a multidimensional array where the first value is the index, so compare to column 2 and return column 1, in one row?

Like:

Dim lookUpArr As Object() = Array.FindAll(Of Object)(MyArrayList.ToArray(x,t), Function(t) t = lookUp)
Return X

 I know how to get information of the second row, could be done within the for loop or using the index, but maybe I am going at it the wrong way and it is more like a database function?

[WCrihfield]

Could you maybe provide a sample Excel file that contains a representation of the multiple columns of data (or a detailed description of what it might contain, so that a similar file could be constructed on our end, for testing), similar to what you will be working with.  And some step by step detailed instructions about what you want to be able to do with the data within.  It kind of sounds like your end goal is to search the first column of data to find which cells (and the row numbers) contain the searched for string, then using the row numbers where they were found, retrieve the data within the second column of those found rows.  Is that an accurate assumption?  If so, what format do you need the resulting data entries to be in (exe: List(Of String), ArrayList, regular array oStrings(), just assign each directly to a variable or property without putting them into a collection, etc.)?

[Guido.LangeTuchscherer]

@WCrihfield 

Yes this is what I am trying to do 🙂

Do I use the wrong array or List?

The file is attached as well.

'basically i use this to read column data from excel
'Dim arrayListA As New ArrayList
'arrayListA = GoExcel.CellValues(filename, "Neu", "A1", "")
'arrayListB = GoExcel.CellValues(filename, "Neu", "B1", "")

'I might be using the wrong type of array, lists ?

'For testing we can use these arrayLists
Dim MyArrayListA As New ArrayList
MyArrayListA.Add("daten A1")
MyArrayListA.Add("daten A2")
MyArrayListA.Add("daten A1")
MyArrayListA.Add("daten A3")
MyArrayListA.Add("daten A1")
MyArrayListA.Add("daten A4")

Dim MyArrayListB As New ArrayList
MyArrayListB.Add("daten C1")
MyArrayListB.Add("daten B2")
MyArrayListB.Add("daten B1")
MyArrayListB.Add("daten C3")
MyArrayListB.Add("daten B1")
MyArrayListB.Add("daten C3")

'I would like to look up the following in the first list and return the List of indecies
'This will be user input from a list later
lookUpA = "daten A1"

'To then look up in in the second List and return the remaining indecies
'This will be user input from a list later
lookUpB = "daten B1"

'To store the indices, currently not used
'Dim indicesList As New ArrayList

'This simply returns all entries in the List containing the searched data, which for me is an array containing 3 times the searched string
'I know this can be useful to search only for a part of a string and return all containing this part, 
' but thats Not what I need, I need the Index Of those data
'Dim lookUpArr As Object() = Array.FindAll(Of Object)(MyArrayListA.ToArray(), Function(t) t = lookUpA)

'Show the found data as a List
'InputListBox("Found Indecies?", lookUpArr, lookUpArr(0), "Search test")

'I could find the indices by looping thorugh the list like this
jIndex = 0 'Index 
'Define new list
Dim foundIndiciesA As New ArrayList

'Search for each item in the arrayList and count the index and add it to an arrqayList
For Each oAItem In MyArrayListA
	If oAItem = lookUpA Then
		'If the matched data is contained, write the index into a List
		foundIndiciesA.Add(jIndex)
	End If
	jIndex = jIndex + 1 'Count index
Next
'Show arrayList
InputListBox("Found Indecies of A?", foundIndiciesA, foundIndiciesA(0), "Search test")

'Find second array size, so I don't need to loop through the entire arrayList
'Dim arraySizeB As Integer 
'arraySizeB = UBound(MyArrayListB) 	'Doesn't work?
'arraySizeB = MyArrayListB.Length	'Doesn't work either?
'MessageBox.Show(arraySizeB)		'Am I using a wrong type of array ot list or do I need to trim it in size, make it finite?

'###--Rather then doing the loops i would prefer: -###
'Dim lookUpArr As Object() = Array.FindAll(Of Object)(MyArrayList.ToArray(x,t), Function(t) t = lookUp)
'Return X 'all indices

'And then I can find the indices for the second List by looping again
Dim foundIndiciesB As New ArrayList
'For iIndex As Integer = 0 To MyArrayListB.Length-1 'which doesn't work, see above
'So instead go through the found Index List
For Each oBItem In foundIndiciesA
	If MyArrayListB(oBItem) = lookUpB Then
		'If the matched data is contained return Index in new List
		foundIndiciesB.Add(oBItem)
	End If
Next
InputListBox("Found Indecies of B?", foundIndiciesB, foundIndiciesB(0), "Search test")

'The final result is basically a user choice of the certain last remaining row and then all the data from that row is written into iProperties
'Which i figured out how to do, apart from the material I did not test yet, but thats for later

[WCrihfield · Accepted solution]

I created another test Excel file to test this scenario.  This time my Excel file has two columns of data.  I attached this Excel file, but since I might be using a newer version of Excel than you, you might not be able to open it, so I also attached a screen shot of the data within.

This time, instead of extracting all the data from the two columns in the Excel file and building two ArrayLists, I'm dealing directly with the data in those Excel columns (to cut out the middle man).  I am continuing to use some ArrayLists within this code, since you are familiar with them (and they are native objects), but only to hold the row numbers where matches are found.

• This code starts a new instance of Excel, opens the specified Excel workbook file, and looks at the specified sheet (by name).
• It then finds the last row being used, and uses that row number to limit the loop searches.
• It then defines a value to search for
  • Manually right now, but I could present the user with an InputListBox of all unique values in column one, and have the user choose one.  I'm not sure how you are currently specifying this value.
• Then creates an ArrayList to store the row numbers which contain matching values
• It then loops through all the cells in that column between the first row and the pre-established oLastRowUsed, checking for the specified value.  If found, the row number is added to the first ArrayList.
• It then defines the value to search for within the second column (again, there are many ways to specify this)
• It then loops through the second column searching for that second value, but this time, it is only searching the cells within that second column that are on those previously found row numbers (per the filter effect)(I assume this is how you want to do it).
• I then set up a check system
  • If there are zero matches, or more than one matches in that second loop, it lets you know with a MsgBox.
  • Else...
    • It pushes that only value within oCol2Rows ArrayList to the oRow variable
    • It assigns the values of the cells of the first and second column of that row to two variables
    • I then included an optional MsgBox, letting you know of the success, and which row number is the winner.
    • It then writes a combination of those two values to a custom iProperty of the 'active' document.
• It then closes the workbook, quits the application, and cleans up.

After I ran this rule, I checked in my iProperties dialog > Custom tab, and there was a new property who's name was "Excel Data", and value was "Sunday - Maybe", and type was "Text", as expected (screen captured image attached).

Here's the code:

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop.Excel

Dim oFileName As String = "C:\Temp\Find Indexes.xlsx"
Dim oSheetName As String = "Sheet1"
Dim oExcelApp As New Microsoft.Office.Interop.Excel.ApplicationClass
oExcelApp.DisplayAlerts = False
Dim oWB As Workbook = oExcelApp.Workbooks.Open(oFileName)
Dim oWS As Worksheet = oWB.Sheets.Item(oSheetName)
Dim oLastRowUsed As Integer = oWS.UsedRange.Rows.Count
'MsgBox("Last row being used is Row#:  " & oLastRowUsed)
Dim oCells As Range = oWS.Cells

Dim oCol1Find As String = "Sunday"
Dim oCol1Rows As New ArrayList
Dim oRow As Integer
'First number is Row index, second number is Column Index
For oRow = 1 To oLastRowUsed
	If oCells.Item(oRow, 1).Value = oCol1Find Then
		oCol1Rows.Add(oRow)
	End If
Next

Dim oCol2Find As String = "Maybe"
Dim oCol2Rows As New ArrayList
For Each oRow In oCol1Rows
	If oCells.Item(oRow, 2).Value = oCol2Find Then
		oCol2Rows.Add(oRow)
	End If
Next

If oCol2Rows.Count = 0 Then
	MsgBox("No rows contained both searched for values.")
ElseIf oCol2Rows.Count > 1 Then
	MsgBox("Multiple rows contained both searched for values.")
Else
	oRow = oCol2Rows.Item(0)
	Dim oVal1 As String = oCells.Item(oRow, 1).Value
	Dim oVal2 As String = oCells.Item(oRow, 2).Value
	MsgBox("Only Row #:  " & oRow & " matched both seached for values.")
	iProperties.Value("Custom", "Excel Data") = oVal1 & " - " & oVal2
End If

oWB.Close
oWB = Nothing
oWS = Nothing
oExcelApp.Quit
oExcelApp = Nothing

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

[Guido.LangeTuchscherer]

Hi@WCrihfield 

Thank you alot for putting the effort in.

I thought reading an excel several times would be more time consuming rather than doing the operations on arrays?

Also I would read the excel from at least 5 (up to 20) different work stations at the same time.

Conclusively Array.FindAll is simply the wrong function to go after then and to give an answer to the main question, in case somebody stumbles on it:

Array.FindAll can be used on one dimensional arrays and only returns the the matching content?