09-12-2022 09:53 AM - last edited on 05-02-2023 10:45 AM by JackLacava
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
Solved! Go to Solution.
09-12-2022 01:49 PM
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
09-12-2022 12:42 PM
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
09-12-2022 01:14 PM
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
09-12-2022 01:49 PM
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
09-12-2022 04:29 PM
That worked! Thanks so much for your help, Sudarshan!