option for trigger formulas to trigger only on changes to existing records

[aeshna-cyanea]

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

[hjhp]

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").

[dsagal]

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.)

[hjhp]

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)?

[aeshna-cyanea]

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.