Creating 3D data table - Format help

[llorden4]

I'm converting a multi-dimensional chart into a data table and am trying to better consolidate my code to be friendly for reading/comparing as well as reduce the code footprint required, but I'm getting a bit lost on code format/syntax here and was hoping someone could help me out.  I'll explain in depth in case someone else is trying to accomplish this same task and may also find this helpful in their search.

I'm trying to take this supplied printed table and store it in my iLogic code as a Matrix/Array.

Column A (17 row entries) is my bolt size

Column B (12 column entries) is my material thickness

Row C (2 rows) are my desired distance values I want to look up

I have been able to digitize this table with the following code...

Dim oHoleChart(17, 12, 2) As Double				'(17)Bolt Size / (12)Material Thickness / (2)Distances C & E'
MyList = New Double(){1 + (1 / 8), 1 + (5 / 16), 1 + (3 / 8), 1 + (11 / 16), 2 + (1 / 16), 2 + (1 / 8), 2 + (3 / 16), 2 + (11 / 16), 3 + (9 / 16), 4 + (1 / 16), 4 + (9 / 16), 5 + (9 / 16)}
For i = 1 To 12		'1/4 inch bolts
	oHoleChart(1, i, 1) = MyList(1, i-1)
	oHoleChart(1, i, 2) = 1 / 2
Next
MyList = New Double(){1 + (1 / 4), 1 + (7 / 16), 1 + (1 / 2), 1 + (13 / 16), 2 + (3 / 16), 2 + (1 / 4), 2 + (5 / 16), 2 + (13 / 16), 3 + (11 / 16), 4 + (3 / 16), 4 + (11 / 16), 5 + (11 / 16)}
For i = 1 To 12		'3/8 inch bolts
	oHoleChart(2, i, 1) = MyList(i-1)
	oHoleChart(2, i, 2) = 1 / 2
Next
.
.
.

So if I wanted to lookup the "C" value for a 1/4" bolt on 9/16 material I could use oHoleChart(1, 6, 1) to find a value of 2.125

What I really want is to do this in a pair of nested For/Next loops so I can group all the "MyList" definitions together so it's friendlier to read against this printed Table and the 2nd loop would reduce my coding.  I can't quite figure the format, but here's the jist of what I'm wanting.

Dim oHoleChart(17, 12, 2) As Double				'(17)Bolt Size / (12)Material Thickness / (2)Distances C & E'
Dim MyList1(17, {}) As Double					'This doesn't work
'Dim MyList(17)									'This doesn't work
'Dim MyList(17, ())								'This doesn't work
MyList1(1, {}) = New Double() {1 + (1 / 8), 1 + (5 / 16), 1 + (3 / 8), 1 + (11 / 16), 2 + (1 / 16), 2 + (1 / 8), 2 + (3 / 16), 2 + (11 / 16), 3 + (9 / 16), 4 + (1 / 16), 4 + (9 / 16), 5 + (9 / 16) }
MyList1(2, {}) = New Double() {1 + (1 / 4), 1 + (7 / 16), 1 + (1 / 2), 1 + (13 / 16), 2 + (3 / 16), 2 + (1 / 4), 2 + (5 / 16), 2 + (13 / 16), 3 + (11 / 16), 4 + (3 / 16), 4 + (11 / 16), 5 + (11 / 16) }
.
.
.
MyList1(17, {}) = New Double() {3 + (9 / 16), 3 + (3 / 4), 3 + (13 / 16), 4 + (1 / 8), 4 + (1 / 2), 4 + (9 / 16), 4 + (5 / 8), 5 + (1 / 8), 6, 6 + (1 / 2), 7, 8 }

MyList2 = New Double() {1 / 2, 1 / 2, 3 / 4 , ...}	'value changes only for bolt size
For j = 1 To 17
	For i = 1 To 12		'1/4 inch bolts
		oHoleChart(j, i, 1) = MyList1(j, i-1)
		oHoleChart(j, i, 2) = MyList2(i-1)
	Next
Next

Is there a workflow here or is my original concept the only way to go?

[llorden4]

I did find a different approach that did get me to where I wanted and will share below for anyone interested.  In short, I just made one long list and used an algorithm to find the correct placement to grab the data.

I would still be interested in learning the answer to my original question if there is one.

Dim oHoleChart(17, 12, 2) As Double				'(17)Bolt Size / (12)Material Thickness / (2)Distances C & E'
'Mat thickness:  1 = 3/16, 2 = 1/4, 3 = 5/16, 4 = 3/8, 5 = 1/2, 6 = 9/16, 7 & 8 = 5/8 (Rad = 1 & 1 1/2), 9 = 3/4, 10 = 7/8, 11 = 1, 12 = 1 1/4
MyList1 = New Double() _
	{1 + (1 / 8), 1 + (5 / 16), 1 + (3 / 8), 1 + (11 / 16), 2 + (1 / 16), 2 + (1 / 8), 2 + (3 / 16), 2 + (11 / 16), 3 + (9 / 16), 4 + (1 / 16), 4 + (9 / 16), 5 + (9 / 16), _		'1/4 inch bolts
	1 + (1 / 4), 1 + (7 / 16), 1 + (1 / 2), 1 + (13 / 16), 2 + (3 / 16), 2 + (1 / 4), 2 + (5 / 16), 2 + (13 / 16), 3 + (11 / 16), 4 + (3 / 16), 4 + (11 / 16), 5 + (11 / 16), _		'3/8 inch bolts
.
.
.
	3 + (9 / 16), 3 + (3 / 4), 3 + (13 / 16), 4 + (1 / 8), 4 + (1 / 2), 4 + (9 / 16), 4 + (5 / 8), 5 + (1 / 8), 6, 6 + (1 / 2), 7, 8}							'3 inch bolts
	
MyList2 = New Double() {1 / 2, 1 / 2, 3 / 4, 1, 1, 1 + (1 / 4), 1 + (1 / 2), 1 + (1 / 2), 1 + (3 / 4), 2, 2, 2 + (1 / 2), 2 + (3 / 4), 3, 3 + (1 / 4), 3 + (3 / 4), 4}				'E values

For j = 1 To 17
	k = (j - 1) * 12
	For i = 1 To 12
		oHoleChart(j, i, 1) = MyList1(k + i - 1)
		oHoleChart(j, i, 2) = MyList2(j - 1)
	Next
Next

[Michael.Navara · Accepted solution]

In my opinion this approach is un-maitainable and obscure for future editors. Try to use objects instead. In my sample I show you how to create object for Bolt description and its thickness list and how to look for specific values  using LINQ query and For-Each statement. 

I create only few properties from your table, but it is very easy to extend my code to all properties (rows and columns)

Sub Main

	'Build List of BoltInfo objects
	Dim boltInfos As List(Of BoltInfo) = GetBoltInfos()

	'Usage sample
	Dim boltSize = 1 / 4
	Dim valueT = 9 / 16

	'Search using LINQ
	Dim thicknesInfos = boltInfos.Where(Function(bi) bi.BoltSize = boltSize).Select(Function(bi) bi.ThicknessInfos).First()
	Dim valueC = thicknesInfos.Where(Function(ti) ti.ValueT = valueT).Select(Function(ti) ti.ValueC1).First()

	Logger.Debug("LINQ returns: " & valueC)

	'Search using For-Each statement
	For Each boltInfo As BoltInfo In boltInfos
		If BoltInfo.BoltSize = boltSize Then
			For Each thicknessInfo As ThicknessInfo In BoltInfo.ThicknessInfos
				If ThicknessInfo.ValueT = valueT
					Logger.Debug("For-Each returns: " & ThicknessInfo.ValueC1)
					Return
				End If
			Next
		End If
	Next
	
End Sub

Private Function GetBoltInfos() As List(Of BoltInfo)

	Dim boltInfos As New List(Of BoltInfo)

	Dim boltInfo As BoltInfo
	'First bolt info 1/4
	boltInfo = New BoltInfo(1 / 4, 5 / 16, 3 / 8)
	boltInfo.ThicknessInfos.Add(New ThicknessInfo(3 / 16, 1 + 1 / 8, 1 / 2))
	boltInfo.ThicknessInfos.Add(New ThicknessInfo(1 / 4, 1 + 5 / 16, 1 / 2))
	'...
	boltInfo.ThicknessInfos.Add(New ThicknessInfo(9 / 16, 2 + 1 / 8, 1 / 2))
	'... and so on

	boltInfos.Add(boltInfo)

	'Second bolt info 3/8
	boltInfo = New BoltInfo(3 / 8, 7 / 16, 1 / 2)
	boltInfo.ThicknessInfos.Add(New ThicknessInfo(3 / 16, 1 + 1 / 8, 1 / 2))
	boltInfo.ThicknessInfos.Add(New ThicknessInfo(1 / 4, 1 + 5 / 16, 1 / 2))
	'...
	boltInfo.ThicknessInfos.Add(New ThicknessInfo(9 / 16, 2 + 1 / 8, 1 / 2))
	'... and so on

	boltInfos.Add(boltInfo)
	Return boltInfos

	'Next bolt infos...
End Function


Class BoltInfo
	Sub New(boltSize As Double, minHoleDia As Double, maxHoleDia As Double)
		Me.BoltSize = boltSize
		Me.MinHoleDia = minHoleDia
		Me.MaxHoleDia = maxHoleDia
		Me.ThicknessInfos = New List(Of ThicknessInfo)()
	End Sub

	Public Property BoltSize() As Double
	Public Property MinHoleDia() As Double
	Public Property MaxHoleDia() As Double

	Public Property ThicknessInfos() As List(Of ThicknessInfo)
End Class

Class ThicknessInfo
	Sub New(valueT As Double, valueC1 As Double, valueC2 As Double)
		Me.ValueT = valueT
		Me.ValueC1 = valueC1
		Me.ValueC2 = valueC2
	End Sub

	Public Property ValueT() As Double
	Public Property ValueR() As Double
	Public Property ValueC1() As Double
	Public Property ValueC2() As Double

End Class

[llorden4]

Thanks for input and I do get your points on maintenance; I believe you recognize my attempts here to make the code digestible for the next team.  Thanks for taking the time for putting that sample code together, I can use this to learn a few new tricks!