5 Replies Latest reply on Mar 7, 2013 11:44 AM by AnthonyS

    Sort author list using 'naming_order' in related table



      Sort author list using 'naming_order' in related table


           I have a database that contains three tables: proposals, collaborators, identities

           Simplified, it looks something like this:

                          proposals                     collaborators                     identities
                          proposal_id                     proposal_id                     identity_id
                          proposal_title                     identity_id                     name

           proposals.proposal_id = collaborators.proposal_id

           collaborators.identity_id = identities.identity_id

           I am trying to create a portal in the 'proposals' layout that will display the authors (identities) that are related to a given proposal, ordered by collaborators.naming_order. I have attempted to accomplish this using two methods, but have failed miserably.

           First, I sorted on 'naming_order' in the relationships. Second, I created a calculated field in 'identities' that pulls the corresponding 'naming_order', but the calculated field does not update when switching from one proposal to the next, so the names are out of order.

           The order in which the authors are displayed on a given proposal is very important; collaborators.naming_order must be followed. Authors (identities) can be, and often are, included on multiple proposals. 

           I cannot figure out how to accomplish this, seemingly simple, task. Any help would be greatly appreciated. Please let me know if I have not painted a clear picture of what I am trying to accomplish. I have attached an image of my simplified proposals layout for reference. You will see that the "First Author" shows up second. 




        • 1. Re: Sort author list using 'naming_order' in related table

               A better view of the relationships... 

          • 2. Re: Sort author list using 'naming_order' in related table

                 IF you use a portal to collaborators (NOT identities) with identities::name included in the portal row, you can either sort the portal or the relationship by naming_order and they will appear in the portal in that order.

            • 3. Re: Sort author list using 'naming_order' in related table

                   Thanks Phil! 

                   This woked wonderfully for the portal! I can't believe I didn't try this while troubleshooting. I have one more area where I still may need some help. I also have a calculation field, in the proposals table, that takes the list of authors, and creates an 'authors_list'. This idea behind this list is that the authros names will be listed together (compact) in a single line of text. The 'authors_list' caluculation, which lives in the 'proposals' table, looks something like this:

                                  Field Name                     Type                     Options / Comments
                                  authors_list                     Calculation                     Unstored, = Substitute( List(identities::full_name); "¶"; "—")

                   I still cannot get this list of authors, displayed in the correct order (naming_order). We would like to use this compact list for our program printing layout. Any ideas?



              • 4. Re: Sort author list using 'naming_order' in related table

                     Put a calculation field in collaborators that simply copies the name from identities.

                     Specify your sort order for the relationship, not the portal on which it is based.

                     Then modify your calculation to list this new calculation field instead of full_name.

                     The calc field, cFull_name defined in Collaborators:


                     and authors_list then becomes:

                     Unstored, = Substitute( List(Collaborators::cFull_name); "¶"; "—")

                • 5. Re: Sort author list using 'naming_order' in related table


                       Your solution to my related problem worked like a charm! I am very impressed with the speed at which you deliver your spot on soulutions! Keep up the good work!