How to set the value at the cell without change the source value at the cell?

Tags: #<Tag:0x00007efc60b019a0>
#1

We are writing bunch of user defined functions. Unlike formula definition we will be using unique character to identify the presence.
in cell C4 user can call function with text ‘~function(para1, param2)’
In renderer we evaluate the function and replace the value in the respective cell.
What we would like here is unlike formula source value and actual at the cell are different. Source value of the formula cell would hold the actual formula and value of the data would be the evaluated value of the formula.
So that when user selects the cell to edit value we can present actual source to the user , just formula cell. When you select a cell with the formula value disappears and original formula is displayed.
So how can set the value at the cell without change the source value at the cell?

#2

Hi @kunal.wit

you can use a custom renderer to show different value in the table and another one in the edit mode. Here’s an example http://jsfiddle.net/jt89695a/

Let me know if you’d need anything else.

#3

Hi @aleksandra_budnik,

Please checkout updated example http://jsfiddle.net/kunal_madhukar/jt89695a/3/
At the time of rendering 0th row, we are changing the inner html. This is similar to how formulas behave. But when we ref the cell with formula it picks the value and not the “formula”.
When cell we special string patterns are matched we want to

  1. Change the inner html, which works perfectly
    2. Assign evaluated value to the cell data.
    This is where I need your help, without changing the original source. Like formula cells.
#4

Hi @kunal.wit,
I’m not sure, what exactly do you want to do, but I hope this example could be helpful: http://jsfiddle.net/jt89695a/6/
And there I have two additional questions for you:

  1. Do you want to use formulas ?
  2. Could you describe steb-by-step what do you want to achieve? Below I’ve added three layers of data storing in Handsontable:
Source Data - your original data 
-------------
|   | A | B |
-------------
| 1 |   |   |
-------------
| 2 |   |   |
-------------

Data - values used in editors
-------------
|   | A | B |
-------------
| 1 |   |   |
-------------
| 2 |   |   |
-------------

Rendered table - that's exactly what is rendered for user
-------------
|   | A | B |
-------------
| 1 |   |   |
-------------
| 2 |   |   |
-------------

It will be very helpful, if you can fill above tables with expected values.

#5

Hi @swistach,

Thanks for the detailed questionnaire . Please find the response below

  1. Do you want to use formulas ?
    I would like to implement formulas which are not supported. The one I am trying is lookup across different sheets.

  2. Could you describe steb-by-step what do you want to achieve? Below I’ve added three layers of data storing in Handsontable:
    I wants to replicate the behaviour similar to formulas .
    I am using custom html renderer for the same. When I encounter string pattern matching to my formula string, I am evaluating it and replacing innerHTML for the cell . But I am not able to use this value in other cells with simple arithmetic operation, nor evaluated values are available when I am using export plugin. The root cause of all these os being not able to update data at a cell. innerHTML just updates the view and not data.

Please find the value for cell A1 in the respective data sets

Source Data - your original data

-------------
|   | A | B |
-------------
| 1 | ~LOOKUP(salaryTable:B10)  | |
-------------
| 2 |   |   |
-------------

Data - values used in editors

-------------
|   | A | B |
-------------
| 1 | 10000  |   |
-------------
| 2 |   |   |
-------------

Rendered table - that’s exactly what is rendered for user

-------------
|   | A | B |
-------------
| 1 | 10000  |   |
-------------
| 2 |   |   |
-------------

Hope I was clear enough this time.

#6

@swistach could you please look into this on priority. We are pro account holder. @kunal.wit is part of our team.
This has been blocker issue for the release.

#7

Hi @amol,
All right, now I understand.
Unfortunately, a formulas plugin is still in a alpha version, that means, it could work improperly (in fact, it’s not recommended to use for an critical functionalities). Additionally, LOOKUP is not supported yet.
But…
You can modify ‘data’ layer on-the-fly, via the modifyData hook (https://docs.handsontable.com/pro/1.14.3/Hooks.html#event:modifyData). This is the same place, where our formular-parser calculate formulas.
It don’t modify sourceData layer, so it’s exactly what you looks for.

TL;DR
I’ve prepared a example: http://jsfiddle.net/q1qee5x6/

I hope that will solve your issue.

Copy and paste displayed value of a custom rendered cell
#8

Hi @swistach,

Thanks for your response and the demo code. Unlike demo values we get for the expression are fetched from server using an AJAX. When we update valueHolder.value on response received in AJAX, its value is not getting updated.
I have just updated the demo with the change showcasing how value will be set.
http://jsfiddle.net/kunal_madhukar/q1qee5x6/2/

#9

Well, maybe something like this: http://jsfiddle.net/qjj42b0b/ ?

#10

@swistach thanks a lot. Above solution worked for us.

#11