I think an indexed static field with an auto-enter calculation might serve you.
Please wait for more feedback from others.
If I understand well, you have 2 tables:
And what you want to do is aggregate the last x data records and display it on a project layout. Correct?
What I would do:
- create a summary field (summaryField) in data.
- create an unstored calc in projects such as rightvalues ( data::id_data ; X ) // id_data is the primary key of data table
- create a new relationship (lastXdata) between projects (on unstored calc) and data (on id_data )
- place the summary field (lastXdata::summaryField) on the project layout.
Remark: You will still need to Refresh the window from time to time.
Thanks to both of you for such a quick reply. I follow your logic but I must be missing a key concept. I though one could not have a relationship based on an unstored field. I did try using a field with auto enter calculation but it does not automatically update. see below. Thanks for your patience.
Update - my bad. Figured out the auto enter problem. Still stuck on relationship with unstored field. Since my auto enter is based on a relationship value, that seems to be my problem.
sorry, I made a mistake in the unstored calc definition. Should be:
rightvalues ( list ( data::id_data ) ; X )
Shouldn't that be:
substitute( rightvalues ( list ( data::id_data ) ; X )) ; ¶; "+") & 0
I don't think so.
Your expression returns the list of values.
My updated expression works to total them.
yes, the edit makes a difference
but this is a different approach than having a separate relationship and a summary field. (good approach also, by the way)