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.
The only way to sort a database is by one of its own fields. There is no formula sort or anything like that.
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.
Wouldn’t it be nice?
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.
Haha, our messages overlapped. Thanks for confirming this as a viable option.
Unfortunately, the join statement doesn’t work in a shared database.
It should work if serverupdate is off.
However, I suspect this might not actually work. Please try it and let me know, if it doesn’t work I should be able to fix it for the next release.
Jim, as you suspected, Join did not work with serverupdate “off”. Thank you for looking into it.
I got it working. This was one of those problems where making the fix itself was much easier than testing the fix to make sure it worked!