LIM: Understanding CSV Import Criteria 2
VERSION: PV900 SV200 PLATFORM: 9.X While importing data to the Register via CSV file, you encounter the following error message: “One or more required columns is missing data. Populate the file and reload”. This article covers tips for troubleshooting and resolving this error. When loading data via a CSV file import, you may encounter the following error message: This error message can be a red herring or false error message. It may not be because you are missing any required data, but instead it can be because the register Display Names in your file (when trimmed of spaces) do not match the Register Field Names. For example, consider the following list of Register Fields: In the example register above, all the Display Names circled in black, when trimmed of spaces, match the Register Field Name column. However, for the three highlighted, the Display Names, trimmed of spaces, do not match the Register Field Names. Why does this pose a problem? When you are importing data through a CSV file, the data load is done off a match in the first row of your file to the Register Field Name, not the Display Name. But across all LIM user screens, the Display Name is presented to the user and not the Register Field Name. So, you may assume that the load works off the Display Name that you see as a user. But that is not the case! If your Display Name is used in the first row of your file and, when trimmed, matches your Register Field Name you will be able to successfully load. But that is sheer luck! For any Display Names (trimmed) that do not match the Register Field Name, you will get the false error message "One or more required columns is missing data. Populate the file and reload”. Resolution When loading data to LIM via a CSV file, ensure that the header row in the CSV file is the Register Field Name OR align your Display Names with the Register Field Names, with or without spaces. In the example above, the Register would need to appear as below to work for all these fields: Overall Recommendation The solution you choose to employ is up to you, and your company needs. This is a design consideration when setting up your initial register. The easiest resolution, when setting up your initial register, is to use a list of Register Field Names (without spaces) that match what you wish to use as the Display Name (with spaces). This will avoid future potential problems. If your register is already set up and since you cannot change the Register Field Name, I would then choose to update the Display Name (which can be changed after the fact) to match the Field Name with spaces. 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.
LIM: Example of Month, Day, or Year Logic in the Register, Spreadsheet, File and Connector
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 SV120 PLATFORM: 9.2.0 USE CASE: for whatever reason, you determine that the out-of-box formulas are not sufficient for your calculations and you need to split the date string into the month, day and year components. This assumes that you have created additional Register Fields to capture the relevant components. PURPOSE: to provide you with an example that you can leverage as part of your build, whether you decide to incorporate this to all existing input options (Register, Spreadsheet, Import File, Connector) or some of them. ASSUMPTIONS: the relevant Register Fields have already been created. In my example, I have HirePeriod, HireYear, TermPeriod and TermYear as separate Register Fields. This section covers the following files/business rules with examples of the logic: A) WsComponentService: ImportRegisterData B) WorkforcePlanningImportRegisterConnector# dashboard extender business rule (to handle connector) C) WsDynamicGridService D) WsTableViewService A) WsComponentService: ImportRegisterData (to handle importing files) TIP: the FunctionName will match with what is referenced in the component. To find the FunctionName, put your LIM-related workspace in the Design Mode (Set Selected Dashboard As Default). Then you can click through and find the relevant component. Below, I am looking for the button associated with the Import button (I clicked on Import & Modify > Add Data > Import to get here) Then going to this specific component, the function name is the ImportRegisterData. Example logic to handle populating the periods and years for WsComponentService: 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. B) WorkforcePlanningImportRegisterConnector#*: ImportRegisterData (to handle connector) *Business rule name may be different depending on the LIM selection and number of workspaces created. Before: this is before any customization. This is what your ImportRegisterData should start out as. After: example of how it could be re-arranged to handle customization. IMPORTANT NOTE: the above assumes this is the only customization needed to the Import Connector. 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. Example below of what this ImportRegisterData could look like if you have multiple customizations that rely on fetching register rows. Anyways, back to assuming that populatePeriods is the ONLY customization needed for the connector…The below is an example to handle populating the periods and years for WorkforcePlanningImportRegisterConnector#*: NOTE: similar to what I already mentioned above for WsComponentService, if you plan to use StringBuilder() instead of declaring the variable as a string in your WorkforcePlanningImportRegisterConnector#* rule, you will need to import System.Text into the business rule. 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. C) WsDynamicGridService: Register Below is an example of how I arranged my logic within my BeforeEvent. The below is an example of the logic associated with populatePeriods. D) WsTableViewService: Spreadsheet Below is an example of how I arranged my logic within my BeforeEvent. The below is an example of the logic associated with populatePeriods.Workforce Planning | Invalid Column Name
I'm receiving the following error when trying to add additional Register Definitions. During the build, I've added new Register fields as requirements evolve but now am facing this error when running the Update button. Has anyone run into this issue and resolved it? Error executing Workspace Assembly Service class 'Workspace.PLNB1.CustomEventHandler_PLN.WsAssemblyFactory'.Invalid column name 'PayGroup'. Invalid column name 'Location'. Invalid column name 'EmpStatus'. (SQL Error Number -2146232060, 207, 207, 207). User Interface Stack Trace: at OneStream.Client.Api.DashboardsAjaxServiceReference.DashboardsAjaxServiceClient.StartExecuteSelectionChangedServerTaskCompressed(SessionInfo si, Boolean isSystemLevel, Guid primaryDashboardID, Guid embeddedDashboardID, Guid componentID, PageInstanceInfo pageInstanceInfo, XFSelectionChangedServerTaskInfo serverTaskInfo) at OneStream.Client.SharedUI.DashboardWcfHelper.ExecuteSelectionChangedServerTaskAsync(SessionInfo si, Boolean isSystemLevel, Guid primaryDashboardID, Guid embeddedDashboardID, Guid componentID, PageInstanceInfo pageInstanceInfo, XFSelectionChangedServerTaskInfo serverTaskInfo, OnAfterExecuteSelectionChangedServerTaskDelegate onAfterExecuteSelectionChangedServerTask, Object userState)
Lookup Drivers in LIM is not working
Hi All, I'm working with Line Item Modelling to implement workforce solution and trying Lookup drivers for one of the calculations. Lookup drivers doesn't seem to work at all. Has anyone tried and whether its working? The lookup driver that i defined doesn't fetch & return anything while using in calculation (formula) though it returns data while i preview it under Lookup drivers. Any help on this would be much appreciated. Thanks in advance. Cheers - Ramesh.LIM: Example of Setting up my Customizations Outside of LIM’s Workspace(s) for Future Upgrades
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 SV120 PLATFORM: 9.2.0 WHY DOES THIS MATTER? To handle future upgrades! By having customizations sit outside of LIM-related Workspaces, the risk of rework or override is minimized. I’ll cover the initial customizations setup and the step-by-step examples of how upgrades are handled. There are three main parts CustomEventHandler initial setup: download and upload the CustomEventHandler file, then update the Workspace Assembly Services on the CodeOnly (XXX) and Workspace Create a custom workspace and copy the Assembly from your LIM-related workspace to your custom workspace. Update assembly code accordingly. Create the dependency within the LIM-related Workspace CustomEventHandler Initial Setup Go to Files under your Workforce Planning # workspace (or LIM-related workspace). You should see a File with a name along the lines of CustomServiceFactory_XXX.zip. NOTE: XXX represents the prefix/suffix for your LIM-selected solution (e.g., Workforce, Duration, Term) Download the .zip and then Load this via Application > Load/Extract Update the Maintenance Unit > CodeOnly (XXX) “Workspace Assembly Service” to CustomEventHandler_XXX.WsAssemblyFactory. NOTE: XXX represents the prefix/suffix for your LIM-selected solution. In my example, the file is CustomServiceFactory_PLN so notice that my Workspace Assembly Service is updated accordingly. Before After Update the Workspace’s Workspace Assembly Service as well from XXX.WsAssemblyFactory to CustomEventHandler_XXX.WsAssemblyFactory NOTE: XXX represents the prefix/suffix for your LIM-selected solution. In my example, the file is CustomServiceFactory_PLN so notice that my Workspace Assembly Service is updated accordingly. Before After Upon refresh, you will see CustomEventHandler_XXX assembly component under your CodeOnly (XXX) Maintenance Unit. Custom Workspace Setup Create new workspace (e.g. Workforce Planning Custom) Set Is Shareable Workspace = True Give it a Namespace Prefix. THIS IS IMPORTANT. REMEMBER TO DO SO. After creating the new workspace (e.g. Workforce Planning Custom), within that workspace create a DMU (e.g. custom as shown below) Copy the CustomEventHandler assembly from your LIM-related workspace) Paste it into your newly created Workspace. In my example, I am pasting it to my Workforce Planning Custom’s Custom Maintenance Unit Rename the Assembly. This can be done by clicking on the Assembly Properties tab and then clicking on the Rename button. In my example, because I have copied the CustomEventHandler assembly from Workforce Planning 2 into my Workforce Planning Custom, I am going to use PLNB2 as the suffix; remember PLNB2 is Workforce Planning 2’s Namespace Prefix. Click into the Assembly component under your Custom Maintenance Unit. Click on the WsAssemblyFactory file. Update ALL the lines to reference the source Workspace (in my example, I updated these lines to reference that of Workforce Planning 2 [prefix of PLNB2]). Another example (I am in the WsAssemblyFactory file). The 4 highlighted rows are going to correspond to your specific customizations that you will then write into the corresponding Services files. Click into each of the Services files and update to your source Workforce Planning instance. Create the Dependency in your LIM-Related Workspace Go back to your source Workforce Planning instance’s CustomEventHandler_PLN assembly and Add Dependency. Fill in the details. Example below. Import your separate workspace in the WsAssemblyFactory file (Workspace.NAMESPACEPREFIX.ASSEMBLYNAME) Update your WsAssemblyFactor lines to reference your custom Assembly. Walk-though of an Upgrade after Implementing this Custom Workspace Load the latest LIM version into the application Go to specifically Line Item Modeling Installer workspace > click on Uninstall or upgrade existing instance of Planning > select relevant workspace > Upgrade REFRESH application then you will see your dashboard under the Workspace appear. CustomEventHandler will be removed, so you will need to go to Files, under CodeOnly (PLN) and download the CustomServiceFactory_PLN.zip. Upload this zip to the application. Update Maintenance Unit > CodeOnly (PLN) "Workspace Assembly Service" to CustomEventHandler_PLN.WsAssemblyFactory. Update Workspaces > Workforce Planning 2 "Workspace Assembly Service" to CustomEventHandler_PLN.WsAssemblyFactory. In CustomEventHandler_PLN, add Dependency and reference the Assembly from your dedicated custom Workforce-related workspace. Add in the import to the WsAssemblyFactory file in CustomEventHandler_PLN Update the relevant ServiceType(s) Your custom code is good to go!
