10-20-2022
08:21 AM
- last edited
Thursday
by
JackLacava
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.
Solved! Go to Solution.
10-31-2022 12:12 PM
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 😉
10-20-2022 12:26 PM
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
10-21-2022 05:44 AM - edited 10-21-2022 09:40 AM
#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
10-21-2022 01:02 PM - edited 10-21-2022 01:10 PM
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
10-24-2022 09:07 AM
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.
10-31-2022 12:12 PM
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 😉
11-01-2022 02:06 PM
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.
11-02-2022 08:49 AM
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.
10-24-2022 09:03 AM
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.
10-31-2022 12:20 PM
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)