I am looking for the best way to allow for user specific sorting of a list. I have a list of users and their locations where each user needs to be able to sort by proximity to the other users.
Here’s my example:
Sam is from San Francisco; Dan is from Denver; and Chris is from Chicago.
Sam sorts and gets, in order: Sam, Dan, Chris
Dan sorts and gets, in order: Dan, Sam, Chris
Chris sorts and gets, in order: Chris, Dan, Sam
My best solution to date is use location specific fields with pre-calculated values. The file would then know who the user is (by account) and the user’s location and be able to sort on the proper field. The table looks like:
User, Location, SanFrancisco_sort, Denver_sort, Chicago_sort
Sam, San Francisco, 1, 2, 3
Dan, Denver, 2, 1, 2
Chris, Chicago, 3, 3, 1
So, when Dan sorts, FM knows who he is and his location, then picks the right field to sort on (Denver_sort). When other users sort, they can get the right sort the same way and not interfere with any one else’s sort. (I don't think I can use a single sort field and calculate values on the fly because two users trying to sort at the same time would put different values in that field.)
Does this make sense? Is there a better way to do this? While I can script the calculation for the values for those _sort fields (knowing the distances between cities), having a separate field for each location seems like too much. The actual list of users and locations is, of course, much larger.