4 Replies Latest reply on Jan 19, 2016 8:11 PM by pruppert

    How do I perform Lookup with calculated Source Field name.

    pruppert

      I want to lookup a value in a related table. However, I'd like the source field to be determined via a calculation field.

       

      For example, I want something like the following:

       

      Lookup ( Table A::theCalculatedColumn  )

       

      where "theCalculatedColumn" is the name of a field that holds the calculated text value of the field to be looked up in Table A.

       

      I can't seem to figure out the syntax for this. Is it possible? How would I do it?

      Thanks.

        • 1. Re: How do I perform Lookup with calculated Source Field name.
          wimdecorte

          I think you should reconsider the use of Lookup().   Is this part of a scripted workflow?

          • 2. Re: How do I perform Lookup with calculated Source Field name.
            pruppert

            Thank you for the reply. I have realized I had a typo in my calculated column name . Now it is working with Lookup and the Evaluate function:

             

            Evaluate ( "Lookup ( Table A::" & theCalculatedColumn & " )" )

            • 3. Re: How do I perform Lookup with calculated Source Field name.
              wimdecorte

              Forgive me for being blunt but I cringe when you have to construct a calc that wraps a Lookup() call in an Evaluate() call in what seems to be a calculated field.

               

              And certainly you want to avoid hard-coding TO references like "Table A::", that will survive any name changes you do in your TOs.

               

              We don't know the context of why you are trying to solve things this way but I feel there must be a more elegant way that is safer and less cumbersome.

               

              Can you expand on what you are trying to do?

              • 4. Re: How do I perform Lookup with calculated Source Field name.
                pruppert

                Ha. Sorry to make you cringe. I appreciate your concern, and the points about table renaming are valid.

                 

                My exact situation is a little hard to describe, but basically I am converting a large reference book for work to a FileMaker solution. The book is hundreds of pages containing 264 different tables of data with hundreds of data points within each table. In the book, you literally look up a value at an intersecting row and column in a given table. This is all done manually by a person. The tables generally present the same variables but their values differ slightly from table to table due to certain conditions. I scanned and OCR'd the whole book to input to Filemaker to automate the the lookups. My solution needs to know specifically what intersecting column and row of a given table to go to given input criteria. Not all of the data is relevant to me. So, I've only taken the relevant columns and placed them into a single table that is used for the lookup. The columns/fields are named according to their matching input data. I just needed a way to tell Filemaker which column to go to, given the input variables.

                 

                It may sound crazy, but I am the only developer of this, and it is for my own use. Though, I am happy to hear any suggestions for optimization.