7 Replies Latest reply on Jun 28, 2013 11:19 AM by philmodjunk

    Method for Setting an Order Field Based on Calculations?

    James_L

      Title

      Method for Setting an Order Field Based on Calculations?

      Post

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

        • 1. Re: Method for Setting an Order Field Based on Calculations?
          philmodjunk

               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.

               No let function required. wink

          • 2. Re: Method for Setting an Order Field Based on Calculations?
            James_L

                 Beautiful!  

                 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.

            • 3. Re: Method for Setting an Order Field Based on Calculations?
              philmodjunk

                   In which case the second option would seem to apply: Use a field of type calculation to reference the data in the related table.

              • 4. Re: Method for Setting an Order Field Based on Calculations?
                James_L

                     Precisely.... but my original question remains, "What's the best method for doing it?"  

                • 5. Re: Method for Setting an Order Field Based on Calculations?
                  philmodjunk

                       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:

                       Table1::CreationDate

                       I can now get sorted portals and sorted relationships for Table2 that sort by date.

                  • 6. Re: Method for Setting an Order Field Based on Calculations?
                    James_L

                         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? 

                    • 7. Re: Method for Setting an Order Field Based on Calculations?
                      philmodjunk

                           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.