I have a long standing FM issue. You can only link tables via indexed, local, fields. Reasonable, logical, and very limiting.
I recently tried using SQL to get a remote field as a local field. But when you set that Calculation to STORE the result it no longer updates, it only fire one time.
Why do I need this you ask?
Here is one example:
JOB Table with a field for Salesperson.
INVOICE Table linked to Job by Job ID.
SALESPERSON Table: I want to have a portal with all the Invoice for that person. It needs to be a portal so you can interact with it.
So how do I link Invoice to Salesperson? If I use a look field it will not automatically update if the Salesperson on the Job is changed.
Currently I do use a Lookup field with a series of scripts to re-lookup if the Job's salesperson changes, but this is cumbersome. And I have other similar situations that deal with thousands of records, so re-lookups are slow and the chances of encountering LOCKED record causing the lookup to fail is very possible.
The person who solves this will be richly rewarded (richly being in the eye of the beholder).