AnsweredAssumed Answered

Method for Setting an Order Field Based on Calculations?

Question asked by James_L on Jun 25, 2013
Latest reply on Jun 28, 2013 by philmodjunk


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"?