I have a fm solution that has a meetings table, organisation table (companies, associations, etc. ), a contacts (people) table, and a tasks table, and then a bunch of connecting and auxiliary tables.
Each user has their own ID, and when they are assigned a task that ID is saved with it.
I would like the users to be able to see how many uncompleted tasks they have from any context, whether viewing the meetings database, organization database, or contact database. This number is easy enough to get using execute sql, or a little less easily with relationships, but I'm having trouble figuring out where this calculation needs to be. The number is the same from any context, so global storage would be nice, but global fields only get updated when the session is instantiated or they are updated manually, so a simple global calculation won't work (I'm a newbie so I just found this out).
Is there anyway to do this without having to create new fields in every table? And, would non-global calculation fields slow things down since they are going to be recalculated for every field (even though, unless the records in the ToDo table are altered, the result should be the same for all the records in the other table) ?
You could have an OnTimer script/script trigger set up in the on open script that updates the global field using the executesql method. My guess is a frequency of 5 minutes would work.