Hi I’ve created a new sheet using “New sheet from subtable”, the target sheet has a field which requires to be calculated each time a new entry is created. Can I set to recalculate formulas in each entry when it is created from the source subtable?
Hi Brandon,
Just to make sure I’m reading this right — you have Sheet A (with the Subtable) generating Sheet C via New Sheet from Subtable. In Ragic’s terms, Sheet A is the parent and Sheet C is the new/child sheet. It sounds like you want a formula field on Sheet C to calculate correctly the moment a new Sheet C entry is auto-created from a new Subtable row on Sheet A — is that correct?
If so, this ties into a general behavior in Ragic: formulas are only triggered on the interface where data is actually entered. Since the Subtable row is entered on Sheet A’s form, and the corresponding Sheet C entry is generated in the background rather than through Sheet C’s own interface, the formula on Sheet C may not recalculate automatically at that point. More background here: Why Didn’t the Formula Trigger or Return the Correct Result?
Now let’s align on some Ragic terms again - the sheet with the Subtable is the parent sheet (Sheet A), and the new sheet generated, Sheet C, is treated as a related sheet of it. For recalculating the formula field on the related sheet (Sheet C) at the moment a new entry is created from a new Subtable row on Sheet A, the simplest setup is on the parent sheet (Sheet A), not the new sheet: go to Form Settings > Form Settings > Recalculate all formulas on related sheets. With this enabled, saving a record on the parent sheet (Sheet A) — including adding a new Subtable row — will trigger formula recalculation on its related sheets, including Sheet C.
One caveat: this has a 2000-record cap per save. If related records exceed that, recalculation is skipped for that save rather than partially applied.
Exactly!
That’s what I’m looking for. Such a simple solution yet I missed it. Thank you so much!
1 Like