TableView - Setting data type to TableView columns

Cosimo
Contributor II

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.

Cosimo_0-1666268300951.png

 

 

1 ACCEPTED SOLUTION

JackLacava
Community Manager
Community Manager

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 😉

View solution in original post

13 REPLIES 13

db_pdx
Contributor III

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

JackLacava
Community Manager
Community Manager

#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

 

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
Community Manager
Community Manager

I suspect the issue is a weird situation due to how the data in the DataTable is retrieved. I tried with accessing the data via row index and it worked, so maybe something happens in the For Each loop - maybe looping by row index would work better? Sadly I'm going on holiday right now and cannot check, but I'll have a go next week.

JackLacava
Community Manager
Community Manager

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 😉

Thanks for the follow-up Jack, much appreciated!  As a moderator, do you have an inside track for bug reporting and documentation updates?  If so, I'll let you submit it.

JackLacava
Community Manager
Community Manager

Yeah, we're opening talks - this thread highlighted that the User Guide needs quite a bit of work, and the TV features need to improve. Thanks for your time, guys.

Hey Jack

 

Has there been any progress on this?

I'm currently facing the same issue and I'm thinking of  trying to create the illusion of a table header by creating two separate table views, one which is the header table and then placing another one directly below which is the data table with no headers...

I actually found a function that allows you to display table headers and use the correct formatting:

 

'Extract SQL data into Data Table
Dim dt As DataTable = Nothing
Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
	dt = BRApi.Database.ExecuteSql(dbConnApp, "SELECT * FROM MyTable", False)
	If Not dt Is Nothing Then dt.TableName = "NoData"
End Using

'Create the Table View object
Dim tableView As New TableView()
tableView.CanModifyData = True
tableview.PopulateFromDataTable(dt, True, True)

 

The only issue with this is that it doesn't allow to create an alias for the column name

Sorry for the flurry of messages on this one but I actually figured out how this one works! Before adding the the table header row, you need to set the IsHeader attribute to true.

'Set IsHeader attribute on Header Row
tvHeader.IsHeader = True
tv.Rows.Add(tvHeader)

@JackLacava I have updated your code below to fix the issue:

 

	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

	'Set IsHeader attribute on Header Row
	tvHeader.IsHeader = True
	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

 

Ashok
New Contributor III

Thanks Mark.

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
Community Manager
Community Manager

See the reply above: https://community.onestreamsoftware.com/t5/Rules/TableView-Setting-data-type-to-TableView-columns/m-...

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)