Announcement
ABC
Recent Activity
Need help on Clear Specific member data
Hi Team, I need your support regarding an issue I am facing with the dashboard-driven data clearing logic. Requirement: When specific Dimension/Members are selected in the dashboard, the attached code is expected to execute in Preview and Clear modes to remove the corresponding data. Issue Observed: The code runs successfully during execution. I can see the log message indicating that the data is “cleared.” However, the actual data is not getting cleared from the system. This suggests that while the process is triggering correctly, the clearing operation is not impacting the underlying data as expected. Could you please review and provide your suggestions on what might be causing this issue or any potential gaps in the approach? Let me know if you need additional details or logs from my end. Thanks in advance for your help. api.Data.SetDataCell(memberScript, amount, isNoData, isDurableCalculatedData) Imports System Imports System.Collections.Generic Imports System.Data Imports System.Data.Common Imports System.Globalization Imports System.IO Imports System.Linq Imports Microsoft.VisualBasic Imports OneStream.Finance.Database Imports OneStream.Finance.Engine Imports OneStream.Shared.Common Imports OneStream.Shared.Database Imports OneStream.Shared.Engine Imports OneStream.Shared.Wcf Imports OneStream.Stage.Database Imports OneStream.Stage.Engine Namespace OneStream.BusinessRule.Finance.EPM_Clear_Specific_Scenarios Public Class MainClass Public Function Main(si As SessionInfo, globals As BRGlobals, api As FinanceRulesApi, args As FinanceRulesArgs) As Object Try '======================================== ' VALID FUNCTION TYPE '======================================== If api.FunctionType <> FinanceFunctionType.CustomCalculate Then Return Nothing End If '======================================== ' PARAMETERS '======================================== Dim entityName As String = GetParam(args, "Entity") Dim scenarioName As String = GetParam(args, "ParamSScenario") Dim timeName As String = GetParam(args, "Time") Dim con as String="Local" Dim accountName As String = GetParam(args, "Account") Dim flowName As String = GetParam(args, "Flow") Dim originName As String = GetParam(args, "Origin") Dim icName As String = GetParam(args, "IC") Dim ud1 As String = GetParam(args, "UD1") Dim ud2 As String = GetParam(args, "UD2") Dim ud3 As String = GetParam(args, "UD3") Dim ud4 As String = GetParam(args, "UD4") Dim ud5 As String = GetParam(args, "UD5") Dim ud6 As String = GetParam(args, "UD6") Dim ud7 As String = GetParam(args, "UD7") Dim ud8 As String = GetParam(args, "UD8") Dim mode As String = GetParam(args, "Mode", "EXECUTE").ToUpper() Dim confirmDelete As String = GetParam(args, "ConfirmDelete", "NO").ToUpper() api.LogMessage("=== DATABUFFER CLEAR START ===") api.LogMessage("Mode=" & mode & " | ConfirmDelete=" & confirmDelete) '======================================== ' VALIDATION '======================================== If String.IsNullOrWhiteSpace(accountName) Then Throw New Exception("Account parameter is required.") End If '======================================== ' BUILD FILTER '======================================== Dim filter As String = "E#" & entityName & ":S#" & scenarioName & ":T#" & timeName & ":A#" & accountName If Not String.IsNullOrWhiteSpace(flowName) Then filter &= ":F#" & flowName If Not String.IsNullOrWhiteSpace(originName) Then filter &= ":O#" & originName If Not String.IsNullOrWhiteSpace(icName) Then filter &= ":I#" & icName If Not String.IsNullOrWhiteSpace(Con) Then filter &= ":C#" & "Local" If Not String.IsNullOrWhiteSpace(ud1) Then filter &= ":U1#" & ud1 If Not String.IsNullOrWhiteSpace(ud2) Then filter &= ":U2#" & ud2 If Not String.IsNullOrWhiteSpace(ud3) Then filter &= ":U3#" & ud3 If Not String.IsNullOrWhiteSpace(ud4) Then filter &= ":U4#" & ud4 If Not String.IsNullOrWhiteSpace(ud5) Then filter &= ":U5#" & ud5 If Not String.IsNullOrWhiteSpace(ud6) Then filter &= ":U6#" & ud6 If Not String.IsNullOrWhiteSpace(ud7) Then filter &= ":U7#" & ud7 If Not String.IsNullOrWhiteSpace(ud8) Then filter &= ":U8#" & ud8 api.LogMessage("Filter used: " & filter) '======================================== ' GET DATABUFFER '======================================== Dim dataBuffer As DataBuffer = api.Data.GetDataBufferUsingFormula(filter) If dataBuffer Is Nothing _ OrElse dataBuffer.DataBufferCells Is Nothing _ OrElse dataBuffer.DataBufferCells.Count = 0 Then api.LogMessage("No data found for given filter.") Return Nothing End If Dim totalCells As Integer = dataBuffer.DataBufferCells.Count api.LogMessage("Cells found: " & totalCells) '======================================== ' SAFETY LIMIT '======================================== If totalCells > 500000 Then Throw New Exception("Too many records selected (" & totalCells & "). Reduce filter.") End If '======================================== ' PREVIEW MODE '======================================== If mode = "PREVIEW" OrElse confirmDelete <> "YES" Then Dim previewCount As Integer = 0 For Each kvp As KeyValuePair(Of DataBufferCellPk, DataBufferCell) In dataBuffer.DataBufferCells api.LogMessage("PREVIEW: " & kvp.Value.CellAmount) previewCount += 1 Next Dim preview As New StringBuilder() preview.AppendLine("DATA SELECTED FOR CLEAR") preview.AppendLine("====================================") preview.AppendLine("Filter :") preview.AppendLine(filter.ToString()) preview.AppendLine("") preview.AppendLine("Total Cells : " & totalCells.ToString("N0")) 'preview.AppendLine("Total Amount: " & Kvp.value.cellamount.ToString("F2")) api.LogMessage("Total PREVIEW cells: " & previewCount) BRApi.Dashboards.Parameters.SetLiteralParameterValue( si, True, "ETPreviewData", preview.ToString()) Return Nothing End If '======================================== ' CLEAR MODE '======================================== Dim clearedCount As Integer = 0 Dim skippedCount As Integer = 0 For Each kvp As KeyValuePair(Of DataBufferCellPk, DataBufferCell) In dataBuffer.DataBufferCells Try Dim cellPk As DataBufferCellPk = kvp.Key Dim memberScriptBuilder As New MemberScriptBuilder() api.Data.ApplyDataBufferCellPkToMemberScriptBuilder(memberScriptBuilder, cellPk) Dim writeScript As String = memberScriptBuilder.GetMemberScript.ToString api.LogMessage("WriteScript" & writescript.ToString()) api.Data.SetDataCell(writeScript, 0D, True,True) clearedCount += 1 Catch exInner As Exception skippedCount += 1 api.LogMessage("SKIPPED: " & kvp.Value.CellAmount.ToString("F2") & " | Error: " & exInner.Message) End Try Next '======================================== ' SUMMARY '======================================== api.LogMessage("=== CLEAR SUMMARY ===") api.LogMessage("Script :" & filter ) api.LogMessage("Total Cells : " & totalCells) api.LogMessage("Cleared : " & clearedCount) api.LogMessage("Skipped : " & skippedCount) api.LogMessage("=== DATABUFFER CLEAR END ===") Return Nothing Catch ex As Exception Throw ErrorHandler.LogWrite(si, ex) End Try End Function '======================================== ' PARAM HELPER '======================================== Private Function GetParam(args As FinanceRulesArgs, name As String, Optional def As String = "") As String Try Dim v As String = args.CustomCalculateArgs.NameValuePairs.XFGetValue(name) If String.IsNullOrWhiteSpace(v) Then Return def Return v.Trim() Catch Return def End Try End Function End Class End Namespace
Modifying SQL Editor component in Dynamic Dashboards not working
Hi there, I'm working on a client using the version 8.5.2 and I'm trying to use the Dynamic Dashboards functionallity to modify a property from a SQL Editor Component. The purpose of this logic is to set a dashboard in "ReadOnly" mode when the parameter is set, and in this case, the read only mode I want to achieve is by setting "ShowDataManipulationButtons = False". The BR logic used is the following: If Component.DashboardComponentType = DashboardComponentType.SqlTableEditor Then Dim sqlDefinition = XFSqlTableEditorDefinition.ReadXmlString(si,Component.XmlData) sqlDefinition.ShowDataManipulationButtons = False Component.XmlData = sqlDefinition.WriteXmlString(si) End If I have checked that after this point the component XmlData has changed, however, despite returning the modified component, the changes as not being displayed in the dashboard. If instead of modifying an SqlEditor component, I change any property on a Button component, the changes are being displayed correctly. Is this a known issue for the version 8.5.2 or is there any specific thing I might be missing for the SqlEditor component? PD: I know I can achieve creating a copy of the component, but it doesn't makes sense to do something twice (or ten times) when there is a way to solve it dynamically once for all. Thank you in advance!Course Announcement: Creating Dashboard Smart Links for the Modern Browser Experience (MBE)
1 MIN READ This course is intended for administrators or consultants who want to distribute dashboards to MBE users by generating Smart Link URLs. It introduces Smart Links, reviews the required code, and explores three use case examples. Delivery Types: On-Demand (OD) Duration: 1 Hour Availability: This Course is Free Course Link: Creating Dashboard Smart Links for the MBEAccounting for Fiscal Year Calendars in LIM other than January to December.
DISCLAIMER: It should be noted that the focus of this technical guide is to provide general information, considerations, and guidelines for an identified topic. It is NOT to be interpreted as the ONLY approach nor a guarantee that there will not be any issues encountered by using this approach as a customer’s requirements or application configuration may render this guidance as not applicable.In addition, statements that “we believe” and similar statements reflect our beliefs and opinions on the relevant subject. These statements are based upon information available to us as of the date of this article, and while we believe such information forms a reasonable basis for such statements, such information may be limited or incomplete, and our statements should not be read to indicate that we have conducted an exhaustive inquiry into, or review of, all potentially available relevant information. OneStream does not warrant as to the accuracy of this guidance, which is provided on an as-is basis. Any forward-looking statements contained herein are based on information available at the time those statements are made and/or good faith beliefs and assumptions as of that time with respect to future events and are subject to risks and uncertainties that could cause actual performance or results to differ materially from those expressed in or suggested by the forward-looking statements. Considering these risks and uncertainties, the forward-looking events and circumstances discussed in this guide may not occur and actual results could differ materially from those anticipated or implied in the forward-looking statements. VERSION: PV900 SV200 PLATFORM: 9.X PURPOSE: to provide an example that you can leverage as part of your LIM build. RESOLUTION: First, create the following set of 4 date fields in your register: StartDate = required field and will be the start date that comes in from your source data and visible to users EndDate = required field and will be the start date that comes in from your source data and visible to users StartDateOffset = Used by LIM as the “Start Date” field for calculations but will be hidden from users and populated through custom date offset code. EndDateOffset = Used by LIM as the “End Date” field for calculations but will be hidden from users and populated through custom date offset code There are 3 places in which register data can be added: WsComponentService = File import or Direct Connect WsDynamicGridService = Add rows directly in dynamic grid WsTableViewService = Adding rows in Spreadsheet So, you will need to add custom code to all 3 places to handle populating the date offset fields. Below walks through the code to be added to all 3 places. In the custom workspace you set up for your custom code (e.g. Workforce Planning Custom 1) go into the custom event handler you created (see other OC post for how to set this up: LIM: Example of Setting up my Customizations Outside of LIM’s Workspace(s) for Future Upgrades | One...), e.g. your Workforce Planning Custom 1 > Code Only (LIM) > CustomEventHandler_LIM > WsComponentService.cs file, add the following code: private static void AfterEvent(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardExtenderArgs args, XFSelectionChangedTaskResult taskResult) { // GET FUNCTION NAME FROM COMPONENT SELECTION CHANGED SERVER TASK ARGUMENTS if (args.FunctionName.XFEqualsIgnoreCase("ImportRegisterData")) { ParseDateFields(si, brGlobals, workspace, args, taskResult); } } private static void ParseDateFields(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardExtenderArgs args, XFSelectionChangedTaskResult taskResult) { var sql = new StringBuilder(); sql.Append(@$" UPDATE [limb1].XFW_LIM_StageRegister SET StartDateOffset = DATEADD(MONTH, -6, StartDate), EndDateOffset = DATEADD(MONTH, -6, EndDate); "); BRApi.Database.ExecuteSql(BRApi.Database.CreateApplicationDbConnInfo(si), sql.ToString(), false); } In your Workforce Planning Custom 1 > Code Only (LIM) > CustomEventHandler_LIM > WsDynamicGridService.cs file, add the following code: private static void BeforeEvent(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardDynamicGridArgs args) { // GET FUNCTION NAME FROM COMPONENT SELECTION CHANGED SERVER TASK ARGUMENTS if (args.Component.Name.XFEqualsIgnoreCase("dg_Register_Insert_Delete_LIM")) { ParseDateFields(si, args); } } private static void ParseDateFields(SessionInfo si, DashboardDynamicGridArgs args) //Had to add in DashboardDynamicGridArgs args { if (args.SaveDataArgs != null) { foreach (XFEditedDataRow row in args.SaveDataArgs.EditedDataRows) { //if (row.InsertUpdateOrDelete == DbInsUpdateDelType.Update) if (row.InsertUpdateOrDelete != DbInsUpdateDelType.Insert && row.InsertUpdateOrDelete != DbInsUpdateDelType.Update) continue; { bool StartDateParsed = DateTime.TryParse( row.ModifiedDataRow.Items.ContainsKey("Start Date") ? row.ModifiedDataRow.Items["Start Date"]?.ToString() : null, out DateTime StartDate); bool EndDateParsed = DateTime.TryParse( row.ModifiedDataRow.Items.ContainsKey("End Date") ? row.ModifiedDataRow.Items["End Date"]?.ToString() : null, out DateTime EndDate); int StartDayInt = 0; int StartMonthInt = 0; int StartYearInt = 0; int EndDayInt = 0; int EndMonthInt = 0; int EndYearInt = 0; row.ModifiedDataRow.Items["Start Date Offset"] = StartDate.AddMonths(-6); row.ModifiedDataRow.Items["End Date Offset"] = EndDate.AddMonths(-6); // row.ModifiedDataRow.Items["End Date Offset"] = EndDate.ToString("MM-dd-yyyy"); //if parsing succeeded, extract month and year directly from DateTime instead of manually trying to convert if (StartDateParsed) { StartDayInt = StartDate.Day; // day StartMonthInt = StartDate.Month; // month StartYearInt = StartDate.Year; // year row.ModifiedDataRow.Items["Start Day"] = StartDayInt; row.ModifiedDataRow.Items["Start Month"] = StartMonthInt; row.ModifiedDataRow.Items["Start Year"] = StartYearInt; } if (EndDateParsed) { EndDayInt = EndDate.Day; // day EndMonthInt = EndDate.Month; // month EndYearInt = EndDate.Year; // year row.ModifiedDataRow.Items["End Day"] = EndDayInt; row.ModifiedDataRow.Items["End Month"] = EndMonthInt; row.ModifiedDataRow.Items["End Year"] = EndYearInt; } } } } } In your Workforce Planning Custom 1 > Code Only (LIM) > CustomEventHandler_LIM > WsTableViewService.cs file, add the following code: private static void BeforeEvent(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, string tableViewName, TableView tableView) { if (tableViewName == "RegisterEditor") { ParseDateFields(si, brGlobals, workspace, tableView); } } private static void ParseDateFields(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, TableView tableView) { foreach (TableViewRow tableViewRow in tableView.Rows) { var RegisterId = tableViewRow.Items["Register ID"]?.OriginalValue?.ToString(); if (tableViewRow.IsHeader) continue; // Skip header row if (string.IsNullOrWhiteSpace(RegisterId)) continue; // Skip blank or whitespace-only rows bool StartDateParsed = DateTime.TryParse( tableViewRow.Items.ContainsKey("Start Date") ? tableViewRow.Items["Start Date"]?.Value?.ToString() : null, out DateTime StartDate); bool EndDateParsed = DateTime.TryParse( tableViewRow.Items.ContainsKey("End Date") ? tableViewRow.Items["End Date"]?.Value?.ToString() : null, out DateTime EndDate); int StartDayInt = 0; int StartMonthInt = 0; int StartYearInt = 0; int EndDayInt = 0; int EndMonthInt = 0; int EndYearInt = 0; bool StartDateUpdate = tableViewRow.Items["Start Date"].IsDirty(); bool EndDateUpdate = tableViewRow.Items["End Date"].IsDirty(); tableViewRow.Items["Start Date Offset"].Value = StartDate.AddMonths(-6).ToString(); tableViewRow.Items["End Date Offset"].Value = EndDate.AddMonths(-6).ToString(); //if parsing succeeded, extract month and year directly from DateTime instead of manually trying to convert if (StartDateUpdate) tableViewRow.Items["Start Date Offset"].Value = StartDate.AddMonths(-6).ToString(); if (StartDateUpdate && StartDateParsed) { StartDayInt = StartDate.Day; // day StartMonthInt = StartDate.Month; // month StartYearInt = StartDate.Year; // year tableViewRow.Items["Start Day"].Value = StartDayInt.ToString(); tableViewRow.Items["Start Month"].Value = StartMonthInt.ToString(); tableViewRow.Items["Start Year"].Value = StartYearInt.ToString(); } if (EndDateUpdate) tableViewRow.Items["End Date Offset"].Value = EndDate.AddMonths(-6).ToString(); if (EndDateUpdate && EndDateParsed) { EndDayInt = EndDate.Day; // day EndMonthInt = EndDate.Month; // month EndYearInt = EndDate.Year; // year tableViewRow.Items["End Day"].Value = EndDayInt.ToString(); tableViewRow.Items["End Month"].Value = EndMonthInt.ToString(); tableViewRow.Items["End Year"].Value = EndYearInt.ToString(); } } } In your Settings > Security > Register Columns page be sure to restrict the date offset column visibility to just administrators. This is because these fields are used to drive calculations but will confuse users if they are visible. Ensure that any formulas that refer to the Start or End Date fields, do so to the StartDateOffset and EndDateOffset fields since those fields are the ones that drive the calculations behind the scenes. By setting StartDateOffset and EndDateOffset in the register as the true start and end date fields, calculations will already take this into account but if you have used any of those register fields further in formulas, you will need to revisit those to ensure they are referring to the correct date field to account for calendar differences. When you import register data, the following should happen by example: Focusing on employee ID #1, we can see the following: Start Date = 7/1/2025 in MM/dd/yyyy format End Date = 6/1/2026 in MM/dd/yyyy format If your company is a July to June Fiscal calendar then those should appear as follows: Start Date = 7/1/2025 = 2025M1 in LIM End Date = 6/1/2026 = 2025M12 in LIM Without the date offset logic, they will appear incorrectly as follows: Start Date = 7/1/2025 = 2025M7 in LIM End Date = 6/1/2026 = 2026M6 in LIM To correct these dates in calculations, the following will correctly happen: Start Date Offset = 1/1/2025 in MM/dd/yyyy format so this will appear as 2025M1 in LIM calculated data End Date Offset = 12/1/2026 in MM/dd/yyyy format so this will appear as 2025M12 in LIM calculated data When mapping data to the cube the data source should appear as follows: This ensures that the data appears in the correct periods within LIM (2026M1 = July 2026….2026M12 = June 2026) and when loaded to the cube.
Decimal column is always dirty
Hi, I need to check in a Table View if certain column values are dirty and I've noticed that when you call the IsDirty() function on any decimal type column (and this only happens on that type), it always returns 'true' even though no changes took place. Because of this i need to manually parse the value and check if the originalValue is different from the current value. At this point I was wondering if this behaviour is intended or not, it seems odd that IsDirty() returns the correct value for every type of value BUT a decimal one. Any clarification on this matter is appreciated, thanks in advance. 🙂
List of User Default Home Pages
Hi Folks, Wondering if anyone has built a BR or query to compile a list of Users and their Default Home Page. I know its in the UserAppSettings.xml File within File Explorer under Internal/User, and you can set individuals through the Admin Solution Tools, however I have not found a good way to See what is already set without going to each user individually (that would not be very practical). Appreciate any help! StephanieIssue in Web Content and File Viewer component to directly open a HTML file in a browser
Dear community, I currently have an html document which is located in the file Explorer under "Documents/Public" folder. I am trying to get this HTML document to be presented into the components, embedded in a dashboard. However, with either a Web Content component or a File Viewer component (which will pinpoint to this specific file in Application Database File, as soon as I open the dashboard, it will load the html page into my default browser... I am using OS v8.1 ... is it a known bug or the expected behaviour ? What is even more strange, directing the Full File Name to a URL will correctly open a document in a dashboard ... Regards,
LIM: Example of Register Field Population from a File Explorer File 1
DISCLAIMER: It should be noted that the focus of this technical guide is to provide general information, considerations, and guidelines for an identified topic. It is NOT to be interpreted as the ONLY approach nor a guarantee that there will not be any issues encountered by using this approach as a customer’s requirements or application configuration may render this guidance as not applicable. In addition, statements that “we believe” and similar statements reflect our beliefs and opinions on the relevant subject. These statements are based upon information available to us as of the date of this article, and while we believe such information forms a reasonable basis for such statements, such information may be limited or incomplete, and our statements should not be read to indicate that we have conducted an exhaustive inquiry into, or review of, all potentially available relevant information. OneStream does not warrant as to the accuracy of this guidance, which is provided on an as-is basis. Any forward-looking statements contained herein are based on information available at the time those statements are made and/or good faith beliefs and assumptions as of that time with respect to future events and are subject to risks and uncertainties that could cause actual performance or results to differ materially from those expressed in or suggested by the forward-looking statements. Considering these risks and uncertainties, the forward-looking events and circumstances discussed in this guide may not occur and actual results could differ materially from those anticipated or implied in the forward-looking statements. VERSION: PV900 SV200 PLATFORM: 9.2.0 USE CASE: your customer wants a standard repository to store supplementary information to be used to populate certain Register Fields. In this example, a list of job codes and its descriptions is handled as a Spreadsheet component within an OneStream dashboard. Every time a user selects a job code or inputs a job code in the Register, the expectation is that the Job Code Description register field is populated with the description that is from this .csv. PURPOSE: to provide you with an example that you can leverage as part of your build, whether you decide to incorporate this to existing input options (Register, Spreadsheet, Import File, Connector). NOTE(S): my example assumes that this file is a .csv and placed in a dedicated folder in File Explorer. ASSUMPTIONS: The relevant Register Fields have already been created. In my example, I have JobCode and JobCodeDescription register fields. The .csv is uploaded to a dedicated folder in File Explorer A simple dashboard that allows the Administrator or select users to update the file. File Source Type for the Spreadsheet component is set up to reference Application Database File. I will be splitting this approach into two articles - 1 which will cover WsComponentService, 2 which will cover WorkforcePlanningImportRegisterConnector, WsDynamicGridService and WsTableViewService. A) WsComponentService: ImportRegisterData (to handle importing files) Example logic to handle populating the JobCodeDescription for WsComponentService: NOTE: I am putting in a sample code equivalent below which pertains to SV200 as the table names have changed from PLN to LIM. private static void populateJobCodeDesc2(SessionInfo si, BRGlobals brGlobals, DashboardWorkspace workspace, DashboardExtenderArgs args, XFSelectionChangedTaskResult taskResult) { int rowNumber = 0; var errorList = new List<string>(); var invalidJobCodes = new List<string>(); try { // Load register rows once DataTable registerRows = GetRegisterRows(si); // Load lookup file once into memory (cached dictionary) FileSystemLocation location = FileSystemLocation.ApplicationDatabase; string fileFullName = "Documents/Public/PLP_Lookups/JobCodeLookup.csv"; bool includeContentFileBytes = true; bool failGracefully = true; XFFileEx sourceFile = BRApi.FileSystem.GetFile(si, location, fileFullName, includeContentFileBytes, failGracefully); if (sourceFile == null || sourceFile.XFFile == null || sourceFile.XFFile.ContentFileBytes == null) { throw new XFException(si, new Exception($"Source file '{fileFullName}' could not be loaded or is empty.")); } // Convert byte[] to string using UTF8 string textContent = System.Text.Encoding.UTF8.GetString(sourceFile.XFFile.ContentFileBytes); // Split into lines (keep non-empty lines) string[] lines = textContent.Split(new[] { "\r\n", "\r", "\n" }, StringSplitOptions.RemoveEmptyEntries); char delimiter = DetectDelimiter(lines); Dictionary<string, string=""> jobCodeLookup = BuildJobCodeLookup(lines, delimiter); // Batch SQL updates (avoid ExecuteSql per row) // Chunk updates to prevent excessively large SQL strings const int batchSize = 500; int statementsInBatch = 0; var sqlBatch = new System.Text.StringBuilder(capacity: 64 * 1024); var connInfo = BRApi.Database.CreateApplicationDbConnInfo(si); foreach (DataRow regRow in registerRows.Rows) { rowNumber++; string jcName = regRow["JobCode"]?.ToString()?.Trim() ?? string.Empty; string empId = regRow["EmployeeID"]?.ToString()?.Trim() ?? string.Empty; string jcDescription; if (!string.IsNullOrEmpty(jcName) && jobCodeLookup.TryGetValue(jcName, out jcDescription)) { regRow["JobCodeDescription"] = jcDescription ?? string.Empty; } else { jcDescription = "INVALID JOB CODE"; if (!string.IsNullOrEmpty(jcName)) invalidJobCodes.Add(jcName); errorList.Add($"Row {rowNumber}, EmployeeID {empId}, JobCode '{jcName}': INVALID JOB CODE"); } // Escape for SQL safety string escDesc = SqlStringHelper.EscapeSqlString(jcDescription); string escJc = SqlStringHelper.EscapeSqlString(jcName); string escEmp = SqlStringHelper.EscapeSqlString(empId); // If your staging table has a unique row key (recommended), use that instead. sqlBatch.AppendLine($@" UPDATE [limb2].XFW_LIM_StageRegister SET JobCodeDescription = '{escDesc}' WHERE EmployeeID = '{escEmp}' AND JobCode = '{escJc}';"); statementsInBatch++; // Execute in chunks if (statementsInBatch >= batchSize) { BRApi.Database.ExecuteSql(connInfo, sqlBatch.ToString(), false); sqlBatch.Clear(); statementsInBatch = 0; } } if (statementsInBatch > 0) { BRApi.Database.ExecuteSql(connInfo, sqlBatch.ToString(), false); } // If any invalids, throw a consolidated error (after updates are done) if (errorList.Count > 0) { string errorMessage = "Some JobCodes were marked as INVALID:" + Environment.NewLine + string.Join(Environment.NewLine, errorList); throw new XFException(si, new Exception(errorMessage)); } } catch (XFException) { throw; } catch (Exception ex) { throw new XFException(si, new Exception("Unexpected error occurred: " + ex.Message, ex)); } }</string,></string></string> IMPORTANT NOTE: If you have more customizations, then you will need to assess how many of these customizations rely on fetching the register rows. You want to consider fetching these register rows once and reuse it instead of having each method (which corresponds to a different customization) call it; this is to optimize performance and processing. You may want to also consider the use of helpers. Below are some helpers that were referenced in the code above. GetRegisterRows private static DataTable GetRegisterRows(SessionInfo si) { try { var sql = new System.Text.StringBuilder(); sql.AppendLine($@" SELECT [limb2].XFW_LIM_StageRegister.EmployeeID, [limb2].XFW_LIM_StageRegister.Instance, [limb2].XFW_LIM_StageRegister.LastName, [limb2].XFW_LIM_StageRegister.FirstName, [limb2].XFW_LIM_StageRegister.Status, [limb2].XFW_LIM_StageRegister.HireDate, [limb2].XFW_LIM_StageRegister.HirePeriod, [limb2].XFW_LIM_StageRegister.TermDate, [limb2].XFW_LIM_StageRegister.TermPeriod, [limb2].XFW_LIM_StageRegister.Salary, [limb2].XFW_LIM_StageRegister.SalaryOverride, [limb2].XFW_LIM_StageRegister.Entity, [limb2].XFW_LIM_StageRegister.CostCenter, [limb2].XFW_LIM_StageRegister.CostCenterDescription, [limb2].XFW_LIM_StageRegister.JobCode, [limb2].XFW_LIM_StageRegister.JobCodeDescription, [limb2].XFW_LIM_StageRegister.FTE, [limb2].XFW_LIM_StageRegister.HireYear, [limb2].XFW_LIM_StageRegister.TermYear FROM [limb2].XFW_LIM_StageRegister "); using (DbConnInfo dbConnApp = BRApi.Database.CreateApplicationDbConnInfo(si)) { DataTable dt = BRApi.Database.ExecuteSql(dbConnApp, sql.ToString(), false); return dt; } } catch (Exception ex) { throw ErrorHandler.LogWrite(si, new XFException(si, ex)); } } DetectDelimiter private static char DetectDelimiter(string[] lines) { // Default comma; inspect the first non-empty line foreach (var ln in lines) { var line = ln?.Trim(); if (string.IsNullOrEmpty(line)) continue; // Prefer tab/semicolon/pipe if present if (line.Contains("\t")) return '\t'; if (line.Contains(";")) return ';'; if (line.Contains("|")) return '|'; return ','; // default } return ','; // fallback } BuildJobCodeLookup private static Dictionary<string, string=""> BuildJobCodeLookup(string[] lines, char delimiter) { var dict = new Dictionary<string, string="">(StringComparer.OrdinalIgnoreCase); bool headerSkipped = false; foreach (string raw in lines) { string line = raw?.Trim(); if (string.IsNullOrEmpty(line)) continue; // Parse respecting quotes var fields = ParseDelimitedLine(line, delimiter); if (fields.Count < 2) continue; string code = (fields[0] ?? string.Empty).Trim(); string desc = (fields[1] ?? string.Empty).Trim(); // Skip header row once if it looks like a header // (common: "JobCode,JobCodeDesc" or similar) if (!headerSkipped && LooksLikeHeader(code, desc)) { headerSkipped = true; continue; } if (string.IsNullOrEmpty(code)) continue; // First occurrence wins (avoid overwrite) if (!dict.ContainsKey(code)) dict[code] = desc; } return dict; }</string,></string,> LooksLikeHeader private static bool LooksLikeHeader(string first, string second) { string a = (first ?? string.Empty).Trim(); string b = (second ?? string.Empty).Trim(); // Minimal header detection: common names return a.Equals("JobCode", StringComparison.OrdinalIgnoreCase) || a.Equals("Job Code", StringComparison.OrdinalIgnoreCase) || b.Equals("JobCodeDesc", StringComparison.OrdinalIgnoreCase) || b.Equals("Job Code Desc", StringComparison.OrdinalIgnoreCase) || b.Equals("JobCodeDescription", StringComparison.OrdinalIgnoreCase); } ParseDelimitedLine private static List<string> ParseDelimitedLine(string line, char delimiter) { var result = new List<string>(8); if (line == null) { result.Add(string.Empty); return result; } var sb = new System.Text.StringBuilder(); bool inQuotes = false; for (int i = 0; i < line.Length; i++) { char c = line[i]; if (c == '"') { // If we're in quotes and next char is also quote, it's an escaped quote if (inQuotes && i + 1 < line.Length && line[i + 1] == '"') { sb.Append('"'); i++; // skip next quote } else { inQuotes = !inQuotes; } continue; } if (c == delimiter && !inQuotes) { result.Add(sb.ToString()); sb.Clear(); continue; } sb.Append(c); } result.Add(sb.ToString()); return result; }</string></string> NOTE: if you plan to use StringBuilder() instead of declaring the variable as a string, you will need to import System.Text into the file. StringBuilder() is a method group so it will also have to be referenced as sql.ToString() as part of the BRApi.Database.ExecuteSql line. Example of what I mean by “import” below. File Explorer Part 2
