Method for Setting an Order Field Based on Calculations?
I've got a One ---> Join----> One relationship that I want to order by date. The tables are:
Projects [one record] ----> Research_Records_Join [one] <------> Research_Line_Items.
The Join table joins Projects :: ID to Research_Line_Items::ID, thus allowing one bit of research to apply to several projects and vice versa.
I frequently need to order all join-table records related to a given Project record, sequentially by a date field in the Research_Line_Items table.
I've got a script that will do that for many functions, but it would be enormously helpful if a calculation field could do that in the Join table, keeping the sort order up to date as new records are added to the Research_Line_Items table (and consequently to the Join table).
What I'm envisioning then is a calculation field in the Join table that (1) finds all the records in that table related to the same Project ID record; (2) sorts them based on a date-field in related Research_Line_Items record; and return a number so that 1 is the earliest record in that found set. Currently I run a script to order the records, but it's got some limitations that would be overcome with a calculation field that does it automatically.
Maybe something with "Let"?