AnsweredAssumed Answered

List Function Sort Order

Question asked by BoiceDaniel on Feb 20, 2011
Latest reply on Feb 22, 2011 by BoiceDaniel


List Function Sort Order


I have a list of ids from a child table in a field in a parent table in a certain order.  I have a calculation field in the parent table that gets a list of data from the child table.  I want the list of data from the child table to appear in the order I have set in the parent ids field. 

The list function will sort the data alphabetically and by the definition set by the relationship, however it does not seem to want to unsort the records so the results will appear in the custom order that I have set in the parent field.

I thought about creating a variable and storing it in the child field, and then sorting it based on that, but of course that won't work because the records in the parent table all have different orders for the parent field. 

Perhaps I need a custom list function.  I could create a string based on a calculated field in the child table that I could then play with, but I just wanted to check to see if there is any other way to do this first, because that seems like it would get rather complicated.

I have defined a relationship between the parent table field with the child ids to the id field of the child table. 

The reason for all this is that in the parent table I have records of a list of publications, and the child table has a list of authors.  One author may have several publications, and one publication may have several authors.  The list of authors needs to be set for each publication to appear in the defined order for the citation.  The structure I have seems to work, except for the sort order for the authors using the list function.

I had it set up with a join table..  Perhaps that is the best solution; use a sort field in a join table for each publication.  But again that will get a little tricky arranging and rearranging the order and it seems a little redundant.  It would be nice if the list fuction had a variable that allowed you to specify the sort order.

Perhaps I could retrieve the author name each time the author is selected and append it to the end of the list in the parent field, then if it is deselected, have a substitute fuction subtract it from the list.  That might work to create and store the citation, except it is not very efficient in terms of the amount of data that is stored.