Spreadsheet Formulas and Calculating Return v...

[Buglouse]

Spreadsheet Formulas and Calculating Return values In Instance.

Having a column with a SSF (SpreadSheet Formula), what is AppSheets behavior? I would like to have a SSF and appsheet run the calculation on sync (or before with an appsheet formula) and publish the calculated value for that row. “#VALUE!” is my constant return with SSF.

Am I able to remove the SSF and add an ASF (AppSheet Formula) for same instance calculation?

My objective is to have a column with a SSF calculated before sync, on data change. I had this as a result before using a VC (Virtual Columns) and hiding the original column, but I am attempting to shorten sync time and the amount of VCs.

What is a solution that has the lowest processing cost and immediate return of result? I have used VCs to replace my SSF columns returning the desired behavior, not sure how sync time was effected, having ~15SSFs to replace with VCs is undesirable. Would I replace all of my SSF with ASF, burdensome…

[MultiTech]

@Buglouse it depends on where you’re wanting to put the “taxing” part of the calculations?

Do you want the server to run them? (SSF)

Then you have a hit on sync time, as the device uploads each edit then waits for the server to run the SSF before moving on to the next update.

Do you want the device to run them? (ASF)

Then you have a hit on form opening/editing/saving, view navigation actions, as well as the sync - as the VCs are recalculated on each of these events. (View navigation causing the VCs to recalculate is dependent on actually going to view that looks at the table.)

The third option is to take your ASF and migrate them into actions, then create a “Group” action to run them all, and use that group action on the “Save event” for the form view.

This will cause the calculations to happen when saving the form, but NOT when syncing, navigating, etc.

You should take caution is you’re thinking of this third option, as this leave scenarios and spots in your app where the columns that your users are thinking are “Live” aren’t actually and could potentially contain out-dated data.

If you embark on this, you need to ensure that at every point where a user saves a form for this table, these calculations run.

One of the added benefits here is that you can actually make that grouped action visible, giving users a way to “trigger” the recalculation.

As long as you cover your bases, I’ve had great success with the third option; but I’ve also learned from some near disasters of data loss.

(^_^)

Happy Apping!