AnsweredAssumed Answered

Sort author list using 'naming_order' in related table

Question asked by AnthonyS on Mar 6, 2013
Latest reply on Mar 7, 2013 by AnthonyS


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.