I would like to be able to sort a database using related data from a second database.
For example, I have Databases A and B. Database B contains a “ZipCode” field which is only displayed in Database A using the related() function. What I would like to do is to sort Database A using the related “ZipCode” from Database B.
Before I spend time trying to solving this, has anyone come across this and figure it out? I searched the documentation and forum and nothing came up.
I would create a temporary field in database A, populate it using a join with database B, sort A on the temporary field then delete that field.
Having said that, what I have actually done in practice in a similar situation is keep a dedicated sort field in A, which is automatically updated by the .ModifyRecord procedure of database B when a related field or fields in B is/are modified. The time overhead for that update each time is negligible and means sorting on the sort field, when necessary, can be as fast as possible.
As a workaround, I’m thinking on having a Temp field in database A, which would be filled with the data from the related field that I want to sort. It will be somewhat slow, but it will accomplish the task.