4 Replies Latest reply on Aug 20, 2009 12:24 PM by comment_1

    Multi Criteria Lookup

    afubeca
    afubeca Aug 19, 2009 1:12 PM

      Title

      Multi Criteria Lookup

      Post

      FileMaker Help mentions a "Multi-Criteria Lookup"... does this mean i can set up a lookup script that identifies a record in a separate table based on multiple fields and not just one? For example: Lookup (F1=AB,F2=BB,F3=BC) then lookup what appears in F4? Right now I am using a find/copy/paste script, but a lookup seems like it would be more efficient. Thanks.

       

      • 832 Views
      • Tags:
        • 1. Re: Multi Criteria Lookup
          comment_1
          comment_1 Aug 19, 2009 2:37 PM (in response to afubeca)
            

          There's no such thing as a "multi-criteria lookup" - but there can be a multi-criteria relationship (or more accurately, a multi-predicate relationship). So you can define your relationship as:

           

          TableA::F1 = TableB::AB

          AND

          TableA::F2 = TableB::BB

          AND

          TableA::F3 = TableB::BC

           

          then define another field in TableB to lookup from TableA::F4.

          • 2. Re: Multi Criteria Lookup
            RickWhitelaw
            RickWhitelaw Aug 19, 2009 11:28 PM (in response to afubeca)
              

            I believe you're referring to a multiple criteria FIND which is indeed possible and scriptable. If I'm not correct then Comment's post is right on. If you're using a "find/copy/paste" script perhaps you should be using a "find/set variable/ set field" type of scenario. "Not using copy and paste" ought to become a "golden rule". I've eliminated copy and paste from everything I do in FM. "Lookup" refers to either an auto-fill (or really a type) of a field (and very useful in some circumstances) or to the "Lookup" function which has some interesting advantages in a calculation field . . . all in the manual of course.

             

            RW 

            • 3. Re: Multi Criteria Lookup
              afubeca
              afubeca Aug 20, 2009 8:20 AM (in response to afubeca)
                

              Can I use "set field" and to set to a variable from a related table? 

               

              I tried using "set field" and specified a field in a related table in the calcualtion, assuming it would grab the value from the record currently in view, but my assumption was wrong and it did not work.

               

              The script step looks like this:

               

              Set Field [Table1::f1;Table 2::f5] 

               

              I really appreciate the advice. Thank you!

               

               

              • 4. Re: Multi Criteria Lookup
                comment_1
                comment_1 Aug 20, 2009 12:24 PM (in response to afubeca)
                  
                afubeca wrote:
                I tried using "set field" and specified a field in a related table in the calcualtion, assuming it would grab the value from the record currently in view

                It depends on where you are at the moment:

                 

                If you are on a layout of Table1, then you will be setting the value of the current record in Table1 to the value taken from the first related record in Table2.

                 

                If you are on a layout of Table2, you will be setting the value of the first related record in Table1 to the value from the current record in Table2.