Proposal Summary
This proposal outlines the development of a streamlined process of managing financial data in NetSuite through a user-friendly interface. The focus is on handling CSV file imports for Custom Record GRW017 LE GL Account Plan Line and GRW017 LE FY TrialBal Version. The solution includes creating new custom records, linking them appropriately, mapping GL Account Plan Lines, and providing an overview with user interaction.
Requirement
Growire Group has a specific requirement to include a user friendly import-interface that does the following:
FIRST PHASE
First Task:
import csv-file
Check whether all lines listed in csv exists as Custom record GRW0107 LE GL Account Plan Line. If not, create a new Custom Record GRW017 LE GL Account Plan Version (eg version 2)
Create the new custom records GRW017 LE GL Account Plan Line (with externalID) as listed in the csv file. Copy the already existing GRW017 LE GL Account Plan Line linked to version 1 and link them to version 2
Check whether all GRW017 LE GL Account Plan Line included in the latest version are mapped using the custom record GRW017 GL Account Plan Line Mapping, If not show a user-friendly interface to map the remaining GRW017 LE GL Account Plan Line
Give an overview of the GRW017 LE GL Account Plan Lines that are included in the template, and asks for the newly created whether they should be included in the template. Show a user interface to include them in the template
Second Task:
Give the possibility to choose the template, create the GRW017 LE FY TrialBal Version and import a csv file to create the GRW017 LE FY TrialBal AcctLine Version records including the debit/credit amounts
Third Task:
We need a nice looking user interface that lists all GRW017 LE GL Account Plan Lines that are linked to a specific LE GL Account Plan Version and give the possibility to create a print from the LE GL Account Plan Version as well as the latest template.
SECOND PHASE:
Instead of importing a csv file in the first task first bullet point, import a xbrl file such as included in attach.
The service from which the xbrl files will be downloaded: The new application Consult (CBSO)
The technical guide for the xbrl files: taxonomy_technical_guide_22.9.1.pdf (nbb.be)
Prerequisites:
- CSV file adheres to the expected format, including the presence of Acc Number, Acc Description, and Acc Name fields. Any deviation from the expected structure may result in data import errors.
- Configured with the necessary custom records, for Legal Entity, Account Plan and Financial Year. Any misconfiguration may lead to errors during the import process.
Deliverables
The proposed user interface features of header fields and sublist elements to optimize the CSV import process in custom HTML page.
Header Fields:
CSV File Selection:
Users from Growire Groups can effortlessly choose or browse files from their system using the provided input field for CSV file selection.
Create CSV File and Attach:
An additional option within the user interface allows users to create a new CSV file directly from the custom page. While the form structure may differ from an Excel sheet, users can input details in a manner similar to an Excel file. After saving the form, the system fetches the details and generates a new CSV file. This file is then attached to the file cabinet and seamlessly chosen for upload through the CSV file input field.
Financial Year Dropdown:
The Financial Year dropdown presents users with a list of GRW017 financial years, streamlining the selection process for this critical parameter.
Legal Entity Dropdown:
This dropdown showcases a list of GRW017 Legal Entity File List, providing users with a convenient selection of legal entities associated with the GRW017 custom record.
Additionally, if Growire Groups need to add new entries for Financial Year or Legal Entity, convenient options have been integrated next to each respective field.
Financial Year Addition:
Next to the Financial Year dropdown, there is an option provided for adding new entries. Clicking on this option allows users to input details for a new financial year directly. After submission, the new financial year is dynamically incorporated into the dropdown for future selections.
Legal Entity Addition:
Similarly, adjacent to the Legal Entity dropdown, there is a dedicated option for adding new entries. By selecting this option, users can input information for a new legal entity. Upon submission, the newly added legal entity becomes part of the dropdown list.
Sublist Fields:
The sublist within the custom HTML page will feature the following fields. By default, the page initializes without displaying any values in the sublist. However, once the appropriate Legal Entity and Financial Year are selected, the LE GL Account Plan custom record entries relevant to the chosen parameters will be dynamically listed in the sublist. It’s important to note that this sublist is non-editable, maintaining data integrity.
The displayed fields include:
Account Number: Displays the account number associated with the LE GL Account Plan custom record entry.
Account Name: Shows the account name corresponding to the LE GL Account Plan custom record entry.
Account Description: Provides a description of the account, offering additional context for the custom record entry.
Is Template Default (Checkbox): Features a checkbox indicating whether the custom record entry is marked as the template default.
The sublist will exclusively display values where the “Is Template” checkbox is checked in the LE GL Account Plan custom record.
Buttons:
The page will also include the following buttons:
- Import:
- This button initiates the processing of the imported file, executing the necessary actions for data integration.
- Close:
- An option to Close the current session.
- Print:
Clicking the Print button enables users to generate a physical or digital copy of the details listed inside the sublist, offering a convenient option for documentation and record-keeping.
During the import process, various scenarios will be encountered, each with its corresponding set of actions. The functioning of the import process can be outlined as follows:
Scenario One (CSV Import for the first time):
In the case of a fresh CSV import, where the lines from the CSV file are not associated with any existing versions for the given legal entity, the system meticulously processes the uploaded file.
The solution adeptly identifies the absence of imported entries in any versions by examining the Acc Number, Acc Description, and Acc Name fields within the CSV.
Subsequently, a new version is dynamically created, specifically tailored to the chosen Legal Entity and Financial Year.
Following this, GRW017 LE GL Account Plan Line custom record entries are generated.
Upon the successful creation of both the new version and the associated custom record entries, a clear success message is promptly displayed to the user. Subsequently, an alert box prompt encourages the user to seamlessly proceed to the next phase—the creation of a Trail Balance custom record.
Records corresponding to the selected financial year will be generated as follows:
Upon the completion of the Trail Balance creation, the page undergoes an automatic reset, presenting a clean slate for subsequent actions. Additionally, users have the convenience of reviewing the newly created custom record entries by selecting the desired Version and Legal Entity from the custom import page.
Scenario Two (CSV Import with Mixed Entries):
In situations involving a CSV import containing both new and existing entries, the system adopts a distinct approach for each type of entry. For entirely new lines imported from the file, a new version is generated specifically for the chosen Legal Entity and Financial Year.
Concurrently, for lines in the CSV that correspond to existing GL Acc Plan Line entries, a seamless process is implemented to intelligently copy them to the new version. This strategic step ensures the preservation of continuity and consistency in data representation.
Following these considerations, the remainder of the process aligns with the procedures outlined in Scenario One, seamlessly advancing to the creation of the Trail Balance.
Print Functionality:
The Print functionality on the custom import page is designed to provide users with a convenient method to generate a hard copy of the GRW017 LE GL Acc Plan Line entries. This printing process is contingent on the values chosen for Legal Entity, Financial Year, and Version.
The PDF template for the printout will adhere to a standardized format. The customized template will systematically incorporate the entries from the specified custom record. Notably, the printout will be formatted to fit the A4 size, ensuring compatibility and readability.
File Storage:
Following each import process, the utilized CSV file will be systematically stored within the file cabinet, housed in a dedicated folder. This approach ensures that in subsequent instances, the files required for the process can be easily retrieved from this designated folder, streamlining accessibility and maintaining an organized file storage system.
Assumptions:
- The assumption is that the CSV files utilized in the import process will consistently adhere to the expected structure with the necessary fields such as Acc Number, Acc Description, and Acc Name.
- It is assumed that the print functionality will produce outputs in A4 size, aligning with standard printing conventions and ensuring a consistent format for the printed documents.
- As per the proposal, the system is currently configured to exclusively process CSV file types. This assumption implies that other file formats are not supported in the current system setup. (Will be considering with Phase 2)
- The print template will be designed and created in a normalized manner, given the absence of a provided mock-up.
- In the scenario where the CSV file is generated directly from the custom page rather than being uploaded, it is assumed that the user interface for adding rows will not closely resemble the structure commonly found in Excel. The user interface is anticipated to be designed in a manner that may differ in layout and functionality from typical Excel structures. Notably, this approach implies limitations, such as the inability to provide formulas as available in Excel sheets.)
- Growire Groups have the full authority to choose or to create financial years and legal entities as needed. Any values added to the custom record directly save into Netsuite. Clicking “Add” will always open the link to create these custom records, ensuring a straightforward and user-controlled process.
- After copying the GRW017 LE GL Account Plan custom record entries onto the new versions, it is assumed that the existing older versions of custom records entries will not be deleted. The retention of older versions is anticipated to be maintained for historical data purposes.
- Whenever the user confirms or declines the alert box prompt for creating the Trail Balance custom records, it is assumed that the initial HTML page will be reloaded, and all values will be reset or cleared. This mechanism aims to provide users with a clean and reset interface.
Notes:
- Ensure that the relationships between custom records, especially between GRW017 LE GL Account Plan Line and GRW017 LE FY TrialBal Version, are correctly established. Any discrepancies in these relationships may lead to data inconsistencies.
- Establish a versioning strategy for custom records to manage and track changes effectively. This ensures that each import creates a new version when necessary, preventing data overlap and preserving historical data.
Risks
- Large CSV files or complex import processes may have performance implications. Monitor system performance during and after the import process to identify any degradation and optimize the solution accordingly.
- The risk of data integrity issues arises if there are discrepancies between the CSV file and the existing custom records.
- The expected number of lines within each CSV file for the creation of custom records is approximately 150. In the event that the number of lines exceeds this threshold, there is a potential risk. If such instances occur, it is crucial to notify us so that we can promptly schedule and manage the import process effectively. Failure to address this risk may lead to performance issues, extended processing times, or potential system overload, emphasizing the importance of proactively monitoring and coordinating large-scale imports.
- On the CSV file lines, if any account description or account name contains comma-separated values, there is a chance of misunderstanding the values during the import process. The presence of commas within these fields may lead to data parsing errors, potentially causing inaccuracies or incomplete representations of the account descriptions or names in the custom records.
- When picking the Legal Entity and Financial Year, there’s a chance of accidentally selecting entries with the same name. Whether it’s done on this custom page or directly through the UI, there’s a risk of choosing identical values. This could lead to confusion in the following steps of the process, potentially impacting the accuracy of the import.
- This proposal exclusively addresses the scope of Phase 1. It does not encompass Phase Two considerations, such as the incorporation of different file types.