Forum Discussion

DcDev's avatar
DcDev
New Contributor II
6 months ago

SQL Table Editor Unable to Edit Any Values

I am trying to create a small POC for using a SQL Table Editor tool.  However, I am unable to edit a table row on the SQL Table Editor even after it is in a Dashboard with all of the settings to do so. 

What is the typical reason for this?  I have Default For Allow Column Updates = True and my OneStream Database Server Maintenance Group for Ancillary Tables is set to Administrators (I am an Admin).  I tried setting that to Everyone and that didn't work either.  I am confused on way the basic functionality of this to to edit SQL data is not working.  Any help appreciated!  

Thank you!

 

Dashboards
  • Henning's avatar
    Henning
    6 months ago

    Hi, you are trying to edit an application database table. I strongly believe that this is not possible using SQL Table Editor. Allowing this would circumvent all auditing within the platform.

    You need to create your own custom table, save your FX data in there and then use a business rule to use native Apis to save the FX rates in FXRateType table.

  • sameburn's avatar
    sameburn
    Contributor II
    6 months ago

    I agree with Henning .  You should not be trying to make changes to Application / System tables with a SQL Table Editor (this is why you cannot edit when viewing an application table).  This will not be audited and is not recommended.  This component is to be used with custom tables (e.g. ones created by you) not ones installed as part of the OneStream client installation e.g. FXRateTypes  

  • Henning's avatar
    Henning
    Valued Contributor II
    6 months ago

    Hi, you are trying to edit an application database table. I strongly believe that this is not possible using SQL Table Editor. Allowing this would circumvent all auditing within the platform.

    You need to create your own custom table, save your FX data in there and then use a business rule to use native Apis to save the FX rates in FXRateType table.

  • DcDev's avatar
    DcDev
    New Contributor II
    6 months ago

    Thank you both!  This is great information to know about the SQL Table Editor tool and makes sense to not modify those directly for security/auditability purposes.  My FXRateTypes was just a POC example table I was using from an Application database, I also tried a Framework database table with no luck.  Again makes sense it is advised to not do this.

    However, I have written backend code for another project where I update a Framework database schedule task table directly (in a business rule) with the correct start and end times (using another source of data outside of OneStream) and I didn't have any issues, so didn't expect a difference here.  I understand that for the most part we don't want to modify those tables, but in the case of that use case, it made sense, just updating task start & end times, not audit tables.    

    The purpose of learning this was for another project where I plan on creating custom tables.  I am assuming you it is best practice to use the Table Data Manager tool to do this, but also, you could write SQL Sps to create new tables as well?  Does anyone have any good example code of this in OneStream?

    Thank you! 

  • NoorMohd's avatar
    NoorMohd
    New Contributor
    4 months ago

    Hi ,

     

    Just wanted to share to the peace of code where you can create a Custom Data Table or SQL table in the desired database location.

    Please ignore the comments from the above code.