Have you tried using the auto-entry by calculated result instead?
I don't fully understand the question. I guess you should use the GetField() function, if you want to identify a field name by calculation. However, I suspect you'd do better by re-examining your data structure instead.
I did try that, and I get the same error because FileMaker can't identify the field based on my syntax. I can't close out of the "Specify Calculation" window without erasing my calculation because FileMaker doesn't understand that I'm trying to use the inner lookup to specify which field name to reference in the outer.
When I click OK and the calculation is "Lookup ( Analytics::Lookup ( Market Data 3::Mkt Sector ) ) , FileMaker says "The specified field cannot be found" and highlights the second "lookup." This tells me that FM is treating the word "lookup" as text and not part of a calculation. Is there an order of operations that FileMaker follows for calculations?
Okay so it was a syntax error. I had to change the calculation to:
Lookup ( "Analytics::" & Lookup ( Market Data 3::Mkt Sector ) )
Which at least lets me close the window, but it returns a ? in my database, which means I need to look at my relationships and see where the disconnect is I guess?
The Lookup() function requires a field reference as its argument. Your calculation gives it (presumably) a field name (i.e. a text string).
So what must I do to my calculation to get it working? I just tried a simple test version of the lookup where the field reference was a calculation (I just split it up into Lookup ("Table::" & "Field") and I get a ? result in my layout. Seems the problem may still be with my calculation construction and not my database relationships.
Seems the problem may still be with my calculation construction and not my database relationships.
I believe it's both. As I said earlier, you need to use GetField() to convert text to field reference. I am saying this with reservation, because I still don't understand the context in which this takes place, nor the purpose of this exercise. My suggestion is to abandon this path and structure the data in a way that a lookup can be straightforward. Again, purely guessing, but you probably need to break records with multiple fields into individual records.
Okay, using GetField() I was able to create a calculation that worked. Now for some reason, I'm trying to use the exact same process and the exact same relationships to refer to a different field, and I'm coming up short. Here's the scoop:
The working calculation in my Meetings table: Lookup ( GetField ("Analytics::" & Lookup (Market Data 3::Mkt Sector) & " Avg.") ) / 1000000
This calculation uses the field Ticker (which relates Meetings and Market Data) to look up the market sector of the company, and then uses that newly identified field (e.g. "Analytics::Consumer Staples") to look up the proper value by way of another relationship between Meetings and Analytics. This works perfectly.
I tried to make another very similar calculation field in Meetings that looks up the value based on market sector AND market cap (e.g. Small-CapConsumer Staples). Didn't work. I thought maybe I was confusing myself with too many parentheses in the calculation, so I created a really simple field which is text via calculated result that's just Market Data 3::MktCap Group & Market Data 3::Mkt Sector.
So now my second lookup calculation field in Meetings is: Lookup ( GetField ("Analytics::" & Lookup (Market Data 3::MktCap_Sector) & " Avg.") ) / 1000000. I still get a ? on my layout.
I'm trying to figure out how one works and one doesn't since the tables and relationships used are all the same. As a check, I put that Market Data 3::MktCap_Sector field (the calculated text) on my layout to make sure it's spitting out the right text, and sure enough it is. What else can I check as a possible source of error? And please, if there's any other information about my database you need to know in order to help, ask specifically what and I can try to answer.