Formulas are incorrectly calculated / auto updated on sort

[f1ames]

Description

When sorting, cells with formulas containing other cell references (e.g. A1, B2, etc) are updated so the references seems to be matching sorted order (e.g. A1 becomes A3). However, it seems that:

• Sometimes references are not correctly updated (e.g. E3 instead of E4).
• Formulas result is not correctly calculated (seems like it may be using physical indexes over visual or it just uses some outdated values/refs 🤔).

Steps to reproduce

1. Go to https://jsfiddle.net/f1ames/0xbqrkt3/3/.
2. Type =E1*2 in the first row, Units column.
   • The result is 20.
3. Sort by Currency column.
   • The initial first row is now second.
   • The formula is =E2*2.
   • The result is 100 (looks like still referring to 1st visual row).
4. Sort again.
   • The initial first row is now fourth.
   • The formula is =E4*2.
   • The result is 60 (looks like it's referring to 2nd visual row).
5. Sort once again - initial order is restored.
   • The formula is =E1*2.
   • The result is 80 (looks like it's referring to 4th visual/physical row).
6. Sort by Code column.
   • The initial first row is now third.
   • The formula is =E6*2 (strange as there are only 5 rows).
   • The result is 60 (looks like it's referring to 2nd visual row).
7. Sort by Code column once again.
   • Formula changes into #REF!

I see the result is different when after each sort step formula is opened (double-click to preview) or not. The above is scenario with opening editor to preview formula. Without opening it looks like the formula always refers to first visual row (even though the cell reference is updated).

Demo

https://jsfiddle.net/f1ames/0xbqrkt3/3/

Your environment

• Handsontable version: Latest (7.1.1)
• Browser Name and version: Chrome 77
• Operating System: macOS Mojave

[AMBudnik]

I'm on Windows 10, but also working with Chrome 77 and the demo works for me a bit different.

Results

1. • initialization
2. 20
3. 100
4. 80
5. 20
6. 100
7. 40

Related issues

#4668

ps. I've tested the latest build from @wszymanski from Monday (14/10/19) and it works the same.

[f1ames]

Hello @AMBudnik!
Thanks for a quick response. From what I see there are two cases, one which you mentioned in #6336 (comment) related to adding formula and then only resorting - I get the same result as you (in environment mentioned in initial bug report).

The second case is previewing a formula after each sorting step (e.g. by double-clicking cell) and then it works as mentioned in the initial report:

So it seems opening editor on cell with formula (without changing anything), changes the way calculations are done upon sorting 🤔

[AMBudnik]

The issue is still replicable using v 8.0.0 https://jsfiddle.net/1v9cgzbe/

[AMBudnik]

This issue is closed temporarily. It will be reopened for development as it became a part of New formula plugin task reported at #6466

I will make sure to inform everyone interested in this topic after the official fix.

Please feel welcome to makes any comments on this issue. And if you experience similar behavior feel free to contact me at support@handsontable.com

[AMBudnik]

Hi @f1ames this issue is no longer replicable using the latest Hadsontable v9 version.
Here https://jsfiddle.net/68xoumej/ is an updated demo

Reproduction

For everyone interested, here is a list of changes for the following version https://handsontable.com/docs/9.0.0/tutorial-release-notes.html and here https://handsontable.com/docs/9.0.0/tutorial-migration-guide.html is a migration guide (from v8 to v9)

[MaximilianFranz]

Just noting that while #9646 is fixed, the sorting issue is not fixed.

I.e. using the same example as in #9646 when we enable columnSorting the formulas break.

[adrianszymanski89]

Hi @MaximilianFranz

Thank you for pointing that out. Indeed, after performing the sorting operation, calculations break. We have this already reported internally, so we will update you once it's fixed.

[cricketthomas]

Any update on this? Struggling to figure out a work around

[MaximilianFranz]

We have not found a suitable workaround yet. Still trying to use sum formulas of a row in the last column and allow sorting. But we have not spend further effort here yet.

[cricketthomas]

Can this issue be reopened or should we create a new one?

[AMBudnik]

Hi @cricketthomas as mentioned above

This issue is closed temporarily. It will be reopened for development as it became a part of New formula plugin task reported at #6466

This issue also has a copy (open) in our private ticketing system. I will inform you as soon as we fix the issue.