3 Replies Latest reply on Jun 16, 2009 1:54 PM by philmodjunk

    Using Find/Script To Move Through Tables

    thudpucker

      Title

      Using Find/Script To Move Through Tables

      Post

      Hey all.. I'm a SQL guy that's trying his hand at FileMaker..

       

      I have three tables.. A, B, C. Table C has keys from both A and B. A and B do not share keys.

       

      I'm trying to write a script that will start out on Layout A, and use those keys to search for items in table C, and display them on the layout?

       

       So far I have:

      GoTo Layout["C"]

      Enter Find Mode

      Set Field[A::Primary_Key]

      Perform Find[]

       

      But I'm really getting stuck on the Specify Find Requests, and how they work.. And I could be going in the completely wrong direction here. Can someone steer me around?

       

      Thansk

      David    

        • 1. Re: Using Find/Script To Move Through Tables
          philmodjunk
            

          Your set field step appears to be missing a parameter.

           

          GoTo Layout["C"]

          Enter Find Mode

          Set Field[A:: Primary_Key; MissingValue]

          Perform Find[]

           

          You can add that parameter by clicking the lower specify... button and entering an expression which can be a value, the data stored in a global field, a variable or some combination of those. You can include an operator such as "<" also.

           

          You also can use the set field step to specify criteria for any field in A not just the primary key.

           

          That help you out?

           

          PS. I got rid of the pesky emoticon by inserting a space after the second colon.

          • 2. Re: Using Find/Script To Move Through Tables
            thudpucker
              

            So do I need two SetField's? One to match Primary Key A to table C, then the primary key for B to table C?

             

            Thanks for the tips.

            David  

            • 3. Re: Using Find/Script To Move Through Tables
              philmodjunk
                

              Not necessarily. You don't even need to specify the first primary key.

               

              The best way to work this out, is to create a layout based on table C and then add all fields of interest from tables A and B to it so you can see what data appears. Now try performing some finds manually by selecting Find Mode and entering search criteria in the various fields from all three tables.

               

              You are essentially constructing a "where" clause where the criteria you enter in each field is a different part of that clause.

               

              Once you see how that works, you can script the same finds using set field to enter criteria into whatever fields make sense for the results you want. Note that I'm suggesting that you add the fields from other tables just so you can see how this works. When you use set field in a script the field that is specified in the first parameter does not need to be present on the current layout.

               

              It might help if I used some SQL to draw an analogy.

               

              Since your layout is based table C, simply entering find mode is roughly the same as:

               

              SELECT * FROM C

               

              The next part of your SQL expression where you spell out the joins is replaced by the relationships you defined in Manage | Database | Relationships.

               

              The criteria you enter in fields for your find takes the place of the WHERE clause.

               

              The found set you get when you perform the find is roughly the same as a record set returned by your SQL based queries.