TeamDesk Knowledge Base & Support

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 (MMB) 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 (MMB) 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.