6 Replies Latest reply on Jan 15, 2015 9:51 AM by StephenBennett

    Find records matching another table

    StephenBennett

      Title

      Find records matching another table

      Post

      First of all, I'm on day two of my FM experience, sorry this is probably quite basic, but after two hours of trying and searching I give up. I'm an SQL and Panorama guy, until now.

      My main table contains billing records, the key field being the invoice number. My second table contains a set of invoice numbers. How do I find the records in the billing table that match the invoice numbers in the second table?

      Many thanks!

       

        • 1. Re: Find records matching another table
          philmodjunk

          Open Manage | Database | Relationships

          Find the table occurrence box for your billing records table. Find the table occurrence box for your invoices. Drag from invoice number in one box to invoice number in the other.

          Done.

          (This is the SQL equivalent of setting up a "SELECT *" query with a JOIN to one other table, but no WHERE or ORDER BY clauses as that function is done dynamically from the context of a layout based on one of these table occurrences.)

          And it is possible to make limited use of SQL in FileMaker via the ExecuteSQL function.

          • 2. Re: Find records matching another table
            StephenBennett

            Thanks, I think did this, but can't find how to run the query?

            I feel pretty stupid at this point....

             

            • 3. Re: Find records matching another table
              philmodjunk

              There is no query to run in FileMaker like you would in a SQL based system (Paradigm shift!)

              Simply open the layout based on one or the other tables shown and perform a find by entering find mode and specifying criteria. This can be done both manually or via a script. (The fact that users get a tool for querying the table "out of the box" while using the current layout to display the results without any need by you to add that capability is one of the major differences between FileMaker and many other database systems.)

              There are some details that you can clean up, however. I can't tell from your screen shot which table represents Billing Records and Which represent Invoices. I wouldn't name a table "Table" as I'm liable not to remember the purpose of that table several months from now when I go back to make design changes to the database. (You don't name tables "Table" in your SQL systems do you? ;-)

              In whichever table represents Invoices, I'd open field options for that key field and specify a unique values validation as that field should only permit unique values and this allows it to function as a Primary Key. In almost all cases, I'd define this field to auto-enter either a serial number or use a calculation to auto-enter Get ( UUID ) in this same table.

              Additional Note: From the layout based on Invoices, you can set up a portal to the other table and that portal (called a subForm in some other systems) will automatically list all billing records linked to the current invoice shown on your layout.

               

              • 4. Re: Find records matching another table
                StephenBennett

                 

                 

                Phil, thanks very much for the direction. Don't worry about the best practices stuff of table table names, I was in a hurry, it's fixed.

                What's still mystifying is using "find" mode to enter all the matching fields the child table. I see I can enter one, two, three + find criteria. But I'm after a solution where it finds *all" the matching records (>5000). I clearly am not getting it. Can I enter a formula in the field I wish to use as the key field?

                In Panorama  is would simple say this:

                «Order number» = lookup("Finders Fees","SO #",«Order number»,"SO #","",0)

                This finds all the records in the table where the fields SO# and Order Number match.

                Again, any direction appreciated!

                • 5. Re: Find records matching another table
                  philmodjunk

                  "All the matching records" would be all the records where Customer Code = Order By. Thus finding them is specifying a single value in a single field.

                  The portal that I mentioned will automatically show all matching records from this relationship.

                  You can also go to whichever layout is based on your "billing records" table, enter find mode, specify the value (customer code? Order by?) in it's match field and click perform find to fin all matching records.

                  Via a script, Go to Related Records can be used to do this as well.

                  • 6. Re: Find records matching another table
                    StephenBennett

                    Yes, I see the possibilities now. Thanks for the direction!