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.
