Your method sounds do-able... Just wondering 'why?'
I'd imagine the reason it's needed is because different user locations require records to be displayed in different priorities.
As far as improving on your current method, I would suggest not using fields at all, and having a sort field that's calculated with a case statement based on account privilege. This way, you could create (or duplicate) a privilege set for each location, and calculate a single sort field based off that.
IE assign users privileges:
Sam is assigned privilege set "San Fransisco"
Dan is assigned privilege set "Denver"
And sort field calc:
Case ( Get(AccountPrivilegeSetName) = "San Fransisco" ; "1" ;
Get(AccountPrivilegeSetName) = "Denver" ; "2";
You could store the LAT/LON coordinates with each user and then use the DistanceBetweenPoints custom function to calculate the distance between all user tuples:
and use the distance to sort.
The most efficient algorithm to choose depends much on the number n of users:
- scaling linearly, distances unstored and calculated on the fly. May soon become slow since upon sorting must be recalculated.
- scaling quadratically (more exactly: n*(n-1)/2), distances stored in a join table. But needs only to be calculated once, when a new user is added or when a user moves. Can be used immediately for sorting.
Edit: Of course also the second algorithm scales linearly, but storage consumption scales quadratically.
Message was edited by: MartinBraendle