Embedding a calculated field name into a lookup function
I need help using a lookup function within another lookup function, where the "inner" lookup is used to identify the field name for the "outer" lookup.
I want to be able to lookup the market sector ownership of a company and have that market sector change dynamically based on which client the report is for. I tried the following calculation and FileMaker wouldn't allow it:
Lookup ( Analytics::Lookup ( Market Data 3::Mkt Sector ) )
Is there a way to get a function like this to work? The "inner" lookup (to identify the sector) matches the the field name in the Analytics table exactly by design, so I know that's not the problem. If I used a script and some variable I suppose that would get me the right result, but I'm trying to avoid scripts here because I plan to use this relationship/solution in multiple forms throughout the database.