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.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.