gristlabs
option for trigger formulas to trigger only on changes to existing records
Describe the problem to be solved
currently, trigger formulas always proc on new records, no way to untick the "trigger on new" checkbox when selecting triggers. Alternatively, fix it so that the formula only triggers only if the new record has non-empty columns that the formula is watching.
Describe the solution you would like
UI change to let us disable new record triggers, or a fix to disable triggers when there are no matching columns in the newly created record
This would be a huge improvement. I have just migrated a pricing spreadsheet and one thing I'd love to add is a column that looks up market prices by API; at the moment this is not realistic because the options to limit when/how a formula is evaluated are too limited. (For example, there is no point calling a price lookup API upon record creation — you should wait at least until product name has been filled in — and I don't want the lookup to happen all the time, only when I change some defined field e.g. "market price date").
Could you share examples of particular use cases? Many, I believe, are possible today.
The basic idea is that a trigger formula can itself contain conditions. If the issue is that it shouldn't trigger for a new record when it has missing data, then you can check for missing data in the formula.
For @hjhp's example, let's say you want some action called only when column Date or Stock is changed, and not on new records if the Date or Stock column is blank. You can achieve that with a trigger formula that triggers on changes to Date and Stock columns, with the formula like:
if $Date and $Stock:
return getPriceForDate($Stock, $Date)
else:
return None
(Note that fetching external data, as in this example, isn't properly supported; though may be attempted by self-hosters by enabling the experimental REQUEST function.)
Interestingly it hadn't really occurred to me to reverse the "forced" trigger-on-create behaviour via an if-statement. Perhaps the question is more philosophical/UI-related: if a checkbox can never be unchecked, should it be presented as a checkbox?
@dsagal could you also please clarify this (slightly tangential): let's say I put your formula into a field called "market_price". If I wanted to modify your if-statement to check that market_price was not already filled in, would I have to use PEEK(), e.g. if $Date and $Stock and not PEEK($market_price)?
That was the request though, we want to move the logic/decision of checking for data from an if condition (in the individual formulas) and into grist itself. I can agree that all it really does is save a couple lines of code in the formula - this way it's easier to initialize with a certain value and then have a simpler formula for generating subsequent ones, instead of adding if/else.
First, to answer @hjhp: if in a trigger formula for column "market_price", you want to look at the current value of "market_price", use the special value variable, which is equivalent to PEEK($market_price). Otherwise, yes, you need to use PEEK(), which says to look at the current value without requiring it to be brought up-to-date first. Normally, if you refer to a column (e.g. $A), Grist will bring that column up-to-date before this formula, but if you refer to $A from a formula for $A, then that creates a circular reference. That's what PEEK() avoids (or using value).
To @aeshna-cyanea , the reason it's trickier than it seems to make this useful is that the distinction between a new record and an existing one is a little hazy in spreadsheet-like workflows. In particular, when using Grist normally via the UI, to add a new row to a table, you start typing into one of the cells. That creates the row with default values for other cells. Then as you type more fields in, the record is already the existing record, no longer a new record. In normal work, you'll have have both Date and Stock set together at the time the record is created: at most one will be set for the new record, and the other would be for an existing record. But still, when they aren't both set, you can't make a useful call to fetch price. So you still wouldn't be able to avoid a conditional check inside the formula. Does this make sense?