Calculating cell values based on another cell's object's properties

Tags: #<Tag:0x00007f8b1d9eb948>
#1

We’re passing objects into our table data, of the format:

  {
    quantity: 20,
    stock: {
      shortName: 'TSLA',
      currency: 'USD',
      price: 34.7,
    }
  },

The columns will be:

  • stock.shortName
  • quantity
  • value

We need to calculate the value using the following formula:

stock.currency + " " + (stock.price * quantity)

EUR 43.10

The table will render something like this:

What would be the best way to do this?

I see there are:

Formulas don’t seem to give us access to the cell object’s content.

I know renderers are powerful - is it possible / performant to get the data for the row in each renderer? I created a test renderer and see it can fire up to as many times as there are cells in the table.

Callbacks look like another option - but we would probably need to pre-calculate the value for all cells when adding data to a table, and then use the same function when we update cells.

Here’s a fiddle if it’s easier to demonstrate:

So just looking for advice on the best approach.

Thank you.

#2

I think that there are a couple of approaches that you can take

  1. Make value as ROUND(B1 * 4.31, 2, false) and add EUR inside a renderer
  2. Attach a function that does it using Math

but as long as it works for you and doesn’t lower the performance it is a good choice.

#3

Hi Aleksandra,

The problem we face is that 4.31 (price) and EUR (currency) are both sub-properties of the stock

Is it possible to reference them in a formula?

Also - you mention “Attach a function that does it using Math”. Can you elaborate?

In either case, we would need to reference the sub-properties from the stock object.

#4

OK, I came up with this:

import numbro from 'numbro'

const symbols = {
  EUR: '€',
  USD: '$',
  GBP: '£',
  YEN: '¥',
}

export default function (instance, td, row, col, prop, values, cellProperties) {
  const data = instance.getSourceData()
  const datum = data[row]
  const quantity = datum.quantity
  const stock = datum.stock
  const price = stock.price
  if (quantity && price) {
    const currency = stock.currency
    const symbol = symbols[currency] || currency
    const text = numbro(quantity * price).format({
      thousandSeparated: true,
      mantissa: 2
    })
    td.className = 'htValue'
    td.innerHTML = `<span class="symbol">${symbol}</span><span class="value">${text}</span>`
  }
}

The key was getSourceData().

It looks great too:

The only down side is that each time any cell changes, it will run as many times as there are cells in a column, or cells in a table.

It would be cool if we could optimise this, as we may end up with tables that are 1000+ rows.

Any suggestions?

#5

we’re working on eco-renderers that will run only when cells get changed. Here’s a link to follow https://github.com/handsontable/handsontable/issues/5769
Currently whatever change you make or add metadata you get extra calls.But… I like your approach.

1 Like
#6