We are working on optimizing our app to work as fast as possible. One thing we are looking at is calculations that we have in table columns. Can anyone shed some light how much impact it has to embed a calculation (especially a complex one) in a table?
One specific case is where we have a master table, a detail table and a sub-detail table. The details table includes a field that calculates the total amounts of the sub-details in a manner like this:
=SUM(FILTER(subdetail,"subdetail[detailId]=detail[detailId]")[amt])
Then the master table includes a field that calculates the total amount for the master in a manner like this:
=SUM(FILTER(detail,"detail[masterId]=master[masterId]")[amt])
Will this cause one set of cascading updates when we change a sub-detail item, causing the related detail row to recalculate its total and its related master row to recalculate its total? Or does every read of the master record cause cascading filters such that the master record filters down to each detail and then each detail filters down to each sub-detail? Or does that calculation work in some other way that is more efficient?
Another question we have is how much overhead it creates to store a commonly-used calculation in a table. An example would be an estimated arrival date for a package that is calculated as the shipping date plus seven days. We would display this on several screens and possibly use it in filters, but wouldn't need to reference it 90% of the times we read the row. Is it more efficient for the table to have a calculated field called estimatedArrivalDate with a formula like =actualShipDate+7 or to include that calculation in each screen and filter where we need it?
Hi @Michael_W_SCR, thanks for posting! Sit tight, as we're working with the team to find details and answers for your two questions. We'll respond back with updates. Thanks for your patience
Hi @Michael_W_SCR, thanks for posting! Measuring performance depends on multiple factors, such as the amount and type of data in your tables. Unfortunately we do not have a deterministic way to measure impact of specific changes. Nevertheless, we can certainly suggest some best practices for optimizing the formulas.
Considering any comparison between one column data from one table and another column data in the existing table in =SUM(FILTER(subdetail,"subdetail[detailId]=detail[detailId]")[amt]), performance may be affected. For best results, compare the column data to some specific value when using a filter. The formula can be changed to compare with THISROW()[detailId], which will help optimize the performance. For example in your sub-detail table you'll have: =SUM(FILTER(subdetail,"detail[detailId]=THISROW()[detailId]")[amt])
and for your master table: =SUM(FILTER(detail,"detail[masterId]=THISROW()[masterId]")[amt]).
To learn more about filters, you can also refer to writing efficient filters (Writing Efficient Filters).
To answer your second question, if you want to reference the same value in multiple places, for example =[actualShipDate]+7, best practice is to make calculations in table columns and reference the resulting column value on the screen as opposed to making the calculations on each screen. Another option is to calculate the formulas in an app as part of a block (not for a list component), so the value becomes global for the app screen and only needs to be calculated once.
Last but not least, thank you for bringing these discussions, which helps the community learn about best practices. We have realized the need for a best practices article. We'll work on putting together an article that will help in adopting best practices while developing apps on Honeycode.
Thanks for the response. Just so I'm clear, if you set a table cell to a formula like =[actualShipDate]+7, that doesn't cause the formula to be re-calculated every time you read the row?
For the formula =[actualShipDate]+7, once it is calculated in a table cell, the read request for the calculated value will not re-calculate, as long as the value of [actualShipDate] does not change.
A formula calculation will be triggered again if a dependent cell (in this case, the cell that is referred to by [actualShipDate]) value changes.
