Skip to main content

OneStream

OneStream – Table Views Part 2 of 2

Laptop Computer Works

In Part 1 of 2 Table Views, I demonstrated the data querying functionality of Table Views and stated that in Part 2 of 2 I would demonstrate how to update the data that was queried which is the subject of this blog.  If you have not read Part 1 please do so before proceeding as this blog extends the previous blog.

To begin the process, I will log into the OneStream reference application, GolfStream.  Once you have logged into the application, select “OnePlace“-> “Application“.

Blog 2021 10 26 20 05 00 Clipboard

Expand “Tools” and then select “Business Rules“.

Blog 2021 11 17 18 48 57 Onestream Xf

Expand “Spreadsheet” and then select the “UTM_TaskList“.

Blog 2021 12 15 19 38 03 Window

With the UTM_TaskList Business Rule “BR” selected, navigate to “Line 92” and update TV_TaskListExport.CanModifyData from “False” to “True” which enables the modifying of data.  Also, update the comment on line 91 to document the change.

Blog 2021 12 15 19 41 56 Clipboard

Blog 2021 12 15 19 43 12 Clipboard

Navigate to “Line 34” and uncomment the line as a private function will be added to the SaveTableView SpreadsheetFunctionType.

Blog 2021 12 15 19 44 33 Window

Blog 2021 12 15 19 46 24 Window

Onestream - Modern Accounting: How to Overcome Financial Close Challenges
Modern Accounting: How to Overcome Financial Close Challenges

Improvements in each of the following period-close core tasks can provide transformative change and are reviewed in this guide include closing the books and external reporting, periodic reconciliations, and managing the period-close process.

Get the Guide

Having enabled the modification of data and uncommented “Case Is = SpreadsheetFunctionType.SaveTableView“, the next step will be to add line 35 “SaveMyTableView(si,args.TableView)“.  SaveMyTableView is a Private Function which will have the code to update table, XFW_UTM_TaskList, for changed records.

Blog 2021 12 15 19 47 46 Clipboard

Navigate to the End Function line of Private Function, GetUTMTaskListExport, and then create the Private Function “SaveMyTableView“.

Blog 2021 12 16 20 21 53 Onestream Xf

With the function added, the code to update the appropriate data record(s) is developed.   As the code in the next image is documented, I will summarize the code development which is to create the SQL statement “Line 144 then Line 173” that will be populated and executed, the TaskID variable which is utilized in the SQL statement “Line 147“, connection to the application database “Line 153“, a loop of the rows “Line 155” and columns “Line 159” in the result set and finally execution of the SQL statement “Line 183“.  If you have not already done so, save and then compile the BR to check the syntax.  At this point, the BR is ready to test and can be closed or remain open.

Blog 2021 12 16 20 18 42 Onestream Xf

To begin the process of testing the SaveTableView functionality, expand “Tools” and then select “Spreadsheet” accessible from Application and open “TaskImportTemplate_UTMT.xlsx” saved in the previous blog post.  Once the file is opened, select “Refresh Sheet“.

Blog 2021 11 17 18 48 57 Onestream Xf Copy

Blog 2021 12 17 08 42 23 Onestream Xf

Update one or more row(s) in the TaskName column as the TaskName column is the column specified in the UPDATE SQL statement.  The rows which are updated have been shaded to identify the rows which were changed.  Note, additional columns can be updated which would require changes to the UPDATE SQL statement in the Business Rule, and one column was chosen to have a concise example for this blog.

Blog 2021 12 17 08 41 35 Onestream Xf

With the data updated, select “Submit Sheet” from the OneStream XF Ribbon.

Blog 2021 12 17 08 42 33 Clipboard

After a second or two, the change records have been saved to the table and the TableView previously returned has been updated.  Note, the changed records have been shaded for identification.

Blog 2021 12 17 08 51 45 Onestream Xf

One of the lines I did not point out in the code summary was “BRApi.ErrorLog.LogMessage(si,SQL_TaskList_U.ToString)” which logged the query execution to the Application Error Log an image of which is included for reference.  Note this is an optional line in the code that can be commented out if preferred.

Blog 2021 12 17 10 14 46 Onestream Xf

At this point you may be wondering, the Excel file had more than four rows; however, the updates captured in the error log are only for four rows.  How did the Submit Worksheet only know to update four rows which had changed data?  The other line I did not include in the summary of the code is “If TV_ColumnU.IsDirty()” which per the Table Views User Guide, “IsDirty – Condition Check if the item has been modified” e.g. by including IsDirty() the SQL Statement will only execute for changed records.

Blog 2021 12 17 10 25 53 Onestream Xf

As always, thank you for reading the blog post.  In the event you have any questions, feel free to either post a comment or email me at terry.ledet@perficient.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Terry Ledet, Senior Solutions Architect

Terry is a Senior Solutions Architect at Perficient and a OneStream Certified Professional (OCP) - Lead Architect R1. He is a frequent speaker at Splash and passionate about making clients successful. When not blogging about OneStream, Terry is a husband, parent, and baseball enthusiast.

More from this Author

Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram