Home      FAQ      Forum      Idea Exchange      Ask a Question      My Stuff      Help   
  
Recalculate calculated columns on change in related table
When I use a calculated column (so not a formula column, but a regular column with a calculate formula), that value gets updated whenever the columns used in the formula change.

However, this does not happen when using a Lookup column in the formula.

It would be great if the column value would also be updated when using Lookup columns in the formula.

I am trying to use this to 'cache' lookup values in my table to increase performance. Using regular formula columns on using the lookup columns directly both incur a performance hit. The only alternative I've found is to set up complicated triggers on the related table, to copy lookup columns to the 'cached' columns.

If there's a better pattern for caching column values from other tables, then I'm open to suggestions, but otherwise this feature seems like the easiest way to implement this.
ID
1179
Category
TeamDesk
Author

Kim
Date Created
2/23/2018 1:36:41 AM
Date Updated
3/8/2018 7:34:13 AM
Status
New Idea
Score
30
Promoted By
Philipp MatuschkaPatricio BustosKim
Comments
Patricio Bustos 2/23/2018 9:22:56 AM
I have created new tables with inserted values ​​(such as dimension) that summarize via relation the information from another detail table.
I did that as you say to improve the performance. The problem is when you have many dimensions. That makes difficult. that was my solution, if there is something that can be done at another level it would be fantastic.
Philipp Matuschka 2/28/2018 6:40:42 AM
If there is such a thing as an anti-promote then I would like to do so. This would be very dangerous for many of my implementation as I rely on the value of that field only changing in the described circumstances.

At the same time I recognise the challenge being described and have indeed implemented the way described a number of times for efficiency reasons. So maybe the answer is to have a new column type which is a "cached column"
Kim 2/28/2018 7:58:18 AM
@Phillip I agree with your concerns. However, there is a certain inconsistency in the way the system deals with lookup columns. For example, a time-based trigger based on a summary lookup date column (Max of Date Modified for example) will actually trigger if the related table is updated. However, a calculated column will not be updated.

Maybe a setting on the column to eager/lazy update would have the desired effect that you are describing.

For me, the goal is to 1. easily persist/cache data from related tables in my table. 2. easily control the way this data is updated (never/automatically (on related record update)/only on record update).
Philipp Matuschka 3/8/2018 7:34:13 AM
@Kim

With that more detailed description I could see myself in favour and in fact I think one would end up with the following options. each of which would be ticked or unticked

- default - only do the calculation on initial creation of the record. I have found myself writing code in the default/calculate formula to ensure this as I DO want an initial default but I DO NOT want the then entered value to later change as a result of calculate. I raised a comment or query or something about this years ago.

- calculate - IE as described by you when any of the directly or indirectly referenced "regular" columns in the same record change, the value of this column is also recalculated (this is today's behaviour)

- cache - as suggested by you, when any referenced data anywhere in the database changes, the value of this column is also recalculated. The rationale for this is as originally defined by you and in my case mainly where I am showing list of records in Views. My work around today is time dependent triggers which update "regular" columns from formula, lookup or selection columns, but
a) it requires careful management of triggers and
b) can show out of date data in the first few minutes.

I do appreciate that the third option means trading off "on screen" performance against "background" performance and I have noticed that other aspects of my DB slow down depending on how many time dependent triggers I have. Maybe the team at Foresoft have some good ideas on how to improve this.
Feedback
 
Back to Search Results