I've worked out a solution, but it's not exactly ideal. I'll post it here in case anyone else finds this thread in the future.
First, create a field called TABLE2::TRIGGER_UPDATE. We will use this field to trigger a re-lookup of the TABLE1::FIELD value every time TRIGGER_UPDATE is modified. If you want, you could set TRIGGER_UPDATE to be something like a modification timestamp housekeeping field -- this will force the update every time the record is modified. Or, you script some other way to change the value of TRIGGER_UPDATE whenever you wanted to update your record.
The next thing you need to do is create an auto-enter (and replace existing value) field called TABLE2::FIELD, defined as follows:
If ( TABLE2::TRIGGER_UPDATE <> "" ; ExecuteSQL ( ... ) )
The ExecuteSQL calculation will vary from case to case, but it's purpose is to pull the current value of TABLE1::FIELD. In my case, it goes something like:
ExecuteSQL ( "SELECT FIELD FROM TABLE1 WHERE PrimaryKey = ?" ; "" ; "" ; TABLE2::ForeignKey )
So! Because the TABLE2::FIELD can now be indexed, all of the calculations in TABLE2 based on it can now be indexed as well.
The performance hit using modification timestamp and ExecuteSQL() could be considerable. Also, it doesn't guarantee the data is current. I'd recommend a scripted approach to updating the trigger field and using a relationship to lookup the data, rather than ExSQL().
But otherwise, yes, this is a good technique for trying to do what you want.
David, thanks for questioning my use of ExSQL and making me think!
Somehow, my brain was stuck on the the misapprehension that I was creating an indexed calculation field rather than an auto-enter field with replace existing value.
For readers unfamiliar, the difference is that indexed calculation fields must only reference the local table. As soon as you reference a remote field, the calculation is forced to be unstored, which causes the search slowdown that I was trying to avoid. ExSQL was my clumsy way around this problem by referencing a remote field as if it was local. (It has since occurred to me that GetField ( "TABLE1::FIELD" ) or Evaluate ( "TABLE1::FIELD" ) would also serve this purpose for an indexed calculation field, rather than my ExSQL command.)
But David is exactly right. So, my new definition for TABLE2::FIELD is:
If ( TABLE2::TRIGGER_UPDATE <> "" ; TABLE1::FIELD )
David, your recommendation to using a scripted approach to update the trigger field is also helpful.