Update adjacent column on column edit

(23 posts) (3 voices)
  • Started 3 years ago by nsepetys
  • Latest reply from myDBR Team
  1. nsepetys, Member

    Hello myDBR Representative,

    How is it going? Is there a way to automatically update a cell value when another cell is edited? I see there are some helper javascript functions but I don't think they'll work since I want to query the database for the adjacent cell. Do you have any recommendations on how I might accomplish this with the built in myDBR functionality?

    Thanks,
    Noah

    Posted 3 years ago #
  2. myDBR Team, Key Master

    Noah,
    when you create a callback function for the editing, the callback function will return as a first parameter the value returned from the editing procedure. You are free to use the parameter as you like. If you want to return more than one value from the editing procedure, you can return them as a JSON string.

    For example If your editing procedure returns a JSON string like this:

    select concat('{"value":"B","style":"another:20"}');

    You can define the callback function to parse the string:

    select 'dbr.javascript', 'function mycallback(value)
    {
    var return_object = jQuery.parseJSON(value);
    }
    ';

    Then the return_object is a JavaScript object containing 'value' and 'another' properties. You can then update the another cell based on the value. You also have the 'this' variable pointing to the edited cell at your disposal as well as the helper functions col_value_get, col_value_set etc.

    --
    myDBR Team

    Posted 3 years ago #
  3. nsepetys, Member

    Nice! I will have to try this out. Thank you!

    Posted 3 years ago #
  4. nsepetys, Member

    I believe I am missing a piece of the solution but let me know if I'm wrong. I think I need to somehow get the return from my "editable" stored procedure and pass that into the "mycallback" function (which looks like how you recommended above). I have the following syntax-

    SELECT 'dbr.editable', 'value', 'sp_DBR_update_proc', 'p_data=data', 'p_datatwo=datatwo', 'p_timefilter=timefilter', "options={'onblur':'', 'callback':mycallback}";

    How would I go about passing the sp_DBR_update_proc return into the mycallback function?

    Posted 3 years ago #
  5. myDBR Team, Key Master

    To return a value (or values) from editing procedure, you return the value via select query. A single value returned will automatically replace the user edited value (for example if the value needs to be formatted).

    If you need to return multiple values (for example for change another field), use the JSON string format.

    So in your case you make a JSON string select from the sp_DBR_update_proc:

    select concat('{"value":',v_edited_value,',"style":"another:',v_another_value,'"}');

    See an example in demos.

    We'll take a look if the JSON formatting could be done automatically by myDBR (as not all databases support JSON formatting) in next versions so that if you return multiple columns from editing procedure, the result would automatically be converted to JSON.
    --
    myDBR Team

    Posted 3 years ago #
  6. myDBR Team, Key Master

    Noah,
    we made this a bit easier for you in the latest build. Now if the editing procedure returns a resultset with more than one column, myDBR will automatically do the JSON conversion for you.

    As a summary:

    If the editing report does not return anything, the edited item is left as the user edited it.

    If the editing report returns a resultset with (one row) and one column, the returned value will by default replace the user edited cell. This allows for you for example to let user enter partial data ("paris") and your editing procedure then will replace it with full data ("Paris, France").

    If the editing report returns a resultset with more than one columns, myDBR will automatically convert it to JSON string which you can then parse in the callback function.

    We updated the example to reflect the change.
    --
    myDBR Team

    Posted 3 years ago #
  7. nsepetys, Member

    Thanks for the example. Any chance we can get the "show how the report is done" content for that example? I noticed it the other day but I did not take close enough notes and now I no longer see it.

    Posted 3 years ago #
  8. myDBR Team, Key Master

    Sure,
    source code is now visible.

    --
    myDBR Team

    Posted 3 years ago #
  9. nsepetys, Member

    thank you!

    Posted 3 years ago #
  10. nsepetys, Member

    Is there a way to specify dynamic column numbers within the col_value_set and col_value_text methods of the mycallback function? I am using a crosstab function and I'd like to be able to update columns based on the month/year they fall under. My case is much like the case here in the "Basic Example" except that I have to also update an adjacent column (this.columnnumber + 1 (the column to the right of the updated value)).

    Posted 3 years ago #
  11. myDBR Team, Key Master

    Sure,
    the "this" keyword in the callback function points to the edited cell. You can use normal HTMLTableCellElement interface to query specifics of the cell. this.cellIndex will return the column number for the edited cell (starting from zero).

    See updated example in the demos.

    --
    myDBR Team

    Posted 3 years ago #
  12. nsepetys, Member

    Thanks! The cellIndex property was exactly what I was looking for. My editable crosstab is now functioning as intended.

    Posted 3 years ago #
  13. ziuras, Member

    Hi
    I have problem with callback. i have 3 editable columns MinK, UzsK, noEditColumn, Is1
    and after each edit i update all 3 columns.
    Update with callback are working, but after editing one row for few times the editable cells of this row are locked and i can not edit it.

    https://photos.app.goo.gl/3dIUIgZksPxkuVDU2

    callback functions:
    function MinCallBack(value, settings)
    {
    var o = jQuery.parseJSON(value);
    col_value_set( this, this.cellIndex+1, 0, o.MinK );
    col_value_set( this, this.cellIndex+2, 0, o.UzsK );
    col_value_text( this, this.cellIndex+4, o.Is1 );
    } ";
    select 'dbr.javascript', "
    function UzsCallBack(value, settings)
    {
    var o = jQuery.parseJSON(value);
    col_value_set( this, this.cellIndex, 0, o.MinK );
    col_value_set( this, this.cellIndex+1, 0, o.UzsK );
    col_value_text( this, this.cellIndex+3, o.Is1 );
    } ";
    select 'dbr.javascript', "
    function Is1CallBack(value, settings)
    {
    var o = jQuery.parseJSON(value);
    col_value_set( this, this.cellIndex-2, 0, o.MinK );
    col_value_set( this, this.cellIndex-1, 0, o.UzsK );
    col_value_text( this, this.cellIndex+1, o.Is1 );
    } ";

    Posted 1 year ago #
  14. ziuras, Member

    locked cells i marked with red in screenshot
    and numbers in locked cells a aligned to top.

    Edited:
    If after input value i press enter or click mouse on not editable cells then all ok
    but if after input value i click on another editable cell in the some row - second cell are locked.

    Posted 1 year ago #
  15. myDBR Team, Key Master

    What happens is a race condition. When you are eding a cell and click another editing cell, the asynchronous editing starts working on saving the edit for the first cell and while that is ongoing, the second cell gets prepared for editing. Once the first cell editing in the background has completed, your JavaScript orverrides the editing in progress in the second cell.

    If you update to the latest build, we added support for this use case. col_value_text will now detect that it may be changing an cell in editing mode and acts accordingly.

    --
    myDBR Team

    Posted 1 year ago #
  16. ziuras, Member

    Hi, thanks for fix.
    I dont now java.
    Can you show an example of using col_value_text with my callback example?

    select 'dbr.javascript', "
    function Is1CallBack(value, settings)
    {
    var o = jQuery.parseJSON(value);
    col_value_set( this, this.cellIndex-2, 0, o.MinK );
    col_value_set( this, this.cellIndex-1, 0, o.UzsK );
    col_value_text( this, this.cellIndex+1, o.Is1 );
    } ";

    Posted 1 year ago #
  17. myDBR Team, Key Master

    There was same race condition issue with col_value_text. It has now beed fixes and if you update to latest version, your code should work ok.

    --
    myDBR Team

    Posted 1 year ago #
  18. ziuras, Member

    Hi,
    another one problem, editing 1 and 2 column (MinK and UzsK) is ok, but after that the 4 column(Pad_Tiekejas from picture or o.Is1 from callback) is locked.
    1 and 2 column are not locked in any way.

    https://photos.app.goo.gl/Op8bpw3b2ei85Grq1

    Posted 1 year ago #
  19. myDBR Team, Key Master

    Did you run the updater which deals with the racing condition with simultaneous edits and col_value_text? If so, please open a suppot ticket with full source code of your report.

    --
    myDBR Team

    Posted 1 year ago #
  20. ziuras, Member

    Yes, i updated yesterday. Now version is: myDBR 5.0.6 (build 3545)

    Edited:
    When Column4 get focus after column2 or column1 was edited - column4 is locks.
    (column1 - MinK, column2 - UzsK, column4 - o.Is1 or Pad_tiekimas)

    Posted 1 year ago #
  21. myDBR Team, Key Master

    Just run the updater again. The racing condition with col_value_text was dealt with in myDBR 5.0.6 Build 3548.

    --
    myDBR Team

    Posted 1 year ago #
  22. ziuras, Member

    Hi,
    updated to 3548.

    There are not locks now.
    I can edit columns. but no changes go to tables - in tables thee are old values.
    and callback functions sets old values to all 3 columns.

    Posted 1 year ago #
  23. myDBR Team, Key Master

    Please open a support ticket with source code of your main report and your editing reports. Will be easier to help as we can see how your code looks like.

    --
    myDBR Team

    Posted 1 year ago #

Reply

You must log in to post.