I’m new to grist and totally love it.
I understand (and agree with) the rational ebehind enforcing the formulas to be defined column-wise.
However, when using a classical spreadsheet, I tend to use per-cell formula in some cases as a simple calculator.
For instance, when I fill data in a price_vat_incl column, I would sometimes fill the value directly (say $12) but other times only have pricing with VAT excluded (say $10), and fill =1.2*10 instead.
have one column price_vat_incl_expr with type “text”, containing for instance values 12 and 1.2*10
use another column price_vat_incl, with formula eval($price_vat_incl_expr).
It works!
PS I thought I could use a trigger formula like eval(value) if type(value) is str else int(value) but it does not work. I guess the type of value and of the function result must be identical.
You could also have a VAT Included toggle column to toggle or check on if the value entered in price_vat_incl includes VAT.
Then, as a trigger formula, use the formula:
if $VAT_Included:
return PEEK($price_vat_incl) * 1.2
else:
return PEEK($price_vat_incl)
For the trigger formula settings, set Apply on changes to: to Current Field and VAT Included. This way, if you toggle VAT Included after inputting the value in Price VAT Incl it will still update the value to reflect your VAT calculation.
If VAT is not a constant, then I would swap out the VAT toggle for a VAT value column, and change out the trigger formula to evaluate if the VAT value column is greater than zero and perform the calculation as needed.
Just another approach, and probably a little more intuitive if you have other users utilizing the product.
thank you for your help, I had not thought of using a toggle, and you made me discover PEEK!
Yes, in my case, VAT is a constant 20%.
In my mind, VAT_Included tells whether or not the raw value I enter (copied from a quote I got from some supplier) already includes the VAT or not.
Hence the trigger formula
is inverted: one should swap the if and else expressions.
I tried it, it works, but has a major drawback:
toggling on->off then off->on does not bring the price back, the 20% raise is remembered!
I understand where this behavior comes from, but I don’t like seeing the raw data I enter being overwritten.
A cleaner solution would involve three columns:
raw_price
raw_price_has_VAT_Included
price_VAT_Included, computed with a formula, from the former two columns.
This requires more screen estate (more columns), but I guess grist has solutions for improving the view.
Maybe I could for instance only display the price_VAT_Included column, and show a widget in card form with raw price details.
I haven’t tried using widgets yet though.
I still think that my use of per-cell formula is legitimate and would be nice to get supported
But not at the cost of using eval, I agree with that.
That makes sense, it made sense in my mind when I typed it up the other night, I think I was thinking the toggle was more like “Calculate VAT” or something.
Going back to one of your original thoughts, and building on the use of PEEK() you can make this work:
Price VAT Incl Trigger formula:
eval(PEEK($price_vat_incl))
The only thing here is that the column type has to remain text for the eval function to work on it, so you wouldn’t be able to use it as a numeric or have it nicely formatted for your currency.
If you change your column type to numeric, for currency, you can coerce your PEEK value to a string by wrapping the column id with str().
eval(PEEK(str($price_vat_incl)))
I think that’s probably the closest you’ll get to a per cell formula without extra columns and with relatively low overhead from a performance perspective. It does seem like In Cell Formula (evaluation), or something, could be a fairly easily implemented additional column type utilizing this solution.
I’m still learning python, so I wasn’t aware eval was an option. Thanks for that.
