8 Replies Latest reply on Oct 8, 2009 1:24 PM by trialuser1111

    Embedding a calculated field name into a lookup function

    trialuser1111

      Title

      Embedding a calculated field name into a lookup function

      Post

      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.

       

      Thanks 

        • 1. Re: Embedding a calculated field name into a lookup function
          mrvodka
             Have you tried using the auto-entry by calculated result instead?
          • 2. Re: Embedding a calculated field name into a lookup function
            comment_1
               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.
            • 3. Re: Embedding a calculated field name into a lookup function
              trialuser1111
                

              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? 

               

               

              • 4. Re: Embedding a calculated field name into a lookup function
                trialuser1111
                  

                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? 

                • 5. Re: Embedding a calculated field name into a lookup function
                  comment_1
                    

                  The Lookup() function requires a field reference as its argument. Your calculation gives it (presumably) a field name (i.e. a text string).

                   

                   

                  • 6. Re: Embedding a calculated field name into a lookup function
                    trialuser1111
                       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.
                    • 7. Re: Embedding a calculated field name into a lookup function
                      comment_1
                        

                      trialuser1111 wrote:
                      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.


                      • 8. Re: Embedding a calculated field name into a lookup function
                        trialuser1111
                          

                        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.

                         

                        Thanks