How to separate data from calculations in tables by performing simple calculations directly in layouts
Table “Progress” uses date field “Day” and number field “Variable” to monitor the evolution of a single decimal variable over many hundreds of consecutive days.
I want a layout to display not only the “Day”/ “Variable” pair of the current record but also that “Day”/ “Variable” pairs of the, say, fifteen previous records (let’s assume that the table is always properly sorted by “Day”).
The way I currently do this is by adding 30 calculated fields to the table:
“Day_minus_01” GetNthRecord ( Day ; Get (RecordNumber) – 1 )
“Day_minus_02” GetNthRecord ( Day ; Get (RecordNumber) – 2 ) etc…
“Variable_minus_01” GetNthRecord ( Variable ; Get (RecordNumber) – 1 )
“Variable_minus_02” GetNthRecord ( Variable ; Get (RecordNumber) – 2 ) etc…
This method works but has two major drawbacks.
1) It makes the “Progress” table very long and confusing.
2) It puts the two fields of raw data (“Day” and “Variable”) at the same level as a multitude of much less important calculated fields.
These problems tend to become unbearable as the number of calculated fields increases. What if I wanted to see not the 15 but the 60 previous records? What if I wanted to perform for each pair a dozen other calculations altogether?
Is it possible to perform calculations directly on a layout (for example in an edit box) in order to avoid crowding tables with simple calculated fields that end up being used only once?
If not, is it common for experienced developers to have tables with only a few fields of raw data and over a hundred calculated fields? If that is the only way, what are the best practices to name, organize and navigate through this ocean of fields ?
In advance, I thank you all very much for your insights. W.
I would suggest using dates instead of days. This will make it more reliable as you add values that span mutiple years. Create a calculation field "date_match" with an expression "date - x", where x equals the number of prior dates to shows. Now create a self-relationship where the match criteria is: date >= date_match and put the results in a portal.
- dg