Can you add records to a table via an Excel Table View?

[molly_lvc]

Hi everyone,

I am trying to add records to a table via an Excel Table View. Using the example in the Table Views documentation, I am able to update existing records; however, any new records added are cleared once I submit the sheet. Is there additional code I can add to allow the Table View to write new records to the table?

Thanks!

Molly

[sudarshan]

You can add new rows back to your table via a table view by using the same principle of looping over the table view object but to differentiate the new rows you use the isDirty() flag in the spreadsheet rule and then using an insert SQL statement to write back.

Here's a community example for table view rules here

Thanks,

Sudarshan

---

---

[molly_lvc]

Hi Sudarshan,

Thanks for your response! 

That makes sense, that's definitely the extra step that I am missing. It seems like I don't have access to view the example. Is there another way you could share?

Thanks so much!

Molly

[sudarshan · Accepted solution]

Here's the code referenced in the above link.

Public Class MainClass
	Private ReadOnly tableName As String = "XFC_CustomTableView_Example1"
	Private ReadOnly PKColumnName As String = "PK"
	Private ReadOnly readOnlyColumns As New list(Of String)({"PK","Scenario","Year"})
	
	Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As SpreadsheetArgs) As Object
		Try
			Select Case args.FunctionType
				
				Case Is = SpreadsheetFunctionType.Unknown
					
				Case Is = SpreadsheetFunctionType.GetCustomSubstVarsInUse
					
					If args.TableViewName.XFEqualsIgnoreCase("PE_Webinar_Sample")
						
						#Region "Defining Parameters"
						'You will be prompted for the value of these variables if they have not been resolved.
						'Dim list As New List(Of String)
						'list.Add("Parameter1NameHere")
						'list.Add("Parameter2NameHere")
						'Return list
						#End Region
						
					End If
					
							
				Case Is = SpreadsheetFunctionType.GetTableView	' -- Refresh button	
					
					If args.TableViewName.XFEqualsIgnoreCase("PE_Webinar_Sample")
						
						#Region "Retrieving Parameters"
						'In case parameters are used, this is how they can be retrieved.
						'Note: These must be defined under SpreadsheetFunctionType.GetCustomSubstVarsInUse
						'Dim Parameter1Value As String = args.CustSubstVarsAlreadyResolved("Parameter1NameHere")
						'Dim Parameter2Value As String = args.CustSubstVarsAlreadyResolved("Parameter2NameHere")
						#End Region
						
						#Region "Select SQL"
						Dim sql As New Text.StringBuilder
						sql.AppendLine($"SELECT * from {tableName}")
						#End Region
						
						#Region "Data Table"
						'Create and populate the Table View from a Data Table
						
						' -- Option using API function GetCustomDataTable to get the Data Table
						'Dim scenarioMember As Member = BRApi.Finance.Members.GetMember(si, dimtypeid.Scenario, si.WorkflowClusterPk.ScenarioKey)
						'Dim timeMember As Member = BRApi.Finance.Members.GetMember(si, dimtypeid.Time, si.WorkflowClusterPk.TimeKey)
						'Dim dbWwfScenario As New DbWhere("WfScenarioName", dboperator.IsEqualTo, scenarioMember.Name)
						'Dim dbWwfTime As New DbWhere("WfTimeName", dboperator.IsEqualTo, timeMember.Name)
						'Dim dbwlist As New List(Of dbwhere) From {dbWwfScenario,dbWwfTime}					
						'Dim dbOwfScenario As New DbOrderBy("WfTimeName",True)
						'Dim odblist As New List(Of DbOrderBy) From {dbOwfScenario}
						'Dim dt As DataTable = BRApi.Database.GetCustomDataTable(si, "Application", tableName, dbwlist, odblist)
						
						' -- Option using SQL syntax and ExecuteSqlUsingReader to get the Data Table
						Dim dt As DataTable = Nothing
						Using dbConnApp As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
							dt = BRApi.Database.ExecuteSqlUsingReader(dbConnApp, sql.ToString, False)
							If dt Is Nothing Then Return Nothing
						End Using	
						#End Region
						
						#Region "Create Table View"
						'Dim tv As New TableView
						' 30/05/2022 - Open enhancement on PopulateFromDataTable - does not work well with cultures settings where the decimal separator is different to '.'
						'tv.PopulateFromDataTable(dt, True, True)
						
						' Data must be saved on the tables and presented to excel using the Invariant culture (i.e.: decimal separator '.').
						Dim tv As TableView = Me.CustomPopulateFromDataTable(si,dt)
						#End Region
						
						#Region "Format Table View"
						'Mark Table View as editable (if it is going to be used for data input/updates)
						tv.CanModifyData = True
						
						'Formatting Header
						tv.NumberOfEmptyRowsToAdd = 10
						tv.EmptyRowsBackgroundColor = XFColors.LightYellow
						tv.HeaderFormat.BackgroundColor = XFColors.Aqua
						tv.HeaderFormat.IsBold = True
						tv.HeaderFormat.IsItalic = True
												
						'Formatting Columns
						For i As Integer = 0 To tv.Columns.Count -1
							Dim tvc As TableViewColumn = tv.Columns.Item(i)
							tvc.ColumnFormat.IsBold = False
							If readOnlyColumns.Contains(tvc.Name) Then
								'Read Only Columns
								tvc.ColumnFormat.BackgroundColor = XFColors.LightGray
							Else 'Regular Columns
								tvc.ColumnFormat.BackgroundColor = XFColors.AntiqueWhite
							End If
							If tvc.DataType = XFDataType.Decimal Then tvc.ColumnFormat.NumDecimals = 2
						Next
						#End Region

						Return tv
					
					End If
					
											
				Case Is = SpreadsheetFunctionType.SaveTableView  ' -- Submit button	(followed by GetTableView)
					
					If args.TableViewName.XFEqualsIgnoreCase("PE_Webinar_Sample")
						Me.SaveTableView(si, tableName, args.TableView, PKColumnName)
					End If
					
			End Select

			Return Nothing
		Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
		End Try
	End Function
	
	#Region "CustomPopulateFromDataTable"
	'Function To populate a tableview From a DataTable - supports multiple User Cultures
	Public Function CustomPopulateFromDataTable(ByVal si As SessionInfo, ByVal dt As DataTable) As TableView
		Try

			'Create the table view object
			Dim tv As New TableView()
			
			'Create the header row object
			Dim tvhr As New TableViewRow()
			tvhr.IsHeader = True
			
			'Create the columns
			For Each dc As DataColumn In dt.Columns
				'Create the column and set them as headers
				Dim tvc = Me.CreateTableViewColumn(si, dc.ColumnName, dc, True, dc.ColumnName)
				tv.Columns.Add(tvc)
				'Populate the header row
				tvhr.items.Add(tvc.Name, tvc)
			Next
			
			'Add the header row to the table view
			tv.Rows.Add(tvhr)
			
			'Add additional rows
			For Each dr As DataRow In dt.Rows
				Dim tvr As New TableViewRow()
				tvr.IsHeader = False
				For Each dc As DataColumn In dt.Columns
					Dim value As String = dr.Item(dc.ColumnName).ToString
					tvr.Items.Add(dc.ColumnName, Me.CreateTableViewColumn(si, dc.ColumnName, dc, False, value))
				Next
				tv.Rows.Add(tvr)
			Next
			
			Return tv
			
		Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
		End Try				
	End Function

	'Aux function to create a new column for table views
	Private Function CreateTableViewColumn(ByVal si As SessionInfo, ByVal columnName As String, ByVal dc As DataColumn, ByVal isHeader As Boolean, ByVal value As String) As TableViewColumn
		Dim tvc As New TableViewColumn()
		tvc.Name = columnName
		
		tvc.IsHeader = isHeader
		tvc.Value = value

		If dc.DataType = GetType(String)
			tvc.DataType = XFDataType.Text
		Else If dc.DataType = GetType(Decimal)
			If Not isHeader And Not String.IsNullOrEmpty(value.ToString) Then
				'Converts decimal numbers back to standard / invariant culture (i.e: using '.')
				'so it works regardless Of User Culture
				tvc.Value = Decimal.Parse(value).XFToStringForFormula
			End If
			tvc.DataType = XFDataType.Decimal				
		Else If dc.DataType = GetType(Integer)
			tvc.DataType = XFDataType.Int16
		Else If dc.DataType = GetType(Boolean)
			tvc.DataType = XFDataType.Boolean
		Else If dc.DataType = GetType(Date) Or dc.DataType = GetType(DateTime)
			tvc.DataType = XFDataType.DateTime
		End If
		
		Return tvc					
	
	End Function 
	#End Region	
	
	#Region "SaveTableView"		
	Private Sub SaveTableView(ByVal si As SessionInfo, ByVal tableName As String, ByRef tableView As tableview, ByVal pkColumnName As String)
		Try		
			Dim invariantCulture As CultureInfo = System.Globalization.CultureInfo.InvariantCulture
			'Create the list for update and insert statements
			Dim updateSqlList As New list(Of String)
			Dim insertSqlList As New List(Of String)
			Dim deleteSqlList As New List(Of String)
			
			#Region "Auto Populate Columns - Optional"
			' Auto Populate Scenario and Year columns with selected WF
			Dim WFScenName As String = BRApi.Finance.Members.GetMemberName(si,dimTypeId.Scenario,si.WorkflowClusterPk.ScenarioKey)
			Dim WfYear As String = BRApi.Finance.Members.GetMemberName(si, dimTypeId.Time, si.WorkflowClusterPk.TimeKey)
			#End Region
			
			'Loop on all the cells
			For Each tvr As TableViewRow In tableView.Rows
				If tvr.IsHeader Then Continue For
				'Retrieve the pk cell
				Dim pkCell As TableViewColumn = tvr.Item(pkColumnName)
			
				'Updated existing row
				'Checks if the row already existed - i.e.: the original value of the pkCell is not empty
				If pkCell.OriginalValue <> String.Empty And pkCell.OriginalValue <> Guid.Empty.ToString Then
					
					Dim updateColsList As New List(Of String)
					
					'Checks if the new value of the PK is empty and, if it is, delete the record					
					If pkCell.Value = String.Empty Then 'Deleted row
					#Region "Delete Row"
						deleteSqlList.Add($"DELETE FROM {tableName} WHERE {pkColumnName} = '{pkCell.OriginalValue}'")						
					#End Region
					
					Else	
					#Region "Update Row"							
						'Loop on all the cells of the row
						For Each tvc As TableViewColumn In tableView.Columns
							'Retrieve current cell
							Dim tvcName As String = tvc.Name
							Dim tvCell As TableViewColumn = tvr.Item(tvcName)

							'Check if the cell is dirty or not
							If tvCell.IsDirty Then									
								Dim tvCellValue As String = tvCell.Value
								
								' -- SAMPLE CODE ---
								' -- Updates Scenario and Year based on selected WF
								' -- This section of the code should be updated to meet your needs									
								If tvcname.Equals("Scenario")
									updateColsList.Add($"{tvcName} = '{WFScenName}'")
								Else If tvcname.Equals("Year")
									updateColsList.Add($"{tvcName} = '{WfYear}'")
									
								' Updates the value depending on the data type of the cell
								Else If tvc.DataType.Equals(XFDataType.Boolean) Then
									If String.IsNullOrEmpty(tvCellValue) OrElse tvCellValue = False OrElse readOnlyColumns.Contains(tvcname) Then
										updateColsList.Add($"{tvcName} = 0")
									Else
										updateColsList.Add($"{tvcName} = 1")
									End If
								Else If tvc.DataType.Equals(XFDataType.Text) OrElse tvc.DataType.Equals(XFDataType.DateTime) Then
									If String.IsNullOrEmpty(tvCellValue) OrElse readOnlyColumns.Contains(tvcname)
										updateColsList.Add($"{tvcName} = ''")
									Else											
										updateColsList.Add($"{tvcName} = '{tvCellValue}'")
									End If
								Else If String.IsNullOrEmpty(tvCellValue) OrElse readOnlyColumns.Contains(tvcname)
									updateColsList.Add($"{tvcName} = NULL")
								Else ' case of decimal type. NOTE: In case you are using data types not specified here you might have to review the if conditions
									Dim decValue As Decimal = Decimal.Zero
									' Parses the decimal so it saves the number using invariant culture (i.e.: '.')
									If Not Decimal.TryParse(tvCellValue,decValue) Then decValue = Decimal.Parse(tvCellValue,invariantCulture)
									' Ignores the updates where the original and updated number match after parsing.
									If String.IsNullOrEmpty(tvCell.OriginalValue) OrElse Decimal.Parse(tvCell.OriginalValue,invariantCulture) <> decValue Then
										updateColsList.Add($"{tvcName} = {decValue.XFToStringForFormula}")
									End If
								End If
								
							End If

						Next

						'Add the row to the list of rows to be updated
						If updateColsList.Count > 0 Then
							updateSqlList.Add($"UPDATE {tableName} SET {String.Join(", ", updateColsList)} WHERE {pkColumnName} = '{pkCell.OriginalValue}'")
						End If
					#End Region
					
					End If					
				
				#Region "Insert Row"
				'New Row
				' -- Check to identify if it is a new row (if PK original value was empty it is a new row)
				Else

					'Create the insert columns list
					Dim insertColsList As New List(Of String)

					'Create the is dirtyFlag
					Dim isNew As Boolean = False

					'Loop on all the cells of the row
					For Each tvc As TableViewColumn In tableView.Columns
						'Retrieve current cell
						Dim tvcName As String = tvc.Name
					
						'Pk Cell
						If tvcname = pkColumnName Then
							insertColsList.Add($"'{Guid.NewGuid}'")
						Else
							'Retrieve current cell
							Dim tvCell As TableViewColumn = tvr.Item(tvcname)

							'Check if the cell is dirty or not
							If tvCell.IsDirty = True Then isNew = True

							'Get the current cell value
							Dim tvCellValue As String = tvCell.Value
							
							' -- SAMPLE CODE ---
							' -- Updates Scenario and Year based on selected WF
							' -- This should be updated to meet your needs		
							
							If tvcname.Equals("Scenario")
								insertColsList.Add($"'{WFScenName}'")
							Else If tvcname.Equals("Year")
								insertColsList.Add($"'{WfYear}'")
								
							'Modify the value depending on the data type of the cell
							Else If tvc.DataType.Equals(XFDataType.Boolean) Then
								If String.IsNullOrEmpty(tvCellValue) OrElse tvCellValue = False OrElse readOnlyColumns.Contains(tvcname) Then
									insertColsList.Add(0)
								Else
									insertColsList.Add(1)
								End If
							Else If tvc.DataType.Equals(XFDataType.Text) Or tvc.DataType.Equals(XFDataType.DateTime)
								If String.IsNullOrEmpty(tvCellValue) OrElse readOnlyColumns.Contains(tvcname)
									insertColsList.Add("''")
								Else
									insertColsList.Add($"'{tvCellValue}'")
								End If
							Else If String.IsNullOrEmpty(tvCellValue) OrElse readOnlyColumns.Contains(tvcname)
								insertColsList.Add("NULL")
							Else ' case of decimal type. NOTE: In case you are using data types not specified here you might have to review the if conditions
								Dim decValue As Decimal = Decimal.Zero
								' Parses the decimal so it saves the number using invariant culture (i.e.: '.')
								If Not Decimal.TryParse(tvCellValue,decValue) Then decValue = Decimal.Parse(tvCellValue,invariantCulture)
								insertColsList.Add(decValue.XFToStringForFormula)
								
							End If

						End If

					Next

					If isNew Then 'Only generates the insert statement if the cell is new
						insertSqlList.Add($"INSERT INTO {tableName} VALUES ({String.Join(",", insertColsList)})")

					End If

				End If
				#End Region					
			Next tvr
			
			#Region "Run SQL update, insert and delete statements"
			'brapi.ErrorLog.LogMessage(si,"updateSqlList: " & vbCrLf & String.Join(vbCrLf, updateSqlList.ToArray))
			'brapi.ErrorLog.LogMessage(si,"insertSqlList: " & vbCrLf & String.Join(vbCrLf, insertSqlList.ToArray))
			'brapi.ErrorLog.LogMessage(si,"deleteSqlList: " & vbCrLf & String.Join(vbCrLf, deleteSqlList.ToArray))
			
			Using dbconn As DbConnInfo = BRApi.Database.CreateApplicationDbConnInfo(si)
				updateSqlList.ForEach(Function(lambda) brapi.Database.ExecuteActionQuery(dbconn, lambda, False, True))
				insertSqlList.ForEach(Function(lambda) brapi.Database.ExecuteActionQuery(dbconn, lambda, False, True))
				deleteSqlList.ForEach(Function(lambda) brapi.Database.ExecuteActionQuery(dbconn, lambda, False, True))					
			End Using
			#End Region

		Catch ex As Exception
			Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
		End Try
	End Sub		
	#End Region
	
End Class

Thanks,

Sudarshan

[molly_lvc]

That worked! Thanks so much for your help, Sudarshan!