Sorting a table based on the results in another table.
The specific example is the Specifications Tab on the Asset screen to sort the fields based on the master sort order defined on the Specification itself. The problem is that the AssetSpec table has the order at the time the record was created and does not automatically update the existing records and so you end up with fields in different orders on different assets based on how old they are and when the new fields were added to the spects. I solved this specific issue with a database function that returns the sort order off the ClassSpec but was told by our IT that in the future that is not an acceptable solution as they want it to be database agnostic.
I tried relationship and it seems you can't sort on something that is not part of the table or based on that table. What other options are there?