Under what context do you need them sorted in this order?
If you are sorting join table records on a layout based on the join table, you need only specify that they be sorted by the date field from Research Line Items and they will sort as needed. You can even include sub summary layout parts that specify the date field from the related table as the "sorted by" field.
But if you need a portal to the join table to be sorted or a calculation uses List to return items from the join table sorted by this field, then you'll need a calculation field in your join table, but it need only be an unstored calcualtion field that copies the date value from the date field in Research LIne Items. Then you can specify a sort order for the Join table records in the relationship or in portal setup for a portal.
And that totally will work for ordering: but one of the things that I'd like to simplify is the need to refer to line-items in recurring reports. So for instance a line in a report might read, "See item "& [join_table :: c_order] & " above," returning the result "See item 5 above" where that field will change from time to time as records are added. Running a script to return the correct number is of course possible: but it would greatly simplify things if a calculation kept that field up to date.
In which case the second option would seem to apply: Use a field of type calculation to reference the data in the related table.
Precisely.... but my original question remains, "What's the best method for doing it?"
I don't know what you mean by "method". Perhaps we are not discussing the same thing.
If I have Table1-----<Table2
and I need to sort the Table2 records by the value of Table1::CreationDate, I can define a calculation field in Table2 with "Date" as the result type and this expression:
I can now get sorted portals and sorted relationships for Table2 that sort by date.
Ah, I see where we're breaking down, here.
I understand that much.
What I'm asking for is a good method for making not just a sort (that part is easy), but for including a calculated field that actually numbers the record's position in that easy sort.
So I've got all my c_date fields: easy to sort the records indeed. But that wasn't exactly the problem. What I need is a calculation that will count up the fields [edited: I meant "records"] in that sort and calculate the current record's position in it.
See, I do a lot of cross-referencing: "See Item 5 above," where today's item 5 might be tomorrow's item 12. If I can reference a c_ field that automatically counts up the number of related records and returns the current record's position, then it will reflect 5 today and 12 tomorrow when I've added more related records.
That make any sense at all?
You can do this one of two ways:
1) don't use any field at all. Use the Insert menu to insert the record number symbol into the portal row.
2) Use Get ( RecordNumber ) to return the same number in a field. Select "Do not store..." in storage options and do not set this up as an auto-entered calculation.