Contributor II

3 years ago

TableView - Setting data type to TableView columns

Hey all,

I'm struggling to figure out how to define a TableViewColumn to be of type Decimal instead of the default type being Text.

I'm in the section where I create a TableViewRowHeader and adding TableViewColumns to it.

I can create a new column:

Dim myTableViewColumn As New TableViewColumn

myTableViewColumn.Name = "Col1"

But when I try to set the type to this column, I get an error " Input string was not in a correct format." when trying to retrieve the table:

myTableViewColumn.DataType = XFDataType.Decimal

Is this syntax correct? I'm assuming that it's in the column definition where I should be defining the datatype but now not so sure. Hopefully someone can point me in the right direction.

As a side note, page 15 in the Table Views User Guide makes reference to a function called CreateTableViewColumn. Is this a custom function or part of the OS library. The compiler doesn't recognize this function.

Spreadsheet

JackLacava

3 years ago

I figured it out.

The problem happens if you try to add visible headers. The minute you call tableView.Rows.Add, the system will ignore the isHeader property and just assume that any cell in the row must conform to the datatype you've declared for the column (you cannot override datatype per row, it's either the entire column or nothing; that also means you can save yourself the trouble to assign it to every cell).

So really there are only two options at this point: strings everywhere, or no headers in your TableView.

Here's a working implementation that renounces headers:

	Dim dt As New DataTable("FakeData")
	dt.Columns.Add("SomeDescription", GetType(String))
	dt.Columns.Add("DecimalNumber", GetType(Decimal))
	dt.Rows.Add("Row one", 10.5)
	dt.Rows.Add("Row Two", 11.0)

	Dim tv As TableView = New TableView()
	tv.CanModifyData = False
	
	' column definitions
	Dim tvHeader As New TableViewRow()
	For Each dtCol As DataColumn In dt.Columns
		Dim tvCol1 As New TableViewColumn()
		tvCol1.Name = dtCol.ColumnName
		tvCol1.Value = dtCol.ColumnName
		If tvCol1.Name.XFEqualsIgnoreCase("DecimalNumber") Then
			tvCol1.DataType = XFDataType.Decimal
		End If
		tvCol1.IsHeader = True
		tv.Columns.Add(tvCol1)
		' uncomment this to break
		' tvHeader.Items.Add(tvCol1.Name, tvCol1)
	Next
	' uncomment this to break
	' tv.Rows.Add(tvHeader)
		
	' data
	For Each row In dt.Rows
		Dim tvDataRow1 As New TableViewRow()
		For Each tvCol As TableViewColumn In tv.Columns
			Dim col As New TableViewColumn()
			col.Name = tvCol.Name
			col.Value = row.Item(tvCol.Name)
			col.IsHeader = False
			tvDataRow1.Items.Add(col.Name, col)
		Next
		tv.Rows.Add(tvDataRow1)
	Next
	Return tv

I would probably consider this a bug, or at the very least something that should be documented. Feel free to submit it and claim the glory 😉

JackLacava

Honored Contributor

3 years ago

#worksforme

I'm on v7.0 and the following code works as expected:

Dim tv As TableView = New TableView()
tv.CanModifyData = False
Dim tvHeader As New TableViewRow()
Dim tvCol1 As New TableViewColumn()
tvCol1.Name = "Test"
tvCol1.Value = "Test"
tvCol1.DataType = XFDataType.Decimal
tvCol1.IsHeader = True
tv.Columns.Add(tvCol1)
tvHeader.Items.Add(tvCol1.Name, tvCol1)

Dim tvDataRow1 As New TableViewRow()
For Each tvCol As TableViewColumn In tv.Columns
	Dim col As New TableViewColumn()
	col.Name = tvCol.Name
	col.Value = Decimal.Parse("10.5")
	col.DataType = XFDataType.Decimal
	col.IsHeader = False
	tvDataRow1.Items.Add(col.Name, col)
Next
tv.Rows.Add(tvDataRow1)
Return tv

I suspect the trick is to mark THE HEADER COLUMN, as well as the data column, with the correct datatype.

Edit: for the record, CreateTableViewColumn is not in the library, it's a simple utility function that most Spreadsheet example rules define at the bottom. I will paste it here, but I would recommend you build your own version if you need to manipulate the datatype, because (as you can see) this doesn't do it.

Private Function CreateTableViewColumn(ByVal columnName As String, ByVal columnValue As String, ByVal isHeader As Boolean) As TableViewColumn
	Dim tableViewColumn As New TableViewColumn()	
	tableViewColumn.Name = columnName
	tableViewColumn.Value = columnValue
	tableViewColumn.IsHeader = isHeader
	Return tableViewColumn
End Function

db_pdx

Valued Contributor

3 years ago

Here's a slightly more realistic example that does not work. It reproduces the error Cosimo mentions. Can you see anything wrong with this?

	'A more realistic way of bringing in data
	Dim dt As New DataTable("FakeData")
	dt.Columns.Add("SomeDescription", GetType(String))
	dt.Columns.Add("DecimalNumber", GetType(Decimal))
	dt.Rows.Add("Row one", 10.5)
	
	'Create the TableView Object
		Dim tableView As New TableView()
		'Create the columns in the Table View using the Data Table columns
		'Header row
		Dim tableViewRowHeader As New TableViewRow()
		For Each dc As DataColumn In dt.Columns
			Dim tc As New TableViewColumn()
			tc.Name = dc.ColumnName
			tc.Value = dc.ColumnName
			If tc.Name = "DecimalNumber" Then
				tc.DataType = XFDataType.Decimal
			End If
			tc.IsHeader = True
			tableView.Columns.Add(tc)
			tableViewRowHeader.Items.Add(tc.Name, tc)
		Next dc
		tableView.Rows.Add(tableViewRowHeader)

		For Each dr As DataRow In dt.Rows
		Dim tableViewRow As New TableViewRow()
			For Each tableViewColumn As TableViewColumn In tableView.Columns
				If tableViewColumn.Name = "DecimalNumber" Then
					Dim tcValue As Decimal
					Dim tc As New TableViewColumn()
					tc.Name = tableViewColumn.Name
					tc.DataType = XFDataType.Decimal
					tcValue = dr.Item(tableViewColumn.Name)
					tc.Value = tcValue
					tc.IsHeader = False
					tableViewRow.Items.Add(tableViewColumn.Name, tc)
				Else
					Dim tcValue As String = ""
					Dim tc As New TableViewColumn()
					tc.Name = tableViewColumn.Name
					tcValue = dr.Item(tableViewColumn.Name)
					tc.Value = tcValue
					tc.IsHeader = False
					tableViewRow.Items.Add(tableViewColumn.Name, tc)
				End If
			Next tableViewColumn
			tableView.Rows.Add(tableViewRow)
		Next dr
		Return tableView

JackLacava

Honored Contributor

3 years ago

I figured it out.

So really there are only two options at this point: strings everywhere, or no headers in your TableView.

Here's a working implementation that renounces headers:

	Dim dt As New DataTable("FakeData")
	dt.Columns.Add("SomeDescription", GetType(String))
	dt.Columns.Add("DecimalNumber", GetType(Decimal))
	dt.Rows.Add("Row one", 10.5)
	dt.Rows.Add("Row Two", 11.0)

	Dim tv As TableView = New TableView()
	tv.CanModifyData = False
	
	' column definitions
	Dim tvHeader As New TableViewRow()
	For Each dtCol As DataColumn In dt.Columns
		Dim tvCol1 As New TableViewColumn()
		tvCol1.Name = dtCol.ColumnName
		tvCol1.Value = dtCol.ColumnName
		If tvCol1.Name.XFEqualsIgnoreCase("DecimalNumber") Then
			tvCol1.DataType = XFDataType.Decimal
		End If
		tvCol1.IsHeader = True
		tv.Columns.Add(tvCol1)
		' uncomment this to break
		' tvHeader.Items.Add(tvCol1.Name, tvCol1)
	Next
	' uncomment this to break
	' tv.Rows.Add(tvHeader)
		
	' data
	For Each row In dt.Rows
		Dim tvDataRow1 As New TableViewRow()
		For Each tvCol As TableViewColumn In tv.Columns
			Dim col As New TableViewColumn()
			col.Name = tvCol.Name
			col.Value = row.Item(tvCol.Name)
			col.IsHeader = False
			tvDataRow1.Items.Add(col.Name, col)
		Next
		tv.Rows.Add(tvDataRow1)
	Next
	Return tv

I would probably consider this a bug, or at the very least something that should be documented. Feel free to submit it and claim the glory 😉

Cosimo
Contributor II
3 years ago
Thanks Jack. I'll run some tests to confirm that both the column in the TableView and the TableViewRowHeader needs to be assigned the datatype separately. Still a weird object model but when it works.. it works.
Also thanks for clarifying the function CreateTableViewColumn. It's not documented in the TableView user guide so someone will need to update that.
- JackLacava
  Honored Contributor
  3 years ago
  See the reply above: https://community.onestreamsoftware.com/t5/Rules/TableView-Setting-data-type-to-TableView-columns/m-p/12891/highlight/true#M929
  
  About CreateTableViewColumn, btw, I noticed that they added it to the TableView object as "CreateColumn". So this works:
  
  Dim tvCol1 As TableViewColumn = myTableView.CreateColumn(dtCol.ColumnName, dtCol.ColumnName, True)

db_pdx
Valued Contributor
3 years ago
Hey Cosimo: "But when I try to set the type to this column, I get an error " Input string was not in a correct format." when trying to retrieve the table:" I had the exact same issue. I bashed my head against the screen for a few hours one day and then just gave up altogether trying to manually define the columns. I believe you are doing it correctly and your syntax mirrors mine (tc.DataType = XFDataType.Double) but obviously either we are doing something incorrectly or there is a bug somewhere.
Our purpose for the Table View is reporting only so my current solution uses the shortcut route of populating the table view directly from the datatable. Depending on your use case this might be a solution until we can get better explanation from someone.
---
Dim tableView As New TableView()
tableView.PopulateFromDataTable(dt, False, True)
Return tableView

Forum Discussion

TableView - Setting data type to TableView columns

Related Content

Prompting for parameters for Tableview

Keeping spreadsheet formatting with a TableView

Reading the BiBlend view in a Spreadsheet (TableView) business rule

Drop Down in TableView not working.

Quick view setting