Auto calc formula-based fields for reporting

[wcksmith]

I saw a post recently that described how to build an additional table with formulas in the fields for reporting while avoiding virtual columns in the main tables in an effort to make the app more nimble.  It works great for my app.  I have a "report table" that has records for each of the four locations I'm tracking. 

One of the fields in the table is for Timestamp.  The article said to add an action that updated the timestamp to NOW() so when clicked on, it updated the timestamp and all other calculations in that row.  It works great when clicked on - it recalculates the formulas in each row showing the correct data in each row. But it requires that the viewer click on the action icon for each row to update it.  

How do I get these fields to auto-calc so the viewer doesn't have to know to click on the action button for each row to get the latest data?  I guess I could come up with an automation and set it to go off every hour or so, but this app really needs the viewer to see the latest up to the minute data to make good decisions.  

Thanks for any help!  Wick

[WillowMobileSys · Accepted solution]

---

@wcksmith wrote:

How do I get these fields to auto-calc so the viewer doesn't have to know to click on the action button

---

There are 2 ways:

A)  If you need or just want immediate updates (e.g. current user needs to see the "Total Beds Available" changes right away), you can implement actions that are executed with the Form Saved behavior, on any Form View that potentially is making changes that impact the "Total Beds Available" data.

B)  You could use Bots to execute actions for any row Add or changed with data that may change the Beds Available.  Bots take a few seconds to a couple minutes to process AND the results get pushed to the user device.  But using Bots is generally easier and more comprehensive.

For BOTH of these, the idea is to:

1)  Create an action that updates your Timestamp column.  You may already have this in the form of your inline button
2)  Create a second action of type "execute an action on a set of rows" that identifies the "Beds Available" rows to be updated - I assume it is only one by location.   Then use Action from 1) as the Referenced action.

I hope this helps!

[wcksmith]

Thanks for the help.  I was able to add actions to the forms that impact those numbers.